非分区表与分区表相互转换

1.非分区表转分区表

(1)--普通表

 table T1
(
  OBJECT_ID   NUMBER not null,
  OBJECT_NAME VARCHAR2(128),
  OWNER       VARCHAR2(30),
  STATUS      VARCHAR2(7)

--分区表

create table PT1
(
  OBJECT_ID   NUMBER,
  OBJECT_NAME VARCHAR2(128),
  OWNER       VARCHAR2(30),
  STATUS      VARCHAR2(7)
)
partition by hash (OBJECT_ID)
(
  partition SYS_P105
    tablespace DATA
);

(2)-

SQL> select count(*) from t1;

  COUNT(*)
----------
  29218402

SQL> select count(*) from pt1;

  COUNT(*)
----------
0

(3)--在线检查是否符合分区的条件

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'T1', DBMS_REDEFINITION.CONS_USE_PK);

(4)--开始在线定义SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T1','PT1');

执行完这个命令后可以看到后台变为（多了一份表的容量）

以及在当前schema表下可以看到多了两个表MLOG$_T1,RUPD$_T1;

SQL> select count(*) from t1;

  COUNT(*)
----------
  29218402

Elapsed: 00:00:00.60
SQL> select count(*) from pt1;

  COUNT(*)
----------
  29218402

(5)--在向t1表插入两行数据；

SQL> insert into t1
  2  select sequence1.nextval,object_name,owner,status from dba_objects
  3  where rownum<3;

2 rows created.

SQL> select count(*) from t1;

  COUNT(*)
----------
  29218404

Elapsed: 00:00:00.60
SQL> select count(*) from pt1;

  COUNT(*)
----------
  29218402

(5)执行两表数据同步--exec dbms_redefinition.sync_interim_table(USER, 'T1','PT1');

SQL> select count(*) from t1;

  COUNT(*)
----------
  29218404

Elapsed: 00:00:00.60
SQL> select count(*) from pt1;

  COUNT(*)
----------
  29218404

(6)结束--EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user,'T1','PT1');

再后台可以看到pt1与t1进行自动相互转换名字；

create table T1
(
  OBJECT_ID   NUMBER,
  OBJECT_NAME VARCHAR2(128),
  OWNER       VARCHAR2(30),
  STATUS      VARCHAR2(7)
)
partition by hash (OBJECT_ID)
(
  partition SYS_P105
    tablespace DATA
);

2--------------------------------------------------------------分区转非分区(exchange)------------------------------------------------------------

例子alter table pt1

exchange partition sys_p105 with table t1;--将t1的内容转化到PT1的单个分区sys_p105中

再次执行上面的命令，可将分区表的内容转回t1表;

注意:当出现分区表中有多个分区表含数据时

执行分区转分区按上面的命令会报错，这种 情况可以先将分区表进行coalesce到一个分区为止再进行转换；

ALTER TABLE pt1
COALESCE PARTITION