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

最新推荐文章于 2025-10-19 11:24:15 发布

原创最新推荐文章于 2025-10-19 11:24:15 发布 · 5.6k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#object #table #exchange #sql #user #insert

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