oracle rename table 相關index view constraint synonym等的變化

首先ORACLE官方文件對RENAME操作的說明如下：

 

Use theRENAME statement to rename a table, view, sequence, or private synonym.

• Oracle Database automatically transfers integrity constraints, indexes, and grants on the old object to the new object.

• Oracle Database invalidates all objects that depend on the renamed object, such as views, synonyms, and stored procedures and functions that refer to a renamed table.

［可以使用rename語句來rename一個表，檢視，序列或私有同義詞］。

.Oracle自動把舊的物件上的完整性約束，索引，和許可權遷移到新的物件上面。

.Oracle資料庫上涉及與命名後的物件有關的例如:檢視，同義詞和儲存過程和函式都會失效。

1.使用scott使用者建立一個測試表e1和測試索引e1_eno.

SQL> create table e1 as select * from emp;

Table created.

SQL> create index e1_eno on e1(empno);

Index created.

2.分析執行計劃。

SQL> explain plan for select * from e1 where empno>7500;

Explained.
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    12 |  1044 |     2   (0)| 00:0
0:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| E1     |    12 |  1044 |     2   (0)| 00:0
0:01 |

|*  2 |   INDEX RANGE SCAN          | E1_ENO |    12 |       |     1   (0)| 00:0
0:01 |

3.檢視index情況。

SQL> select index_name,table_owner,table_name,status from user_indexes where index_name='E1_ENO';

INDEX_NAME                     TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME                     STATUS
------------------------------ --------
E1_ENO                         SCOTT
E1                             VALID

4.進行rename操作.

SQL> rename e1 to e2;

Table renamed.

5.查詢索引情況：

SQL> select index_name,table_owner,table_name,status from user_indexes where index_name='E1_ENO';

INDEX_NAME                     TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME                     STATUS
------------------------------ --------
E1_ENO                         SCOTT
E2                             VALID
6.按照第二步進行分析，執行計劃如下：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    12 |  1044 |     2   (0)| 00:0
0:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| E2     |    12 |  1044 |     2   (0)| 00:0
0:01 |

|*  2 |   INDEX RANGE SCAN          | E1_ENO |    12 |       |     1   (0)| 00:0

發現更改表名後，索引也同時跟著變動，依舊有效。

 

7.再次更換為e1看看索引情況：

SQL> rename e2 to e1;

Table renamed.

SQL> select index_name,table_owner,table_name,status from user_indexes where index_name='E1_ENO';

INDEX_NAME                     TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME                     STATUS
------------------------------ --------
E1_ENO                         SCOTT
E1                             VALID
發現索引再次隨著表的更改也進行了同步變動。

8.根據第2步再次檢視執行計劃：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    12 |  1044 |     2   (0)| 00:0
0:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| E1     |    12 |  1044 |     2   (0)| 00:0
0:01 |

|*  2 |   INDEX RANGE SCAN          | E1_ENO |    12 |       |     1   (0)| 00:0
0:01 |

--------------------------------------------------------------------------------

總結：由此可知：rename 表後，相應的索引會隨著表的變動同步變動。依舊是有效的。不需要我們手動修改。

 

 

我們也可以測試一下rename 表後，同義詞是否有效：

1.使用scott給e1表建立一個同義詞s_e.

create synonym s_e for e1;

2.查詢同義詞資訊

SQL> select synonym_name,table_owner,table_name from user_synonyms;

SYNONYM_NAME                   TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
S_E                            SCOTT
E1
3.重新命名

SQL> rename e1 to e2;

Table renamed.
4.查詢s_e

SQL> select synonym_name,table_owner,table_name from user_synonyms;

SYNONYM_NAME                   TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
S_E                            SCOTT
E1

SQL> select * from s_e;
select * from s_e
              *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

5.重新更改為e1.

SQL> rename e2 to e1;

Table renamed.

SQL> select synonym_name,table_owner,table_name from user_synonyms;

SYNONYM_NAME                   TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
S_E                            SCOTT
E1

6.查詢s_e

發現ok.

 

總結：rename表後，同義詞不會同步，因此rename表後，同義詞失效。

 

 

我們再測試一下rename表對檢視的影響：

1.在e1表的基礎上建立檢視v_e1

SQL> create view v_e1 as select empno,ename,sal from e1;

View created.

SQL> select * from v_e1;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
2.rename 表

SQL> rename e1 to e2;

Table renamed.
3.驗證一下view是否可用。
     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
SQL> select * from v_e1;
select * from v_e1
              *
ERROR at line 1:
ORA-04063: view "SCOTT.V_E1" has errors
4.重新改為e1

SQL> rename e2 to e1;

Table renamed.

SQL> select * from v_e1;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
5.再次驗證檢視的有效性

SQL> rename e2 to e1;

Table renamed.

SQL> select * from v_e1;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250

總結：rename後，view同同義詞一樣失效。