Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.
   
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter