Oracle 12c 新特性 --- 存储过程dbms_redefinition.redef_table在线重新定义表的存储属性

概念

REDEF_TABLE is a new procedure in the DBMS_REDEFINITION package which allows a one-step operation to easily redefine a table or partition under the following specific set of conditions:
REDEF_TABLE是DBMS_REDEFINITION包中的一个新程序，它允许one-step操作在以下特定条件下轻松地重新定义一个表或分区:
•	Tablespace changes for table or partition, index, and LOB columns.
•	Compression type changes for table or partition, index key, and LOB columns.
•	STORE AS SECUREFILE or BASICFILE for LOB columns.
•	表或分区、索引和LOB列的表空间更改。
•	对表或分区、索引键和LOB列的压缩类型更改。
•	存储为LOB列的SECUREFILE或BASICFILE。

You can use the REDEF_TABLE procedure in the DBMS_REDEFINITION package to perform online redefinition of a table's storage properties.
您可以在DBMS_REDEFINITION包中使用REDEF_TABLE程序来执行对表存储属性的在线重新定义。
The REDEF_TABLE procedure enables you to perform online redefinition a table's storage properties in a single step when you want to change the following properties:
REDEF_TABLE程序允许您在希望更改以下属性时，在单个步骤中执行在线重新定义表的存储属性:
Tablespace changes, including a tablespace change for a table, partition, index, or LOB columns
表空间更改，包括表、分区、索引或LOB列的表空间更改

Compression type changes, including a compression type change for a table, partition, index key, or LOB columns

压缩类型更改，包括表、分区、索引键或LOB列的压缩类型更改
For LOB columns, a change to SECUREFILE or BASICFILE storage
对于LOB列，更改为SECUREFILE或BASICFILE存储

When your online redefinition operation is not limited to these changes, you must perform online redefinition of the table using multiple steps. The steps include invoking multiple procedures in the DBMS_REDEFINITION package, including the following procedures: CAN_REDEF_TABLE, START_REDEF_TABLE, COPY_TABLE_DEPENDENTS, and FINISH_REDEF_TABLE.

当您的在线重新定义操作不受这些更改的限制时，您必须使用多个步骤对表进行在线重新定义。这些步骤包括在DBMS_REDEFINITION包中调用多个过程，包括以下过程:CAN_REDEF_TABLE, START_REDEF_TABLE, COPY_TABLE_DEPENDENTS, and FINISH_REDEF_TABLE。

实验

1) 创建表 print_ads 默认表空间users 
[leo@www.cndba.cn ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 26 12:56:14 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> conn test/test@pdbcndba
Connected.

SQL> create table print_ads( AD_ID NUMBER(6),AD_TEXT CLOB);

Table created.

SQL> SET LONG 500
SQL> select dbms_metadata.get_ddl('TABLE','PRINT_ADS') from dual;

DBMS_METADATA.GET_DDL('TABLE','PRINT_ADS')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST"."PRINT_ADS"
   (	"AD_ID" NUMBER(6,0),
	"AD_TEXT" CLOB
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"
 LOB ("AD_TEXT") STORE AS SECUREFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES )

DBMS_METADATA.GET_DDL('TABLE','PRINT_ADS')
--------------------------------------------------------------------------------

2） 创建索引指定表空间test
SQL> CREATE INDEX print_ads_ix
    ON print_ads (ad_id)
  TABLESPACE test;  2    3  

Index created.


SQL> select dbms_metadata.get_ddl('INDEX','PRINT_ADS_IX') from dual;

DBMS_METADATA.GET_DDL('INDEX','PRINT_ADS_IX')
--------------------------------------------------------------------------------

  CREATE INDEX "TEST"."PRINT_ADS_IX" ON "TEST"."PRINT_ADS" ("AD_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "TEST"
  
3）重新定义表如下:
该表压缩了高级行压缩。
表的表空间从USERS更改为TEST。本例假设USERS表空间存在。
该索引压缩了 COMPRESS 1压缩。
该索引的表空间由TEST更改为USERS。
表中的LOB列被压缩COMPRESS HIGH 。
LOB列的表空间从USERS更改为TEST。此示例假设TEST表空间存在。
将LOB列更改为SecureFiles LOB存储。

4）DBMS_REDEFINITION.REDEF_TABLE 在线重定义表的存储属性
SQL> BEGIN
  DBMS_REDEFINITION.REDEF_TABLE(
    uname                      => 'TEST',
    tname                      => 'PRINT_ADS',
    table_compression_type     => 'ROW STORE COMPRESS ADVANCED',
    table_part_tablespace      => 'TEST',
    index_key_compression_type => 'COMPRESS 1',
    index_tablespace           => 'USERS',
    lob_compression_type       => 'COMPRESS HIGH',
    lob_tablespace             => 'TEST',
    lob_store_as               => 'SECUREFILE');
END;
/  

PL/SQL procedure successfully completed.
 5）可以看到表，索引默认表空间改变，LOB 字段存储在test，表索引的压缩属性也改变
SQL> select dbms_metadata.get_ddl('TABLE','PRINT_ADS') from dual;

DBMS_METADATA.GET_DDL('TABLE','PRINT_ADS')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST"."PRINT_ADS"
   (	"AD_ID" NUMBER(6,0),
	"AD_TEXT" CLOB
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED LOGGING
  TABLESPACE "TEST"
 LOB ("AD_TEXT") STORE AS SECUREFILE (
  TABLESPACE "TEST" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  COMPRESS HIGH  KEEP_DUPLICATES )

DBMS_METADATA.GET_DDL('TABLE','PRINT_ADS')
--------------------------------------------------------------------------------


SQL> select dbms_metadata.get_ddl('INDEX','PRINT_ADS_IX') from dual;

DBMS_METADATA.GET_DDL('INDEX','PRINT_ADS_IX')
--------------------------------------------------------------------------------

  CREATE INDEX "TEST"."PRINT_ADS_IX" ON "TEST"."PRINT_ADS" ("AD_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS 1
  TABLESPACE "USERS"

参考文档：

http://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN11677

http://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN11677