Basic usage of Oracle synonyms and Sequences

Oracle synonyms are commonly used to replace an object. Their main role is to Use synonyms to easily manipulate objects in different user modes. We use select user from dual to view the currently used users.

Access tables in other user mode:

 
 

  
  
select * from scott.dept;  
 
 

View the dept table data in scott user mode.

Oracle synonyms are classified into private and public, which can only be used by created users. Public is available to all users.

Syntax:

 
 

  
  
CREATE SYNONYN DEPT FOR SCOTT.DEPT; 
 
 

Creates a dept table under the scoot user as a synonym for the current user. The default value is private.

Drop synonym dept.

 
 

  
  
create public synonym dept for scott.dept; 
 
 

Creates a public query and performs the same query in other user modes.

Sequence

Role: an independent transaction is automatically increased or decreased according to a certain increment. It is a group of integer values.

Syntax:

Create sequence myseq start with 1 -- start from 1

Increment by 1 -- increase by 1 each time

Order -- sort from small to large

Nocycle; -- in order to avoid repeated values and do not perform sequential loops, this item affects performance and forces the database to access the disk.

Usage:

 
 

  
  
select myseq.nextval from dual; 
 
 

Obtain the next value through nextval.

Pass

 
 

  
  
select myseq.currval from dual;  
 
 

View the value of the current sequence.

If the database is restarted, you cannot view the current value of the sequence immediately through currval. You need to obtain the value through nextval and then view the value of the current sequence through currval.

Desc dba_sequences: view the sequence under dba. It also includes all_sequences and user_sequences.

The increment of the modification sequence is:

 
 

  
  
ALTER SEQUENCE MYSEQ INCREMENT BY 3; 
 
 

Modify the Incremental Quantity of the sequence. However, the current value of the sequence cannot be modified. The above content is an introduction to the basic use of Oracle synonyms and sequences. I hope you will get something better.