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 - you can choose any number you like to start with |
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; 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 |