SEQUENCE

SEQUENCE: A sequence is an object in oracle, which is used to generate a series of numbers. It can generate unique sequential numbers for using in a primary key column of a table.

 

CURRVAL and NEXTVAL are called pseudo columns. CURRVAL returns the current value of a sequence. NEXTVAL increments the sequence and then returns the next value. A sequence can be modified using ALTER statement.

 SQL> ALTER SEQUENCE SS MAXVALUE 99999;

 

SQL> DROP SEQUENCE SS;

LOCK TABLE

 LOCK TABLE : The LOCK TABLE command is used to prevent concurrent processes from changing a table or from using it.

 The two Locking modes are

1)IN SHARE MODE: In which concurrent processes are allowed to perform only read-only operations.
2)IN EXCLUSIVE MODE:Prevents concurrent processes from performing any operation the table.

 Only the Owner of the table, DBA, or a user having ALTER, DELETE, INSERT, SELECT, UPDATE can lock a table. A table can have multiple SHARE LOCKS but only one EXCLUSIVE LOCK

 

Example: For obtaining EXCLUSIVE LOCK we write.

 SQL> LOCK TABLE STUD in EXCLUSIVE MODE;

 SQL> LOCK TABLE STUD in EXCLUSIVE MODE NOWAIT;

If we try to obtain a lock using the first statement then Oracle waits if the table is not available for locking. In second statement it returns immediately.

Example: For obtaining SHARE LOCK we write.

SQL> LOCK TABLE STUD in SHARE MODE; --- to obtain shared mode lock