 |
|
Administration of Other Database Objects
Oracle Tips by Burleson
|
Chapters 4, 5, and 6 covered the
administration of database tables and indexes. In this chapter, we
will discuss the administration of other database objects, which
include synonyms, sequences, views, and so on. More specifically, for
Oracle8 and Oracle8i, we add Java, DIRECTORY, DIMENSION, OUTLINE,
CONTEXT, OPERATOR, MATERIALIZED VIEWS, and LIBRARY objects; for
Oracle, we add the ability to create parameter (initialization) and
server parameter (dynamically reconfigurable initialization) files.
Administration of Synonyms
Synonyms are a very powerful feature of Oracle
and other SQL-compliant relational database systems. They are used as
a database shorthand. They make it possible to shorten the
specification of long or complex object names. This is especially
useful for shared tables or views. In addition, the use of DATABASE
LINKS in synonyms allows transparent access to other databases on
other nodes or even other entire systems halfway around the globe.
Creation of Synonyms
Synonyms are created using the CREATE SYNONYM
command. Synonyms can be either PRIVATE (the default) or PUBLIC.
Private synonyms can only be used by the user creating them. You must
have a specific grant or one of the special ROLES assigned to you
before you can create synonyms. Only users with appropriate privileges
(usually DBAs) can create PUBLIC synonyms. Since only the owner can
use them, PRIVATE synonyms are more secure.
The simplified format of the CREATE SYNONYM
command follows:
CREATE
[PUBLIC] SYNONYM [schema.]synonym_name
FOR [schema.]object[@dblink];
where:
Synonym. An allowed name. (It cannot be
the name of an existing object for this user. For the purposes of
uniqueness, the schema name is considered to be a part of the name for
an object.)
Schema.object. An existing table, view,
package, procedure, function, Java object, materialized view, other
synonym, or sequence name. The schema object cannot be contained in a
package. A synonym cannot be created for an object type.
Database link. An existing database
link (covered in section 7.3).
Synonyms provide both data independence and
location transparency. With proper use and assignment, they allow an
application to function regardless of table ownership, location, or
even database.
Synonyms can be used in SELECT, INSERT,
UPDATE, DELETE, EXPLAIN PLAN, LOCK TABLE, AUDIT, NOAUDIT, GRANT,
REVOKE, and COMMENT commands.
Alteration of Synonyms
Synonyms cannot be altered. To change a
synonym’s definition, it must be dropped and re-created.
Dropping Synonyms
Synonyms are dropped via the DROP command:
DROP
[PUBLIC] SYNONYM [schema.]synonym_name;
Re-creating Synonyms
It is sometimes handy to have a script lying
around to rebuild your existing synonyms. The script in Source 7.1
will create a synonym rebuild script for you from your data
dictionary.
SOURCE 7.1 Script to generate synonym
rebuild script.
REM
FUNCTION: SCRIPT FOR CREATING SYNONYMS
REM
This script must be run by a user with the DBA role.
REM This script is intended to run with Oracle7 or
Oracle8.
REM Running this script will in turn create a script
to build
REM all the synonyms in the database. The created
script,
REM create_synonyms.sql, can be run by any user with
the DBA
REM role or with the 'CREATE ANY SYNONYM' and
'CREATE PUBLIC
REM SYNONYM' system privileges.
REM NOTE: This script does not capture synonyms for tables owned
REM by the 'SYS' user.
REM Only preliminary testing of this script was
performed. Be
REM sure to test it completely before relying on it.
REM
SET VERIFY OFF FEEDBACK OFF TERMOUT OFF ECHO OFF PAGESIZE 0
SET TERMOUT ON
SELECT ''Creating synonym build script...'' FROM dual;
SET TERMOUT OFF
COLUMN dbname NEW_VALUE db NOPRINT
SELECT name dbname FROM v$database;
DEFINE cr=''chr(10)''
SPOOL rep_out\&db\crt_syns.sql
SELECT ''CREATE ''|| DECODE(owner,''PUBLIC'',''PUBLIC '',NULL) ||
'SYNONYM ''|| DECODE(owner,''PUBLIC'',NULL, owner || ''.'') ||
LOWER(synonym_name) || '' FOR '' || LOWER(table_owner) ||
''.'' || LOWER(table_name) ||
DECODE(db_link,NULL,NULL,''@''||db_link) || '';''
FROM sys.dba_synonyms
WHERE table_owner != ''SYS''
ORDER BY owner
/
SPOOL OFF
SET VERIFY ON FEEDBACK ON TERMOUT ON PAGESIZE 22
CLEAR COLUMNS
UNDEF cr
Administration of Sequences
Sequences are special database objects that
provide numbers in sequence for input to a table. They are useful for
providing generated primary key values and for input of number type
columns such as purchase order, employee number, sample number, and
sales order number, where the input must be unique and in some form of
numerical sequence.
Creation of Sequences
Sequences are created by use of the CREATE
SEQUENCE command. The command’s format follows:
CREATE
SEQUENCE [schema.]sequence_name
[START WITH n]
[INCREMENT BY n]
[MAXVALUE n|NOMAXVALUE]
[MINVALUE n|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE n|NOCACHE]
[ORDER|NOORDER];
where:
sequence_name. The name you want the
sequence to have. This may include the user name if created from an
account with DBA privilege.
n. An integer, positive or negative.
INCREMENT BY. Tells the system how to
increment the sequence. If it is positive, the values are ascending;
if it is negative, the values are descending.
START WITH. Tells the system which
integer to start with.
MINVALUE. Tells the system how low the
sequence can go. For ascending sequences, it defaults to 1; for
descending sequences, the default value is 10e27-1.
MAXVALUE. Tells the system the highest
value that will be allowed. For descending sequences, the default is
1; for ascending sequences, the default is 10e27-1.
CYCLE. Causes the sequence to
automatically recycle to minvalue when maxvalue is reached for
ascending sequences; for descending sequences, it causes a recycle
from minvalue back to maxvalue.
CACHE. Caches the specified number of
sequence values into the buffers in the SGA. This speeds access, but
all cached numbers are lost when the database is shut down. The
default value is 20; maximum value is maxvalue-minvalue.
ORDER. Forces sequence numbers to be
output in order of request. In cases where they are used for
timestamping, this may be required. In most cases, the sequence
numbers will be in order anyway, so ORDER will not be required. ORDER
is necessary only to guarantee ordered generation if you are using
Oracle with the Oracle Real Application Clusters option in parallel
mode. If you are using exclusive mode, sequence numbers are always
generated in order.
Sequences prevent the performance problems
associated with sequencing numbers generated by application triggers
of the form:
DECLARE
TEMP_NO NUMBER;
BEGIN
LOCK TABLE PO_NUM IN EXCLUSIVE MODE NOWAIT;
SELECT MAX(PO_NUM)+1 INTO TEMP_NO FROM SALES ;
END;
If the application requires numbers that are
exactly in sequence (e.g., 1, 2, 3 . . .) then the trigger shown above
may be your only recourse, since if a statement that references a
sequence is rolled back (canceled), that sequence number is lost.
Likewise, any cached sequence numbers are lost each time a database is
shut down. The closest a sequence can get to being fully sequential
would be:
CREATE
SEQUENCE in_order
START WITH 1
INCREMENT BY 1
NOMAXVALUE
ORDER
NOCACHE;
If a sequence already exists in a test or
development instance, the script SEQ_RCT.SQL, available from the Wiley
Web site, can be used to build a script to transfer or document the
structure of the sequence.
Alteration of Sequences
There may be times when a sequence must be
altered, such as when a maximum or minimum value is reached. The ALTER
SEQUENCE command is used to accomplish this. The format of the command
follows:
ALTER
SEQUENCE [schema.]sequence_name
[INCREMENT BY n]
[MAXVALUE n|NOMAXVALUE]
[MINVALUE n|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE n|NOCACHE]
[ORDER|NOORDER];
Only
future sequence numbers are affected by this statement. To alter the
START WITH clause, the sequence must be dropped and re-created. For
ascending sequences, the MAXVALUE cannot be less than the current
sequence value. For descending sequences, the MINVALUE cannot be
greater than the current sequence value.
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
