//
you're reading...
SQL

A Short Guide to Oracle SQL [X] – Other Database Objects (Sequences & Synonyms)

This is last topic in this series. Till now we talked about a major database object “Table“. Apart from tables there are some other objects which is used more commonly and frequently. These are Indexes, Sequences, Synonyms and Views. An Index is an optional db object that provide faster access path to table data. Sequence used to generate numeric value for a column. View is a virtual (logical) table with rows and columns (from different tables). Whereas a Synonym is an alias for database objects.

I have divided this topic in three different posts. In this post, we will talk about sequences and synonyms. Indexes and Views will be subject of the next two posts in this series.

SEQUENCE

A sequence is a database object that used to auto generate unique integers. Ideally, it can be used to automatically generate primary key or unique key value for a NUMBER data type. The values in a sequence can be start at a specific number, can be in ascending or descending order.

Even though you are guarantee to have unique values, it is possible to have gaps between two sequence numbers. Accessing the same sequence by the various table, rollbacks or no use of caching value could be the valid reason for this.

 
CREATE SEQUENCE [ schema. ] sequence
   [ { INCREMENT BY } integer
   | { START WITH } integer
   | { MAXVALUE integer | NOMAXVALUE }
   | { MINVALUE integer | NOMINVALUE }
   | { CYCLE | NOCYCLE }
   | { CACHE integer | NOCACHE }
   | { ORDER | NOORDER }
   ]...
;

INCREMENT BY – An integer value that specifies the interval by which the sequence will be increased or decreased. A integer value can be positive or negative but can not be zero. If omitted, it defaults to 1.

START WITH – Specifies the first value generated by the sequence. THe default is 1.

MAXVALUE/NOMAXVALUEMAXVALUE specifies the maximum value for the sequence. If you don’t want to specify maximum value use NOMAXVALUE. This is the default.
Specify NOMAXVALUE to indicate a maximum value of 1028-1 for an ascending sequence or -1 for a descending sequence.

MINVALUE/NOMINVALUEMINVALUE specifies the minimum value that the sequence can generate. Default is NOMINVALUE.
Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -(1027 -1) for a descending sequence.

CYCLE/NOCYCLECYCLE specifies that a sequence will continue to generate values after reaching its MIN or MAX limit. NOCYCLE is default option. If NOCYCLE is used, the sequence cannot generate more numbers after reaching either its minimum or maximum value. So, Oracle will raise an error if you use NEXTVAL pseudocolumn when sequnce reached MAXVALUE.

CACHE/NOCACHE – The CACHE option pre-allocates a set of sequence numbers and keeps in memory so that sequence numbers can be assigned faster. The minimum value for this parameter is 2. NOCACHE is default and doesn’t pre-allocate the numbers for faster access.
If you omits both clauses then database caches 20 sequence numbers by default.

ORDER/NOORDERORDER guarantees that sequence numbers are generated in order of request. Typically used in RAC environment. The default is NOORDER.

 
CREATE SEQUENCE seq_test_no;

The above example omits all the optional parameter from the CREATE SEQUENCE clause. So the default values are used for other clauses.

NOTE:
You can drop a sequence using DROP SEQUENCE command.

 
DROP SEQUENCE sequence_name;

After creating sequence you can access its value via using pseudocolumns CURRVAL and NEXTVAL. CURRVAL returns the current value of sequence whereas NEXTVAL increments the sequence and returns its new value.
(Using NEXTVAL, for teh first time after creating sequence returns the inital value of the sequence.)

dbuser@O11R2> CREATE SEQUENCE seq_test_no;
Sequence created.

dbuser@O11R2> SELECT seq_test_no.CURRVAL FROM dual;

SELECT seq_test_no.CURRVAL FROM dual
       *
ERROR at line 1:
ORA-08002: sequence SEQ_TEST_NO.CURRVAL is not yet defined in this session

dbuser@O11R2> CREATE TABLE test1
            (id NUMBER, name VARCHAR2(10));
Table created.

dbuser@O11R2> INSERT INTO test1
     VALUES(seq_test_no.CURRVAL,'First ID');

VALUES(seq_test_no.CURRVAL,'First ID')
       *
ERROR at line 2:
ORA-08002: sequence SEQ_TEST_NO.CURRVAL is not yet defined in this session

dbuser@O11R2> INSERT INTO test1
     VALUES(seq_test_no.NEXTVAL,'ID 1');

1 row created.

dbuser@O11R2> SELECT seq_test_no.CURRVAL FROM dual;
   CURRVAL
----------
         1

dbuser@O11R2> SELECT seq_test_no.NEXTVAL FROM dual;
   NEXTVAL
----------
         2

dbuser@O11R2> INSERT INTO test1
     VALUES(seq_test_no.NEXTVAL,'Next ID');

1 row created.

dbuser@O11R2> SELECT * FROM test1;

        ID NAME
