Synonyms in Oracle.

Synonyms are aliases for objects in database. Synonyms make it easy for a schema to access objects in other schema. Normally objects in other schema can be accessed by prefixing schema name to the object, but with the use of synonym there is no need to use schema name while accessing the object.

Synonyms can be public as well as private.

Public Synonym : Public synonyms are objects of public schema and can be accessed by any schema in the same or different database(assuming databases are connected by a link).

Let's see how to create a public synonym.

CREATE PUBLIC SYNONYM tab1 FOR sch1.tab1;

Now the table tab1 can be accessed by any schema using name tab1.

Private Synonym : Private synonyms are private to a schema. They can be referenced only by schema which owns the object. Private synonym can be created by removing the private keyword from the above CREATE statement.

CREATE SYNONYM tab1 FOR sch1.tab1;

Note :

1) Knowing a public synonym for an object doesn't mean that a schema can access the object as it wants. Access policies to the object will always be determined by access permissions granted to the schema for the object.

2) Permission for creation of a public synonym has to be granted by DBA.

For more info visit.

Pin objects in Oracle shared pool using DBMS_SHARED_POOL package.

DBMS_SHARED_POOL package can be used to pin PL/SQL objects, SQL cursors, triggers, sequence inside shared pool area in Oracle database memory.

Oracle has shared pool inside System Global Area(SGA) where it caches compiled PL/SQL and SQL code to improve response and reduce execution time when the same code is executed again. Whenever we execute a SQL or PL/SQL code then Oracle checks for  compiled version of the code in the shared pool area. If it finds the compiled version then the code is executed from the shared pool area, this is known as soft parse, or else, code is fetched from disk, compiled, executed and stored in the shared pool area for future reference.

Shared pool area has a memory limit, so Oracle keeps moving out objects as new objects come in, i.e. aging process is followed to move older objects out of the memory to make space for new objects. Sometimes, for improving performance, there is a need to keep some objects in the shared pool area and skip the usual aging process irrespective of the new objects coming in, so we pin those objects permanently inside the shared pool area. These objects will then not be removed by usual aging process unless we explicitly ask Oracle to unpin them.

Let's see how to use this package.

This package consists of some procedures which can be called to pin/unpin the objects.

PROCEDURE DBMS_SHARED_POOL.KEEP
(name IN VARCHAR2
,flag IN CHAR DEFAULT 'P');

KEEP procedure can be used to pin object inside shared pool area.
name: Name of the object to be pinned.
flag: Type of the object to be pinned.

Value of flag can be
P : Procedure/Function/Package
T : Type
Q : Sequence
C : Cursor
R : Trigger

PROCEDURE DBMS_SHARED_POOL.UNKEEP
(name IN VARCHAR2
,flag IN CHAR DEFAULT 'P');

UNKEEP procedure can be used to unpin object from shared pool area. Object will follow normal aging process once unpinned.
name: Name of the object to be unpinned.
flag: Type of the object to be unpinned.

PROCEDURE DBMS_SHARED_POOL.SIZES
(minsize IN NUMBER);

SIZES procedure can be used to get information about all objects which are currently in shared pool area and exceed the size specified in parameter of the procedure.

PROCEDURE DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD
(threshold_size IN NUMBER);

ABORTED_REQUEST_THRESHOLD procedure can be used to avoid Oracle flushing old objects in order to make space for a big new object. When threshold is set then any object greater than threshold size will not be allowed in memory if sufficient memory space is not available in shared pool.

NOTE: DBMS_SHARED_POOL is not accessible by a normal user, also there is no public synonym referencing the package. This package is mostly used by the Oracle DBAs.

For more info visit

Use copy command to copy tables on local or remote database.

Generally we use CREATE TABLE tab1 AS SELECT..... to copy a table's data to some other table.

There are some limitations with this method.

1) Oracle doesn't commit in between while copying data, so undo segment may run out of space.

2) This technique cannot be used to copy LONG type columns in a table.

Let's see how to use copy command to get rid of these problems.

Suppose we want to create a new table tab_new with data of table tab_old. In this case copy command will be used as

COPY FROM username/password@db_name
CREATE tab_new
USING SELECT * FROM tab_old;

This will create a new table tab_new with data of table tab_old.

Some other options which can be used in place of CREATE are

INSERT : Inserts records in an existing table.

APPEND : Creates the target table, if it doesn't exists, and inserts records.

REPLACE : Drops the existing table, creates new table and inserts records.

For more info visit.