1、创建测试源表（以scott.emp为例）
SQL> set long 9999
SQL> set pagesize 999
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "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),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
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" ENABLE,
SUPPLEMENTAL LOG GROUP "GGS_EMP_87108" ("EMPNO") ALWAYS,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") DISABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
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"
修改原emp表创建语句，定义分区列为HIREDATE：
CREATE TABLE "SCOTT"."EMP_HD"
( "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),
CONSTRAINT "PK_HD_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
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" ENABLE
) SEGMENT CREATION IMMEDIATE
PARTITION BY RANGE ("HIREDATE") INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION 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
NOCOMPRESS LOGGING
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";
执行在线重定义:
SQL> exec dbms_redefinition.start_redef_table('SCOTT', 'EMP', 'EMP_HD');
PL/SQL procedure successfully completed.
SQL> select INDEX_NAME from dba_indexes where table_name = 'EMP_HD';
INDEX_NAME
------------------------------
PK_EMP
SQL> exec dbms_redefinition.sync_interim_table('SCOTT', 'EMP', 'EMP_HD');
PL/SQL procedure successfully completed.
SQL> exec dbms_redefinition.finish_redef_table('SCOTT', 'EMP', 'EMP_HD');
PL/SQL procedure successfully completed.
SQL> select count(*) from SCOTT.EMP_HD;
COUNT(*)
----------
14
SQL> select table_name, partition_name from dba_tab_partitions where table_name = 'EMP';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
EMP SYS_P41
EMP SYS_P42
EMP SYS_P43
EMP SYS_P44
EMP SYS_P45
EMP SYS_P46
EMP SYS_P47
EMP SYS_P48
EMP SYS_P49
EMP SYS_P50
EMP SYS_P51
EMP SYS_P52
12 rows selected.
至此，scott.emp已经被重定义为分区表，分区列为HIREDATE，下面进行重定义分区列实验，将分区列重定义为EMPNO；
2、重定义准备
SQL> select INDEX_NAME from dba_indexes where table_name = 'EMP';
INDEX_NAME
------------------------------
PK_HD_EMP
创建临时表，临时表跟源表结构一致，但分区列改为：EMPNO
CREATE TABLE "SCOTT"."EMP_NO"
( "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),
CONSTRAINT "PK_NO_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
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" ENABLE
) SEGMENT CREATION IMMEDIATE
PARTITION BY RANGE ("EMPNO")
(partition p1 values less than(7500),
partition p2 values less than(7750),
partition p3 values less than(8000))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
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";
检查重定义的合理性；
SQL> exec dbms_redefinition.can_redef_table('SCOTT', 'EMP')
3、开始重定义
重定义表结构
BEGIN
DBMS_REDEFINITION.start_redef_table('SCOTT','EMP','EMP_NO');
END;
/
回滚：exec dbms_redefinition.abort_redef_table(‘SCOTT’,‘EMP’,‘EMP_NO’);
同步临时表
BEGIN
dbms_redefinition.sync_interim_table('SCOTT','EMP','EMP_NO');
END;
/
检查临时表分区及索引是否正常
SQL> select table_name, partition_name from dba_tab_partitions where table_name = 'EMP_NO';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
EMP_NO P1
EMP_NO P2
EMP_NO P3
SQL> select INDEX_NAME from dba_indexes where table_name = 'EMP_NO';
INDEX_NAME
------------------------------
PK_NO_EMP
结束重定义
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','EMP','EMP_NO');
END;
/
检查
SQL> select partitioned from dba_tables where table_name = 'EMP';
PAR
---
YES
SQL> select INDEX_NAME from dba_indexes where table_name = 'EMP';
INDEX_NAME
------------------------------
PK_NO_EMP
SQL> select table_name, partition_name from dba_tab_partitions where table_name = 'EMP';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
EMP P1
EMP P2
EMP P3
select * from scott.EMP partition(P1);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
