这篇文章讨论一下在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博客 ” ，链接：http://blog.itpub.net/23135684/viewspace-1753024/，如需转载，请注明出处，否则将追究法律责任。