同义词INVALID的相关测试

文档课题：同义词INVALID的相关测试.
数据库：oracle 11.2.0.4 64位
1、创建同义词
scott@ORCL 2022-10-14 13:34:42> create public synonym syn_dept for app2_dept;

Synonym created.
scott@ORCL 2022-10-14 13:36:09> select * from syn_dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
sys@ORCL 2022-10-14 13:44:57> col object_name for a15
sys@ORCL 2022-10-14 13:45:05> select object_name,status from dba_objects where object_type='SYNONYM' and object_name='SYN_DEPT';

OBJECT_NAME     STATUS
--------------- -------
SYN_DEPT        VALID
说明：公共同义词创建完成，且状态为valid.
2、删依赖表
scott@ORCL 2022-10-14 13:41:34> drop table app2_dept;

Table dropped.
sys@ORCL 2022-10-14 13:45:06> select object_name,status from dba_objects where object_type='SYNONYM' and object_name='SYN_DEPT'

OBJECT_NAME     STATUS
--------------- -------
SYN_DEPT        INVALID
scott@ORCL 2022-10-14 13:46:13> select * from syn_dept;
select * from syn_dept
              *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
说明：删除创建同义词的基表，同义词状态变成invalid，并且不能查询该同义词.
3、编译同义词
sys@ORCL 2022-10-14 13:46:18> alter public synonym SYN_DEPT compile;

Synonym altered.

sys@ORCL 2022-10-14 13:47:35> select object_name,status from dba_objects where object_type='SYNONYM' and object_name='SYN_DEPT';

OBJECT_NAME     STATUS
--------------- -------
SYN_DEPT        VALID
scott@ORCL 2022-10-14 13:46:47> select * from syn_dept;
select * from syn_dept
              *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
说明：编译同义词后，状态显示为valid，但依然不能查询该同义词.
4、闪回依赖表
scott@ORCL 2022-10-14 13:49:56> select object_name,original_name,operation,type from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$6vkkyqauDxvgU9iFqMDeyA==$0 PK_TEST_ID                       DROP      INDEX
BIN$6vkkyqavDxvgU9iFqMDeyA==$0 APP2_DEPT                        DROP      TABLE

scott@ORCL 2022-10-14 13:51:26> flashback table APP2_DEPT to before drop;

Flashback complete.

scott@ORCL 2022-10-14 13:51:52> select * from syn_dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
说明：闪回此前删除的表后，同义词恢复正常查询.
5、闪回不编译
scott@ORCL 2022-10-14 13:53:01> drop table app2_dept;

Table dropped.

sys@ORCL 2022-10-14 13:53:59> select object_name,status from dba_objects where object_type='SYNONYM' and object_name='SYN_DEPT'

OBJECT_NAME     STATUS
--------------- -------
SYN_DEPT        INVALID

scott@ORCL 2022-10-14 13:53:40> select object_name,original_name,operation,type from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$6vkkyqaxDxvgU9iFqMDeyA==$1 BIN$6vkkyqauDxvgU9iFqMDeyA==$0   DROP      INDEX
BIN$6vkkyqayDxvgU9iFqMDeyA==$0 APP2_DEPT                        DROP      TABLE

scott@ORCL 2022-10-14 13:54:14>  flashback table APP2_DEPT to before drop;

Flashback complete.

scott@ORCL 2022-10-14 13:54:22> select * from syn_dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

说明：以上测试说明删除表后在闪回恢复表，即使不将同义词状态编译成valid也不影响该同义词的查询.