Destructive Testing of dual tables

With regard to dual table destructive testing, since it is a destructive test, it needs to be determined that this test is only used for testing or personal learning. Some SQL statements may seem extremely simple, but once

With regard to dual table destructive testing, since it is a destructive test, it needs to be determined that this test is only used for testing or personal learning. Some SQL statements may seem extremely simple, but once

With regard to dual table destructive testing, since it is a destructive test, it needs to be determined that this test is limited to testing or personal learning, and some SQL statements may seem extremely simple, however, once running, the entire business system will crash.
For example, we can open a table named dual, which is a dummy table. The content in the table has no specific meaning and exists for the sake of existence. However, once a problem occurs in this table, all related basic operations will be affected, and the consequences cannot be imagined.
In a simple simulation, start the following attempt on a personal machine, drop the table dual

SQL> show user
USER is "SYS"
SQL>
SQL> show parameter insta
NAME TYPE VALUE
-----------------------------------------------------------------------------
Active_instance_count integer
Cluster_database_instances integer 1
Instance_groups string
Instance_name string TEST01
Instance_number integer 0
Instance_type string RDBMS
Open_links_per_instance integer 4
Parallel_instance_group string
Parallel_server_instances integer 1
SQL> drop table dual; -- No problem occurred when running this command.
Table dropped.

SQL> select count (*) from dual; -- the error 1775 is returned once the query is started.
Select count (*) from dual
*
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL> select * from dual; -- try again and the problem persists. It can be imagined that the problem in the online business system is fatal.
Select * from dual
*
ERROR at line 1:
ORA-01775: looping chain of synonyms
-- If you do not try to restart the database, you can use the following method. We can completely recreate the table dual and insert dummy data.

SQL> CREATE TABLE "SYS". "DUAL"
("DUMMY" VARCHAR2 (1)
) TABLESPACE "SYSTEM ";
Table created.

SQL> GRANT SELECT ON "SYS". "DUAL" TO PUBLIC WITH GRANT OPTION;
Grant succeeded.

SQL> select * from dual;
No rows selected

SQL> insert into dual values ('x ');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from dual; -- simple verification, you will find that the problem seems to be resolved.
D
-
X

SQL> select sysdate from dual; -- try to use dual to query the time, and no problem is found.

SYSDATE
------------------
20-NOV-14
At this time, you can view the invalid objects in the related sys and find that there are still many affected objects. At this time, you can re-compile them.

SQL> select object_name, owner, object_type from dba_objects where status = 'invalid'
OBJECT_NAME OWNER OBJECT_TYPE
-------------------------------------------------------------------------------
AQ $ _ ALERT_QT_V SYS EVALUATION CONTEXT
ALERT_QUE_R SYS RULE SET
ALERT_QUE_N SYS RULE SET
DBMS_RCVMAN SYS PACKAGE BODY
DBMS_BACKUP_RESTORE SYS PACKAGE BODY
DBMS_CDC_UTILITY SYS PACKAGE BODY
DBMS_CDC_ISUBSCRIBE SYS PACKAGE BODY
AQ $ _ MEM_MC_V SYS EVALUATION CONTEXT
AQ $ _ AQ_PROP_TABLE_V SYS EVALUATION CONTEXT
Aq_prop_policy_r SYS RULE SET
Aq_prop_policy_n SYS RULE SET