【体系结构】rename表操作后index，synonym ，view是否依然可用
先说结论：
- Oracle数据库自动把旧的对象上的完整性约束，索引，和权限迁移到新的对象上面。
- Oracle数据库上涉及与命名后的对象有关的例如：视图，同义词和存储过程和函数都会失效。
1、使用scott用户创建一个测试表test_rename，和测试索引idx_test_rename_empno
SQL> create table test_rename as select * from emp; Table created. SQL> create index idx_test_rename_empno on test_rename(empno); Index created.
2、分析执行计划
SQL> explain plan for select * from test_rename where empno>7500; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2223029709 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 418 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_RENAME | 11 | 418 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_TEST_RENAME_EMPNO | 11 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------- 2 - access("EMPNO">7500) 14 rows selected.
3、查看index情况
SQL> COL INDEX_NAME FOR A25 SQL> COL TABLE_OWNER FOR A15 SQL> COL TABLE_NAME FOR A15 SQL> select index_name,table_owner,table_name,status from user_indexes where index_name='IDX_TEST_RENAME_EMPNO'; INDEX_NAME TABLE_OWNER TABLE_NAME STATUS ------------------------- --------------- --------------- -------- IDX_TEST_RENAME_EMPNO SCOTT TEST_RENAME VALID
4、进行rename操作
SQL> rename test_rename to test; Table renamed.
5、查看索引情况
SQL> select index_name,table_owner,table_name,status from user_indexes where index_name='IDX_TEST_RENAME_EMPNO'; INDEX_NAME TABLE_OWNER TABLE_NAME STATUS ------------------------- --------------- --------------- -------- IDX_TEST_RENAME_EMPNO SCOTT TEST VALID
6、再次查看执行计划
SQL> explain plan for select * from test where empno>7500; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------- Plan hash value: 3341093940 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 418 | 2 (0)| 00:00:01| | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 11 | 418 | 2 (0)| 00:00:01| |* 2 | INDEX RANGE SCAN | IDX_TEST_RENAME_EMPNO| 11 | | 1 (0)| 00:00:01| ------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------- 2 - access("EMPNO">7500) 14 rows selected.
总结：由此可知：rename 表后，相应的索引会随着表的变动同步变动。依旧是有效的。不需要我们手动修改。
1、使用scott给test表创建一个同义词s_test
SQL> create synonym s_test for test; Synonym created.
2、查看同义词信息
SQL> COL SYNONYM_NAME FOR A10 SQL> COL TABLE_OWNER FOR A10 SQL> COL TABLE_NAME FOR A10 SQL> select synonym_name,table_owner,table_name from user_synonyms; SYNONYM_NA TABLE_OWNE TABLE_NAME ---------- ---------- ---------- S_TEST SCOTT TEST
3、重命名表
SQL> rename test to test1; Table renamed.
4、查询同义词
SQL> select synonym_name,table_owner,table_name from user_synonyms; SYNONYM_NA TABLE_OWNE TABLE_NAME ---------- ---------- ---------- S_TEST SCOTT TEST SQL> select * from s_test; select * from s_test * ERROR at line 1: ORA-00980: synonym translation is no longer valid
5、将表名改回来
SQL> rename test1 to test; Table renamed. SQL> select * from s_test; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected.
总结：rename表后，同义词不会同步，因此rename表后，同义词失效。
1、创建视图
SQL> create view v_test as select empno,ename,sal from test; View created.
2、rename表
SQL> rename test to test1; Table renamed.
3、查看视图是否失效
SQL> select * from v_test; select * from v_test * ERROR at line 1: ORA-04063: view "SCOTT.V_TEST" has errors
4、表名改回来
SQL> rename test1 to test; Table renamed.
5、再次查询视图
SQL> select * from v_test; EMPNO ENAME SAL ---------- ---------- ---------- 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 14 rows selected.
总结：rename后，view同同义词一样失效。
