这篇文章讨论一下在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--
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23135684/viewspace-1753024/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
