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