Oracle PL/SQL/User Previliege/Synonym

create synonym for a view

   <source lang="sql">

SQL> SQL> create table emp(

 2           emp_id                integer         primary key
 3          ,lastname               varchar2(20)    not null
 4          ,firstname              varchar2(15)    not null
 5          ,midinit                varchar2(1)
 6          ,street                 varchar2(30)
 7          ,city                   varchar2(20)
 8          ,state                  varchar2(2)
 9          ,zip                    varchar2(5)
10          ,shortZipCode                   varchar2(4)
11          ,area_code              varchar2(3)
12          ,phone                  varchar2(8)
13          ,company_name           varchar2(50));

Table created. SQL> SQL> SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values

 2                      (1,"Jones","Joe","J","1 Ave","New York","NY","11202","1111","212", "221-4333","Big Company");

1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values

 2                      (2,"Smith","Sue","J","1 Street","New York","NY","11444","1111","212", "436-6773","Little Company");

1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values

 2                      (3,"X","Peggy","J","1 Drive","New York","NY","45502","2222","212", "234-4444","Medium Company");

1 row created. SQL> SQL> create or replace view phone_list as

 2  select emp_id, firstname || " " || midinit || ". " || lastname as name,"(" || area_code || ")" || phone as telephone#
 3  from emp;

View created. SQL> SQL> SQL> SQL> desc phone_list

Name                                                                                                  Null?    Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
EMP_ID                                                                                                NOT NULL NUMBER(38)
NAME                                                                                                   VARCHAR2(39)
TELEPHONE#                                                                                             VARCHAR2(13)

SQL> select * from phone_list;

   EMP_ID NAME                                    TELEPHONE#

---

--------------------------------------- -------------

        1 Joe J. Jones                            (212)221-4333
        2 Sue J. Smith                            (212)436-6773
        3 Peggy J. X                              (212)234-4444

3 rows selected. SQL> create synonym phones for phone_list; Synonym created. SQL> desc phones

Name                                                                                                  Null?    Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
EMP_ID                                                                                                NOT NULL NUMBER(38)
NAME                                                                                                   VARCHAR2(39)
TELEPHONE#                                                                                             VARCHAR2(13)

SQL> select * from phones;

   EMP_ID NAME                                    TELEPHONE#

---

--------------------------------------- -------------

        1 Joe J. Jones                            (212)221-4333
        2 Sue J. Smith                            (212)436-6773
        3 Peggy J. X                              (212)234-4444

3 rows selected. SQL> SQL> select view_name from user_views; VIEW_NAME

---

EMP_HQ V AVG_SAL EMPDEPT_V DEPT_SAL ALL_ORACLE_ERRORS INVENTORY_VIE TOP_EMP EMP_BONUS SHARED PHONE_LIST 11 rows selected. SQL> SQL> select synonym_name, table_name from user_synonyms; SYNONYM_NAME TABLE_NAME

---

------------------------------

PHONES PHONE_LIST 1 row selected. SQL> SQL> SQL> drop synonym phones ; Synonym dropped. SQL> SQL> SQL> SQL> drop table emp; Table dropped.

 </source>
   
  

Create synonym for table

   <source lang="sql">
 

SQL> SQL> CREATE TABLE source_log

 2  (backup_date    DATE,
 3   backup_time    VARCHAR2(6),
 4   last_ddl_time  DATE,
 5   owner          VARCHAR2(30),
 6   name           VARCHAR2(30),
 7   type           VARCHAR2(12),
 8   line           NUMBER,
 9   text           VARCHAR2(2000))
10  /

Table created. SQL> CREATE INDEX source_log_idx1 ON source_log

 2    (last_ddl_time, owner, name)
 3  /

Index created. SQL> CREATE or replace PUBLIC SYNONYM source_log FOR source_log

 2  /

Synonym created. SQL> GRANT SELECT, INSERT ON source_log to PUBLIC

 2  /

Grant succeeded. SQL> SQL> drop table source_log; Table dropped. SQL> SQL>

 </source>
   
  

Create synonyms for dropped tables

   <source lang="sql">

SQL> SQL> SQL> SELECT "CREATE PUBLIC SYNONYM " || table_name

 2         || " for " || user || "." || table_name || ";"
 3  FROM DBA_TABLES
 4  WHERE dropped = "NO";

no rows selected SQL>

 </source>
   
  

Creating a Private Synonym

   <source lang="sql">
 

create synonym addresses for hr.locations; desc addresses

 </source>
   
  

Creating a Public Synonym

   <source lang="sql">
 

create public synonym employees for hr.employees;

 </source>
   
  

drop public synonym;

   <source lang="sql">
 

drop public synonym synonymName;

 </source>
   
  

drop synonym addresses;

   <source lang="sql">
 

Creating a Private Synonym create synonym addresses for hr.locations; desc addresses

drop synonym addresses;

 </source>
   
  

Viewing synonyms and what they reference.

   <source lang="sql">

SQL> select owner, synonym_name, table_owner, table_name

 2    from all_synonyms
 3   where synonym_name="JOBS" and rownum < 6;

no rows selected SQL> SQL> SQL>

 </source>