Working with Other Schema Objects

You can manage other schema objects with Oracle Enterprise Manager Database Control (Database Control), including the following:

• Sequences

  A sequence is a database object that generates unique integers. Each time that you query the sequence, it increments its current value by a designated amount and returns the resulting integer. Sequences can be simultaneously queried by multiple users, and each user receives a unique value. For this reason, using a sequence to provide the value for a primary key in a table is an easy way to guarantee that the key value is unique, regardless of the number of users inserting data into the table.

• Synonyms

  A synonym is an alias for any schema object, such as a table or view. Synonyms provide an easy way to hide the underlying database structure from an application or a user. Synonyms can be private or public. A public synonym does not have to be qualified with a schema name, whereas a private synonym does, if the user referencing the private synonym is not the synonym owner. For example, consider the following query, issued by a user who has been granted the SELECT object privilege on the hr.employees table:

  SELECT   employee_id, salary
  FROM     hr.employees
  ORDER BY salary
  

  Now suppose you create a public synonym named personnel as an alias for the hr.employees table, and you grant the SELECT privilege on the hr.employees table to PUBLIC (all database users). With the public synonym in place, any user can issue the following simpler query:

  SELECT   employee_id, salary
  FROM     personnel
  ORDER BY salary
  

  The user who created this query did not need to know the name of the schema that contains the personnel data.

  Note: If a user owns a table named personnel, then that table is used in the query. If no such table exists, then the database resolves the public synonym and uses the hr.employees table.

  An additional benefit of synonyms is that you can use the same synonym in a development database as in the production database, even if the schema names are different. This technique enables application code to run unmodified in both environments. For example, the preceding query would run without errors in a development database that had the employees table in the dev1 schema, if the personnel synonym is defined in the development database to point to the dev1 schema.

  Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary. To reference a synonym in a query, you must have privileges on the object to which it points. Synonyms themselves cannot be secured. If you grant object privileges on a synonym to a user, then you are granting privileges on the object to which the synonym points.

• Database links

  A database link is a schema object that points to another Oracle database. You use a database link to query or update objects in a remote database. Database links are used in distributed database environments, which are described in Oracle Database Administrator's Guide.