這篇文章討論一下在Oracle Database資料庫中將普通錶轉換為分割槽表過程中遇到的問題，這裡將普通錶轉換為分割槽表並沒有使用線上重定義的方法，使用最傳統的方法，將原始表重新命名，建立新表，把原始表中的資料INSERT到新表中，完成分割槽表的轉換。

線上重定義表的內容可參考文章《使用DBMS_REDEFINITION包執行線上重定義表》：http://blog.itpub.net/23135684/viewspace-1765128/

使用線上重定義將普通錶轉換為分割槽表的例子可參考文章《使用DBMS_REDEFINITION線上重定義表普通表為分割槽表》：http://blog.itpub.net/23135684/viewspace-661756/

下面透過測試進行說明：

C:\Users\LIUBINGLIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期一 7月 27 12:42:54 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

連線到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version where rownum<=2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production

SQL> create user test1 identified by test1 default tablespace users temporary tablespace temp;

使用者已建立。

SQL> create user test2 identified by test2 default tablespace users temporary tablespace temp;

使用者已建立。

SQL> grant connect,resource to test1;

授權成功。

SQL> grant connect,resource to test2;

授權成功。

SQL> grant create view ,create trigger to test1;

授權成功。

SQL> grant create view ,create trigger to test2;

授權成功。

SQL> connect test1/test1
已連線。
SQL> show user
USER 為 "TEST1"

SQL> create table abc(id number,insdate date,name varchar2(20),age number); <<<< 建立一張測試表。

表已建立。

SQL> alter table abc add constraint pk_id primary key(id); <<<< 為表指定一個主鍵，主鍵會對應一個唯一索引。

表已更改。

SQL> create index ins_idx01 on abc(insdate); <<<< 建立一個普通索引。

索引已建立。

SQL> create view v_abc as select * from abc; <<<< 建立一個和測試表相關的檢視。

檢視已建立。

SQL> create table abc_mirror(id number,insdate date,name varchar2(20),age number); <<<< 建立一個測試表的觸發器。

表已建立。

SQL> create or replace trigger abc_trigger
2 after insert on test1.abc
3 for each row
4 begin
5 insert into test1.abc_mirror
6 values
7 (:new.id, :new.insdate, :new.name, :new.age);
8 commit;
9 end abc_trigger;
10 /

觸發器已建立

SQL> select * from abc;

未選定行

SQL> insert into abc values(1,sysdate,'a',12);
insert into abc values(1,sysdate,'a',12)
*
第 1 行出現錯誤:
ORA-04092: COMMIT 不能在觸發器中
ORA-06512: 在 "TEST1.ABC_TRIGGER", line 5
ORA-04088: 觸發器 'TEST1.ABC_TRIGGER' 執行過程中出錯

<<<< 從這個錯誤可以說明兩點，觸發器中不能包含COMMIT關鍵字，對測試表的事務和觸發器的事務是同一個事務，結束源表的事務即結束了觸發器中的事務。第二點是，如果觸發器出現問題，那麼對基礎表的操作不能正常進行。

SQL> create or replace trigger abc_trigger
2 after insert on abc
3 for each row
4 begin
5 insert into abc_mirror
6 values
7 (:new.id, :new.insdate, :new.name, :new.age);
8 end abc_trigger;
9 /

觸發器已建立

SQL> insert into abc values(1,sysdate,'a',12);

已建立 1 行。

SQL> commit; <<<< 觸發器中的事務也結束。

<<<< 這點以前有過文章說明：《儲存過程、觸發器與事務之間的關係》：http://blog.itpub.net/23135684/viewspace-709920/

提交完成。

SQL> select * from abc;

ID INSDATE NAME AGE
---------- -------------- -------------------- ----------
1 27-7月 -15 a 12

SQL> select * from abc_mirror;

ID INSDATE NAME AGE
---------- -------------- -------------------- ----------
1 27-7月 -15 a 12

SQL> select * from v_abc;

ID INSDATE NAME AGE
---------- -------------- -------------------- ----------
1 27-7月 -15 a 12

SQL> set linesize 200
SQL> select index_name,index_type,table_name from user_indexes where table_name='ABC'; <<<< 測試表有2個相關的索引。

INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
INS_IDX01 NORMAL ABC
PK_ID NORMAL ABC

SQL> select trigger_name,table_name from user_triggers where table_name='ABC'; <<<< 測試表有1個相關的觸發器。

TRIGGER_NAME TABLE_NAME
------------------------------ ------------------------------
ABC_TRIGGER ABC

SQL> grant select on abc to test2;

授權成功。

SQL> connect test2/test2
已連線。
SQL> show user
USER 為 "TEST2"

SQL> create view v_abc2 as select * from test1.abc;

檢視已建立。

SQL> connect test1/test1
已連線。
SQL> show user
USER 為 "TEST1"
SQL> alter table test1.abc rename to test1.abcd; <<<< 對錶進行重新命名操作，to後面的表名不能加上使用者的字首。
alter table test1.abc rename to test1.abcd
*
第 1 行出現錯誤:
ORA-14047: ALTER TABLE|INDEX RENAME 不能與其他分割槽組合

