OCP 1Z0 051 50

50. SLS is a private synonym for the SH.SALES table.
The user SH issues the following command:
DROP SYNONYM sls;
Which statement is true regarding the above SQL statement?
A. Only the synonym would be dropped.
B. The synonym would be dropped and the corresponding table would become invalid.
C. The synonym would be dropped and the packages referring to the synonym would be dropped.
D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.

synonym是个独立对象，你可以把它理解成windows里的“快捷方式”，删除后对源没影响。
我们用function来代替packages

SQL> conn sys/ofbiz as sysdba
已连接。
SQL> create or replace public synonym sls for sh.sales;
同义词已创建。

SQL> conn sh/sh
已连接。

SQL> create or replace synonym sls for sh.sales;
同义词已创建。

SQL> CREATE OR REPLACE FUNCTION get_sal RETURN NUMBER AS
  2    v_cust_id sls.cust_id%TYPE;
  3  BEGIN
  4    SELECT cust_id INTO v_cust_id FROM sls WHERE rownum <= 1;
  5    RETURN v_cust_id;
  6  END;
  7  /

函数已创建。

删除后，原表没影响

SQL> drop synonym sls;
同义词已删除。

SQL> select prod_id from sales where rownum <=1;
   PROD_ID
----------
        13

已选择 1 行。

funciton仍在

SQL> SELECT object_name, status FROM user_objects WHERE object_name = upper('get_sal');

OBJECT_NAM STATUS
---------- ----------
GET_SAL    INVALID

已选择 1 行。

public synonym不受影响（两个不同的快捷方式）

SQL> select * from sls where rownum <=1;

   PROD_ID    CUST_ID TIME_ID        CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- -------------- ---------- ---------- ------------- -----------
        13        987 10-1月 -98              3        999             1     1232.16

已选择 1 行。

Answer: A