Oracle uses synonyms to point users to other schema objects when the user does not have direct access to that schema. This is generally accomplished by creating a synonym owned by public to a private schema object. Once the public synonym is created all users have access to the private schema object. In this article we will recreate the DDL for public synonyms referencing one private schema’s objects.
1. Connect to your Oracle database server and logon to SQL*PLUS.
MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 15 02:57:41 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
2. Create public synonyms for all of the user SCOTT’s private tables.
Select ‘create public synonym ‘||table_name||’ for scott.’
||table_name||’;’ from dba_tables where owner=’SCOTT’;
SQL> Select ‘create public synonym ‘||table_name||’ for scott.’
2 ||table_name||’;’ from dba_tables where owner=’SCOTT’;
create public synonym DEPT for scott.DEPT;
create public synonym EMP for scott.EMP;
create public synonym BONUS for scott.BONUS;
create public synonym SALGRADE for scott.SALGRADE;
SQL> create public synonym DEPT for scott.DEPT;
Synonym created.
SQL> create public synonym EMP for scott.EMP;
Synonym created.
SQL> create public synonym BONUS for scott.BONUS;
Synonym created.
SQL> create public synonym SALGRADE for scott.SALGRADE;
Synonym created.
SQL>
3. Execute the following SQL statement to regenerate the DDL for public synonyms referring to private tables owned by the user SCOTT.
select ‘create public synonym ‘||synonym_name||’ for ‘||table_owner||’.’||table_name||’;’
from dba_synonyms where table_owner=’SCOTT’;
SQL> select ‘create public synonym ‘||synonym_name||’ for ‘||table_owner||’.’||table_name||’;’
2 from dba_synonyms where table_owner=’SCOTT’;
create public synonym DEPT for SCOTT.DEPT;
create public synonym EMP for SCOTT.EMP;
create public synonym BONUS for SCOTT.BONUS;
create public synonym SALGRADE for SCOTT.SALGRADE;
SQL>
4. This DDL can now be used to rebuild the public synonyms of a give schema or move the synonyms to another database.
Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com