Oracle PL/SQL/User Previliege/Synonym

create synonym for a view

 
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.

Create synonym for table

  
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>

Create synonyms for dropped tables

 
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>

Creating a Private Synonym

  
create synonym addresses for hr.locations;
desc addresses

Creating a Public Synonym

  
create public synonym employees for hr.employees;

drop public synonym;

  
drop public synonym synonymName;

drop synonym addresses;

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

drop synonym addresses;

Viewing synonyms and what they reference.

 
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>