oracle删除一张表后，索引，同义词，视图，约束会被删除么
问题描述：看到有一道题，说删除一张表之后，什么会被关联删除
进行测试，看看一张表什么会被关联删除，进行scott下的EMP进行测试
一、创建测试需求用例
表结构：
SQL> desc emp; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
创建视图：
CREATE OR REPLACE VIEW V_EMP AS SELECT * FROM EMP WHERE ENAME LIKE '%A%' UNION ALL SELECT * FROM EMP WHERE ENAME LIKE '%S%' UNION ALL SELECT * FROM EMP WHERE SAL >= 3000; CREATE OR REPLACE VIEW V_EMP AS SELECT * FROM EMP WHERE ENAME LIKE '%A%' UNION SELECT * FROM EMP WHERE ENAME LIKE '%S%' UNION SELECT * FROM EMP WHERE SAL >= 3000;
创建序列：
CREATE SEQUENCE SEQ_BJSXT START WITH 20001 INCREMENT BY 2 MAXVALUE 99999999 MINVALUE 1 CYCLE CACHE 50
创建同义词
create or replace synonym syn_emp for scott.emp;
删除前状态
视图查询：
SQL> select view_name,view_type from user_views; VIEW_NAME VIEW_TYPE ------------------------------ ------------------------------ V_EMP
索引查询：
SQL> select INDEX_NAME,TABLE_NAME,STATUS from user_indexes; INDEX_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ -------- SYS_IL0000089251C00003$$ TEST VALID IDX_T_ID T VALID PK_EMPNO_OGG EMP_OGG VALID PK_EMP EMP VALID IDX_EMP_ENAME EMP VALID PK_DEPTNO_OGG DEPT_OGG VALID PK_DEPT DEPT VALID 7 rows selected.
序列查询：
SQL> r 1* select * from user_sequences SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- SEQ_BJSXT 1 99999999 2 Y N 50 20101
主键查询:
主键
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND INDEX_TYPE 1 IDX_EMP_ENAME EMP ENAME 1 10 10 ASC NORMAL 2 PK_EMP EMP EMPNO 1 22 0 ASC NORMAL
外键约束：
select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 'EMP'; 1 SCOTT FK_DEPTNO R EMP <Long> SCOTT PK_DEPT NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2013/8/24 12:04:21
同义词查询：
SQL> SELECT * FROM USER_SYNONYMS; SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- SYN_EMP SCOTT EMP
user_obejcts状态查询：
14和17分别是emp表和emp表中的索引object，其余的还能看到序列，视图，同义词的对象
select * from user_objects; OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME 1 PK_DEPT 87107 87107 INDEX 2013/8/24 12:04:21 2013/8/24 12:04:21 2013-08-24:12:04:21 VALID N N N 4 2 DEPT 87106 87106 TABLE 2013/8/24 12:04:21 2013/8/24 12:04:21 2013-08-24:12:04:21 VALID N N N 1 3 BONUS 87110 87110 TABLE 2013/8/24 12:04:21 2013/8/24 12:04:21 2013-08-24:12:04:21 VALID N N N 1 4 SALGRADE 87111 87111 TABLE 2013/8/24 12:04:21 2013/8/24 12:04:21 2013-08-24:12:04:21 VALID N N N 1 5 EMP_OGG 88789 88789 TABLE 2020/12/23 13:14:06 2020/12/23 13:14:30 2020-12-23:13:14:06 VALID N N N 1 6 DEPT_OGG 88790 88790 TABLE 2020/12/23 13:14:07 2020/12/23 13:14:42 2020-12-23:13:14:07 VALID N N N 1 7 PK_EMPNO_OGG 88791 88791 INDEX 2020/12/23 13:14:11 2020/12/23 13:14:11 2020-12-23:13:14:11 VALID N N N 4 8 PK_DEPTNO_OGG 88792 88792 INDEX 2020/12/23 13:14:12 2020/12/23 13:14:12 2020-12-23:13:14:12 VALID N N N 4 9 TEST 89251 89251 TABLE 2021/6/13 16:12:42 2021/6/13 16:12:42 2021-06-13:16:12:42 VALID N N N 1 10 IDX_T_ID 89274 89274 INDEX 2021/6/14 16:44:43 2021/6/14 16:44:43 2021-06-14:16:44:43 VALID N N N 4 11 SYS_IL0000089251C00003$$ 89253 89253 INDEX 2021/6/13 16:12:42 2021/6/13 16:12:42 2021-06-13:16:12:42 VALID N Y N 4 12 SYS_LOB0000089251C00003$$ 89252 89252 LOB 2021/6/13 16:12:42 2021/6/13 16:12:42 2021-06-13:16:12:42 VALID N Y N 8 13 T 89273 89273 TABLE 2021/6/14 16:40:39 2021/6/14 16:44:45 2021-06-14:16:40:39 VALID N N N 1 14 EMP 87108 87108 TABLE 2013/8/24 12:04:21 2022/1/23 16:31:06 2022-01-23:16:31:06 VALID N N N 1 15 SEQ_BJSXT 90884 SEQUENCE 2022/1/23 16:04:25 2022/1/23 16:04:25 2022-01-23:16:04:25 VALID N N N 1 16 SYN_EMP 90891 SYNONYM 2022/1/23 16:27:28 2022/1/23 16:27:28 2022-01-23:16:27:28 INVALID N N N 1 17 BIN$1jx+fvk2Dy3gUwajqMCaPA==$1 90885 90885 INDEX 2022/1/23 16:04:38 2022/1/23 16:27:53 2022-01-23:16:27:53 VALID N N N 4 18 V_EMP 90883 VIEW 2022/1/23 16:03:58 2022/1/23 16:03:58 2022-01-23:16:03:58 INVALID N N N 1 19 BIN$1jx+fvk3Dy3gUwajqMCaPA==$1 87109 87109 INDEX 2013/8/24 12:04:21 2022/1/23 16:27:53 2022-01-23:16:27:53 VALID N N N 4 20 PRO_SUPPLEMENT_LEAVE_MAPPING 90886 PROCEDURE 2022/1/23 16:10:40 2022/1/23 16:10:40 2022-01-23:16:10:40 INVALID N N N 1
回收站：
SQL> show recyclebin
SQL>
二、删除表
SQL> drop table emp;
Table dropped.
索引状态：
INDEX_NAME TABLE_NAME STATUS 1 SYS_IL0000089251C00003$$ TEST VALID 2 IDX_T_ID T VALID 3 PK_EMPNO_OGG EMP_OGG VALID 4 PK_DEPTNO_OGG DEPT_OGG VALID 5 PK_DEPT DEPT VALID
视图状态：
SQL> select view_name,view_type from user_views; VIEW_NAME VIEW_TYPE ------------------------------ ------------------------------ V_EMP
序列状态：
SQL> select * from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- SEQ_BJSXT 1 99999999 2 Y N 50 20101
同义词状态：
SQL> SELECT * FROM USER_SYNONYMS; SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- SYN_EMP SCOTT EMP
user_objects状态查询：
可以看到用户表以及索引就已经被删除了
select * from user_objects;
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME 1 PK_DEPT 87107 87107 INDEX 2013/8/24 12:04:21 2013/8/24 12:04:21 2013-08-24:12:04:21 VALID N N N 4 2 DEPT 87106 87106 TABLE 2013/8/24 12:04:21 2013/8/24 12:04:21 2013-08-24:12:04:21 VALID N N N 1 3 BONUS 87110 87110 TABLE 2013/8/24 12:04:21 2013/8/24 12:04:21 2013-08-24:12:04:21 VALID N N N 1 4 SALGRADE 87111 87111 TABLE 2013/8/24 12:04:21 2013/8/24 12:04:21 2013-08-24:12:04:21 VALID N N N 1 5 EMP_OGG 88789 88789 TABLE 2020/12/23 13:14:06 2020/12/23 13:14:30 2020-12-23:13:14:06 VALID N N N 1 6 DEPT_OGG 88790 88790 TABLE 2020/12/23 13:14:07 2020/12/23 13:14:42 2020-12-23:13:14:07 VALID N N N 1 7 PK_EMPNO_OGG 88791 88791 INDEX 2020/12/23 13:14:11 2020/12/23 13:14:11 2020-12-23:13:14:11 VALID N N N 4 8 PK_DEPTNO_OGG 88792 88792 INDEX 2020/12/23 13:14:12 2020/12/23 13:14:12 2020-12-23:13:14:12 VALID N N N 4 9 TEST 89251 89251 TABLE 2021/6/13 16:12:42 2021/6/13 16:12:42 2021-06-13:16:12:42 VALID N N N 1 10 IDX_T_ID 89274 89274 INDEX 2021/6/14 16:44:43 2021/6/14 16:44:43 2021-06-14:16:44:43 VALID N N N 4 11 SYS_IL0000089251C00003$$ 89253 89253 INDEX 2021/6/13 16:12:42 2021/6/13 16:12:42 2021-06-13:16:12:42 VALID N Y N 4 12 SYS_LOB0000089251C00003$$ 89252 89252 LOB 2021/6/13 16:12:42 2021/6/13 16:12:42 2021-06-13:16:12:42 VALID N Y N 8 13 T 89273 89273 TABLE 2021/6/14 16:40:39 2021/6/14 16:44:45 2021-06-14:16:40:39 VALID N N N 1 14 SEQ_BJSXT 90884 SEQUENCE 2022/1/23 16:04:25 2022/1/23 16:04:25 2022-01-23:16:04:25 VALID N N N 1 15 SYN_EMP 90891 SYNONYM 2022/1/23 16:27:28 2022/1/23 16:27:28 2022-01-23:16:27:28 INVALID N N N 1 16 V_EMP 90883 VIEW 2022/1/23 16:03:58 2022/1/23 16:03:58 2022-01-23:16:03:58 INVALID N N N 1 17 PRO_SUPPLEMENT_LEAVE_MAPPING 90886 PROCEDURE 2022/1/23 16:10:40 2022/1/23 16:10:40 2022-01-23:16:10:40 INVALID N N N 1
结合测试得出结论
1.删除一张表，肯定会删除一张表中的数据和这个对象
2.删除表后，同义词，视图，序列不会被删除
3.删除表后，索引会被删除，不会失效。索引失效只有row_id发生重新排序的时候才会出现，比如分区进行增删，表move，压缩或者shrink的时候会出现索引失效的情况
图中的答案应为：ACD