---------- -------
         1 ID 1
         3 Next ID
    • You can query USER_SEQUENCES to get information about sequnces owned by the user.You can also query USER_OBJECTS.
    • dbuser@O11R2> SELECT * FROM user_sequences;
      
      SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY CYCLE ORDER CACHE_SIZE LAST_NUMBER
      ------------------------------ ---------- ---------- ------------ ----- ----- ---------- -----------
      SEQ_TEST_NO                             1 1.0000E+28            1 N     N             20          21
      TESTING                                 1        130            2 Y     N             20          41
      
      dbuser@O11R2> SELECT object_name, created, status
           FROM user_objects
           WHERE object_type='SEQUENCE';
      
      OBJECT_NAME                    CREATED   STATUS
      ------------------------------ --------- -------
      TESTING                        05-MAR-13 VALID
      SEQ_TEST_NO                    05-MAR-13 VALID
      
    • You can use ALTER SEQUENCE to modify it’s behaviour such as change the increment value or minimum or maximum value etc. You cannot start a sequence with different value (for this you have to re-create the sequence).
    • ALTER SEQUENCE seq_test_no
      INCREMENT BY 2
      MAXVALUE 1000
      CYCLE;
      
      dbuser@O11R2> SELECT * FROM user_sequences 
                 WHERE sequence_name = 'SEQ_TEST_NO';
      
      SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY CYCLE ORDER CACHE_SIZE LAST_NUMBER
      ------------------------------ ---------- ---------- ------------ ----- ----- ---------- -----------
      SEQ_TEST_NO                             1       1000            2 Y     N             20           5
      

Related Privileges:

    • CREATE SEQUENCE for creating sequence in your own schema.
    • CREATE ANY SEQUENCE for creating sequence in another user’s schema.

SYNONYM

Synonym is used as an alias for a table, view, sequence, procedure, materialized view etc. It is generally used for security and convenience. Since a synonym is simply an alias, it requires no storage in the data dictionary other than its definition. The only time a synonym needs to redefine when the underlying object is renamed or moved.

CREATE [ OR REPLACE ] [PUBLIC] SYNONYM [schema.]synonym 
     FOR [ schema. ] object [ @ dblink ] ;

Creating a synonym for an object allows you to reference the object without specifying its owner/schema (if the synonym is public) provide another name for the object.

Synonym can be of two types: PRIVATE and PUBLIC. A Private synonym refers to objects of users own schema and accessible to only those who have appropriate privileges for underlying objects. Whereas a Public synonym visible to everyone and owned by the user group PUBLIC.

hr@O11R2> CREATE SYNONYM emp FOR hr.employees;                  -- Private Synonym
Synonym created.

hr@O11R2> conn user$plsql
Enter password:
Connected.

user$plsql@O11R2> SELECT COUNT(*) FROM emp;
SELECT COUNT(*) FROM emp
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


user$plsql@O11R2> SELECT COUNT(*) FROM hr.emp;                  -- specify schema name for private synonym

  COUNT(*)
----------
       107

A private synonym must have unique name within the schema. Public synonym can have same name as schema objects. You have to specify schema name along with synonym to access private synonym. This is not the case with public synonym.

dbadmin@O11R2> CREATE PUBLIC SYNONYM emp FOR hr.employees;
Synonym created.

user$plsql@O11R2> conn db_user
Enter password:
Connected.

db_user@O11R2> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
       107

db_user@O11R2> conn scott
Enter password:
Connected.

scott@O11R2> SELECT COUNT(*) FROM emp;                
                                                       
  COUNT(*)                                            
----------                                            -- ??
        14

When you specify a object without schema name, Oracle first look into the objects owned by current user. If no object found, it will look for the private synonym owned by the current user. If no private synonym found with the name, it will search for public synonyms. In this case, there is a Object (table) called emp in the SCOTT schema, so oracle will point to this table – not the table pointed by the public synonym emp.

Even though a public synonym is visible to everyone, a user still needs object privileges on underlying object or public synonym.

scott@O11R2> conn test
Enter password:
Connected.

test@O11R2> SELECT COUNT(*) FROM emp;
SELECT COUNT(*) FROM emp
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

One impportant point to remember is, a synonym can be created for the non-existing database objects or for the objects you don’t have any privileges. You only get an error message when you are trying to access teh synonym.

dbadmin@O11R2> CREATE PUBLIC SYNONYM testing FOR test.test_table;
Synonym created.

dbadmin@O11R2> SELECT * FROM testing;
SELECT * FROM testing
              *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

DROP SYNONYM command is used to delete synonym. When deleting public synonym you must use PUBLIC keyword. Otherwise, Oracle will returns an error message.

DROP [PUBLIC] SYNONYM [ schema. ] synonym [FORCE] ;

hr@O11R2> DROP SYNONYM emp;

Synonym dropped.

hr@O11R2> conn dbadmin
Enter password:
Connected.

dbadmin@O11R2> DROP SYNONYM emp;
DROP SYNONYM emp
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist

dbadmin@O11R2> DROP PUBLIC SYNONYM emp;

Synonym dropped.

Related Privileges:

    • CREATE SYNONYM for creating private synonym in your own schema.
    • CREATE ANY SYNONYM for creating private synonym in another user’s schema.
    • CREATE PUBLIC SYNONYM for creating public synonym. PUBLIC SYNONYM usually created by DBAs or users with DBA privileges.
    • DROP ANY SYNONYM to drop private synonym in another user’s schema.
    • DROP PUBLIC SYNONYM to drop public synonym.
Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: