Synonyms and Sequences

Synonyms

Synonyms are aliases for objects within the database, they are used to make life easier for users, they hide the objects identity and can be either public or private. public are accessible to all users, and private synonyms are part on an individual users schema, so the user has to grant privilege right to other users. Synonyms can be created for

They are used to allow users access to objects that lie outside their own schema. There are two major uses of synonyms

Creating public

create public synonym employees for test.employees;

Note: any user can use the synonym above

Creating private create synonym addresses for hr.locations;
Removing drop synonym addresses;
Useful Views
DBA_SYNONYMS describes all synonyms in the database
DBA_CATALOG lists all indexes, tables, views, clusters, synonyms, and sequences in the database

Sequences

Oracle has a automatic sequence generator that can produce a unique set of numbers, normally the numbers are used for primary keys. It is possible to cache the sequence numbers making the numbers ready and available in memory which improves performance, however if the system was to crash those sequences numbers are lost forever so be careful if the application requires no loss of sequence numbers.

There are a number of options that can be used when creating a sequence

Creating

create sequence employee_id_seq
start with 1000
increment by 1
nomaxvalue
nocycle;

start with - you can choose any number you like to start with
increment by - you can increment by any number
nomaxvalue - just keep on going
nocycle - you can recycle the list
Removing drop sequence employee_id_seq;
caching (default 20)

alter sequence employee_id_seq cache 100;

Note: remember you will lose the cache values during a system crash.

Using

select employee_id_seq.nextval from dual;
select employee_id_seq.currval from dual;

Note: If using "currval" in you get error message “is not yet defined in this session” must use nextval first.
Useful Views
DBA_SEQUENCES describes all sequences in the database
DBA_CATALOG lists all indexes, tables, views, clusters, synonyms, and sequences in the database