SQL> alter table test1.abc rename to abcd;

表已更改。

SQL> set linesize 200

SQL> select index_name,index_type,table_name,status from user_indexes where table_name='ABCD'; <<<< 對錶進行了重新命名，相關的索引會跟著發生變化，且狀態為VALID。

INDEX_NAME INDEX_TYPE TABLE_NAME STATUS
------------------------------ --------------------------- ------------------------------ --------
INS_IDX01 NORMAL ABCD VALID
PK_ID NORMAL ABCD VALID

SQL> select trigger_name,table_name,status from user_triggers where table_name='ABCD'; <<<< 對錶進行了重新命名，相關的觸發器會跟著發生變化，狀態為ENABLED，下面的測試結果也證明狀態是可用。這以為了在該觸發器中的程式碼會將原來的ABC自動的修改為ABCD。

TRIGGER_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
ABC_TRIGGER ABCD ENABLED

SQL> select * from abcd;

ID INSDATE NAME AGE
---------- -------------- -------------------- ----------
1 27-7月 -15 a 12

SQL> select * from abc_mirror;

ID INSDATE NAME AGE
---------- -------------- -------------------- ----------
1 27-7月 -15 a 12

SQL> select * from v_abc; <<<< 雖然觸發器會跟著變，但是檢視不會跟著變。
select * from v_abc
*
第 1 行出現錯誤:
ORA-04063: view "TEST1.V_ABC" 有錯誤

SQL> insert into abcd values(2,sysdate,'b',14);

已建立 1 行。

SQL> commit;

提交完成。

SQL> select * from abc_mirror;

ID INSDATE NAME AGE
---------- -------------- -------------------- ----------
2 27-7月 -15 b 14
1 27-7月 -15 a 12

SQL> connect test2/test2
已連線。

SQL> select * from v_abc2; <<<< 其他使用者，和測試表相關的檢視也不會跟著變。
select * from v_abc2
*
第 1 行出現錯誤:
ORA-04063: view "TEST2.V_ABC2" 有錯誤

除了以上的內容外，我們還可能想知道哪些資料庫物件關聯了某張表，或者某個資料庫物件引用了哪些表等等，這裡討論一下和此相關的3個資料字典：
>public_dependency PUBLIC_DEPENDENCY lists dependencies to and from objects, by object number.

>DBA_dependencies|ALL_dependencies|USER_dependencies DBA_DEPENDENCIES describes all dependencies in the database between procedures, packages, functions, package bodies, and triggers, including dependencies on views created without any database links.

>DBA_SOURCE|ALL_SOURCE|USER_SOURCE USER_SOURCE describes the text source of the stored objects owned by the current user.

下面的SQL語句可以查詢出哪些資料庫物件關聯了指定的表：
Select owner, object_type, object_name, object_id, status
from sys.DBA_OBJECTS
where object_id in
(Select object_id
from public_dependency
connect by prior object_id = referenced_object_id
start with referenced_object_id = (Select object_id
from sys.DBA_OBJECTS
where owner = 'TEST1' -- Owner
and object_name = 'ABCD' -- table_name
/*and object_type = :type*/ -- TABLE / SYNONYM
))

下面的SQL語句可以查詢出某個資料庫物件引用了哪些表：
select de.referenced_name
from user_dependencies de
where de.name = 'ABC_TRIGGER'
and de.referenced_type = 'TABLE';

下面的SQL語句可以查詢出某個資料庫物件引用了哪些序列：
select de.referenced_name
from user_dependencies de
where de.name = 'ABC_TRIGGER'
and de.referenced_type = 'SEQUENCE';

以上的測試結果在分割槽錶轉換的情況下會出現以下的問題：
1).由於觸發器會跟著表名的變化而自動的調整程式碼，這在新分割槽表插入資料之後需要重新修改編譯相應的所有觸發器，將其調整回原來的表名，這個可以透過USER_TRIGGER.TABLE_NAME欄位來確定相關的觸發器。
2).由於檢視、儲存過程（沒有USER_PROCESSES.TABLE_NAME資料字典）等不會發生變化，所以需要手動的驗證相關的物件是否可用。
3).為了不影響表相關物件的正常使用，在調整完成後應該執行utlrp.sql指令碼，手動重新編譯一下不正確的物件，確保表相關的物件都能夠正常的使用，特別是觸發器，觸發器變得無效會使和源表相關的所有事務都無法執行。
4).另外，新的分割槽表建議重新收集一下表的統計資訊。
5).在建立分割槽表的時候，除了注意表本身外，還需要注意建立表的索引，主外來鍵，各種約束，授權等（這個可以透過PL/SQL工具在檢視錶的原始碼時很好的展現出來）。
6).為了提高將舊錶資料插入分割槽表的速度，建議先插入資料到分割槽表，最後來建立索引，主外來鍵，各種約束和建立授權，在將舊錶插入分割槽表時可以酌情考慮指定/*+APPEND PARALLEL(4) NOLOGGING*/的提示來提高資料的插入效率。

--end--

Oracle Database將普通錶轉換為分割槽表遇到的問題

相關文章