Creating Sequences and Synonyms - Oracle DBA

Various other database objects are needed to support the main objects in the database (such as tables). Two such objects are sequences and synonyms.

Sequence

An Oracle sequence is a named sequential number generator. A sequence is often used to generate a unique key for the primary key of a table. A sequence object is owned by a single schema, but it can be used by other database users if the proper permissions are granted to the users.

Sequence A database structure that generates a series of numbers typically used to assign primary key values to database tables.

Sequences can begin and end with any value, can be ascending or descending, and can skip (increment) a specified number between each value in the sequence. The basic syntax for CREATE SEQUENCE is as follows:

If all optional parameters are omitted, the sequence starts with one and increases by increments of one, with no upper boundary.

Sequences are referenced in DML statements by using the syntax sequence_ name.currval or sequence_name.nextval. The qualifier nextval retrieves the next value. The qualifier currval retrieves the most recent number generated without incrementing the counter. For example, here are some sample SELECT statements that access the sequence used for employee numbers, EMPLOYEES_SEQ:

The HR department has asked the DBA, Janice, to re-create the sequence for the EMPLOYEES table to start at 501 and increment by 10. Janice drops the old sequence and re-creates it:

After the sequence has been created, the user HR inserts a record into the EMPLOYEES table as follows:

1 row created.

The next time the employees_seq sequence is used, the value returned will be 511.

Synonyms

A synonym is an alias for another database object, such as a table, sequence, or view. Synonyms provide easier access to database objects outside the user's schema.

There are two kinds of synonyms: public and private. Public synonyms are available to all database users. A private synonym is available only in the session of the schema owner who created it.

Synonym An alias assigned to a table, view, or other database structure. Synonyms can be either available to all users (public) or available only to one schema owner (private).

Synonyms are useful in providing a common name to a database object, regardless of which username is logged in to the database. The temporary table created by Janice the DBA, called TRAVEL_DEST, must be qualified with the schema name if anyone other than Janice wants to access it. For example, if the user HR is connected to the database and no synonym has been specified, the table must be fully qualified:

The syntax for creating a synonym is as follows:

To facilitate easy access to the table TRAVEL_DEST, Janice creates a public synonym for the table:

What happens if a user has a private synonym called TRAVEL_DEST, or worse yet, his or her own table is called TRAVEL_DEST? Unqualified object references (object references that aren't prefixed with a schema name) are resolved in the following order:

  1. A real object with the specified name
  2. A private synonym owned by the current user
  3. A public synonym

Private synonyms can be useful in a development environment when you have a copy of a table with a different name. A private synonym can be created to refer to the copy of the production table with the same name as the production table. During testing, the developer's private synonym points to the copy and does not impact the production table. When development is complete, the developer can remove the private synonym and move the new SQL code into a production environment, without changing any table names in the SQL code.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle DBA Topics