本文介绍如何使用Oracle DBMS_REDEFINITION包进行在线重定义，将非分区表转换为分区表的过程。包括使用ROWID方式进行重定义的具体步骤，如开始重定义、同步临时表中的数据、结束重定义等。
DBMS_REDEFINITION.CONS_USE_PK ---主键重定义
DBMS_REDEFINITION.CONS_USE_ROWID ---rowid重定义
对于采用了ROWID方式重定义的表，包括了一个隐含列M_ROW$$。推荐使用下列语句经隐含列置为UNUSED状态或删除。
ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);
ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;
1、创建分区表
SQL> select *from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
原表t1 ，表上没有主键，只能通过rowid方式在线重定义做分区表；
SQL> select count(*)From t1;
COUNT(*)
----------
1719910
SQL> create table t2 partition by range(hiredate)
2 (partition p1 values less than (to_date('1980-01-01','yyyy-mm-dd')) tablespace users ,
3 partition p2 values less than(to_date('1981-01-01','yyyy-mm-dd')) tablespace users ,
4 partition p3 values less than(to_date('1982-01-01','yyyy-mm-dd')) tablespace users ,
5 partition p4 values less than(maxvalue) tablespace users )
6 as select * from T1 where 0=1;
表已创建。
2、开始在线重定义
----开并行
alter session force parallel dml parallel8;
alter session force parallel query parallel8;
alter session force parallel ddl parallel4;
检查是否能够被重定义
SQL> begin2 dbms_redefinition.can_redef_table('SCOTT','T1',DBMS_REDEFINITION.CONS_USE_ROWID);
3 end;
4 /
PL/SQL 过程已成功完成。
开始重定义
SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE('scott','T1','T2',null,DBMS_REDEFINITION.CONS_USE_ROWID);
3 END;
4 /
PL/SQL 过程已成功完成。
拷贝关联对象
SQL> DECLARE
2 num_errors PLS_INTEGER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'SCOTT',
5 orig_table => 'T1',
6 int_table => 't2',
7 copy_indexes => 0,
8 copy_triggers => TRUE,
9 copy_constraints => FALSE,
10 copy_privileges => TRUE,
11 ignore_errors => FALSE,
12 num_errors => num_errors,
13 copy_statistics => FALSE);
14 END;
15 /
PL/SQL 过程已成功完成。
同步临时表中的数据
SQL> begin
2 dbms_redefinition.sync_interim_table('SCOTT','T1','T2');
3 end;
4 /
PL/SQL 过程已成功完成。
结束重定义
SQL> begin
2 dbms_redefinition.finish_redef_table('SCOTT','T1','T2');
3 end;
4 /
PL/SQL 过程已成功完成。
3、查看分区表
SQL> SELECT TABLE_NAME, PARTITION_NAME ,high_value FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'T1' ORDER BY 2;
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
T1 P1 TO_DATE(' 1980-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T1 P2 TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T1 P3 TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T1 P4 MAXVALUE
SQL> select *from t1 where rownum<10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
418 SMITH CLERK 7902 17-12月-80 800 20
430 SMITH CLERK 7902 17-12月-80 800 20
431 SMITH CLERK 7902 17-12月-80 800 20
443 SMITH CLERK 7902 17-12月-80 800 20
455 SMITH CLERK 7902 17-12月-80 800 20
467 SMITH CLERK 7902 17-12月-80 800 20
479 SMITH CLERK 7902 17-12月-80 800 20
491 SMITH CLERK 7902 17-12月-80 800 20
503 SMITH CLERK 7902 17-12月-80 800 20
已选择9行。
查看表的定义：
SQL> select dbms_metadata.get_ddl('TABLE','T1','SCOTT') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','T1','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T1"
( "EMPNO" NUMBER(8,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("HIREDATE")
(PARTITION "P1" VALUES LESS THAN (TO_DATE(' 1980-01-01 00:00:00', 'SYYYY-MM-DD HH24
:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFA
ULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P2" VALUES LESS THAN (TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FL
ASH_CACHE DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P3" VALUES LESS THAN (TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR
=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE D
EFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P4" VALUES LESS THAN (MAXVALUE)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "USERS" NOCOMPRESS )
10g的数据库
SQL> select * From v$version;
BANNER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0
Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select count(*)From t1;
COUNT(*)
----------
78848
create table t2 partition by range(hiredate)
(partition p1 values less than (to_date('1980-01-01','yyyy-mm-dd')) tablespace users ,
partition p2 values less than(to_date('1981-01-01','yyyy-mm-dd')) tablespace users ,
partition p3 values less than(to_date('1982-01-01','yyyy-mm-dd')) tablespace users ,
partition p4 values less than(maxvalue) tablespace users )
as select * from T1 where 0=1;
create table t2 partition by range(hiredate)
(partition p1 values less than (to_date('1980-01-01','yyyy-mm-dd')) tablespace users ,
partition p2 values less than(to_date('1981-01-01','yyyy-mm-dd')) tablespace users ,
partition p3 values less than(to_date('1982-01-01','yyyy-mm-dd')) tablespace users ,
partition p4 values less than(maxvalue) tablespace users )
SQL> 2 3 4 5 6 as select * from T1 where 0=1;
Table created.
SQL> begin
2 dbms_redefinition.can_redef_table('SCOTT','T1',DBMS_REDEFINITION.CONS_USE_ROWID);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE('scott','T1','T2',null,DBMS_REDEFINITION.CONS_USE_ROWID);
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> begin
2 dbms_redefinition.sync_interim_table('SCOTT','T1','T2');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_redefinition.finish_redef_table('SCOTT','T1','T2');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT TABLE_NAME, PARTITION_NAME ,high_value FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'T1' ORDER BY 2;
TABLE_NAME PARTITION_NAME
HIGH_VALUE
-------------------- -------------------- --------------------------------------------------------------------------------
T1 P1 TO_DATE(' 1980-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T1 P2 TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T1 P3 TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T1 P4 MAXVALUE
SQL> select dbms_metadata.get_ddl('TABLE','T1','SCOTT') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','T1','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T1"
( "SYS_C00009_14052219:29:44$" VARCHAR2(255),
"EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("HIREDATE")
(PARTITION "P1" VALUES LESS THAN (TO_DATE(
' 1980-01-01 00:00:00', 'SYYYY-MM-DD HH2
4:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN
S 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAX
EXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROU
PS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P2" VALUES LESS THAN (TO_DATE(' 1981-01-01 00:
00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
ALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P3" VALUES LESS THAN (TO_DATE(' 1982-01-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTEN
TS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_
POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P4" VALUES LESS THAN (MAXVALUE)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 2
55
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXT
ENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS
1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS )
我们发现"SYS_C00009_14052219:29:44$" VARCHAR2(255), 所以需要删除
SQL> ALTER TABLE t1 SET UNUSED ("SYS_C00009_14052219:29:44$");
Table altered.
SQL> ALTER TABLE t1 DROP UNUSED COLUMNS;
Table altered.
说明此在10g时候有虚拟伪劣，但是11g后此列自动禁用
扫一扫
8995
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
