使用用rowid 在线重定义分区表

在线重定义有两种方法：一种是基于主键、另一种是基于ROWID。ROWID的方式不能用于索引组织表，而且重定义后会存在隐藏列M_ROW$$，默认采用主键的方式

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> begin
  2   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后此列自动禁用