12C的在线重定义,有新的特性,可以通过一条命令进行在线重定义,而不会像11g那样,要好几个步骤。
12c的官方文档中是这样说的
129.7.8 REDEF_TABLE Procedure
This procedure provides a single interface that integrates several redefinition steps including the CAN_REDEF_TABLE Procedure, the START_REDEF_TABLE Procedure, the COPY_TABLE_DEPENDENTS Procedure and the FINISH_REDEF_TABLE Procedure.
This procedure can change data storage properties including tablespaces (for table, partition, subpartition, index, LOB column), compress type (for table, partition, subpartition, index, LOB column) and STORE_AS clause for the LOB column.
语法:
DBMS_REDEFINITION.REDEF_TABLE (
uname IN VARCHAR2,
tname IN VARCHAR2,
table_compression_type IN VARCHAR2 := NULL,
table_part_tablespace IN VARCHAR2 := NULL,
index_key_compression_type IN VARCHAR2 := NULL,
index_tablespace IN VARCHAR2 := NULL,
lob_compression_type IN VARCHAR2 := NULL,
lob_tablespace IN VARCHAR2 := NULL,
lob_store_as IN VARCHAR2 := NULL,
refresh_dep_mviews IN VARCHAR2 := 'N',
dml_lock_timeout IN PLS_INTEGER := NULL);
创建测试用的用户,表
create user c##bb identified by oracle;
alter user c##bb default tablespace users;
grant resource,connect to c##bb container=all;
grant dba to c##bb container=all;
SYS@win12c>create user c##bb identified by oracle;
用户已创建。
SYS@win12c>alter user c##bb default tablespace users;
用户已更改。
SYS@win12c>grant resource,connect to c##bb container=all;
授权成功。
SYS@win12c>grant dba to c##bb container=all;
授权成功。
SYS@win12c>
conn c##bb/oracle@win12c
create table t1 (id number) tablespace users;
declare i integer;
begin
for i in 1..100 loop
insert into t1 values(i);
end loop;
commit;
end;
SYS@win12c>conn c##bb/oracle@win12c
已连接。
C##BB@win12c>create table t1 (id number) tablespace users;
表已创建。
C##BB@win12c>
C##BB@win12c>declare i integer;
2 begin
3 for i in 1..100 loop
4 insert into t1 values(i);
5 end loop;
6 commit;
7 end;
8 /
PL/SQL 过程已成功完成。
C##BB@win12c>
C##BB@win12c>select count(*) from c##bb.t1;
COUNT(*)
----------
100
C##BB@win12c>
C##BB@win12c>select owner,table_name,tablespace_name from dba_tables where owner='C##BB';
OWNER TABLE_NAME TABLESPACE
---------- ---------- ----------
C##BB T1 USERS
C##BB@win12c>
创建测试用的表空间,进行在线重定义。通过在线重定义,将表从一个表空间转移到另一个表空间
create tablespace bb datafile 'E:\ORADATA\ORCL\bb01.DBF' size 100M;
exec dbms_redefinition.redef_table('C##BB','T1','BB');
BEGIN
DBMS_REDEFINITION.REDEF_TABLE(
uname => 'C##BB',
tname => 'T1',
table_compression_type => NULL,
table_part_tablespace => 'BB',
index_key_compression_type => NULL,
index_tablespace => NULL,
lob_compression_type => NULL,
lob_tablespace => NULL,
lob_store_as => NULL);
END;
C##BB@win12c>create tablespace bb datafile 'E:\ORADATA\ORCL\bb01.DBF' size 100M;
表空间已创建。
进行在线重定义
C##BB@win12c>BEGIN
2 DBMS_REDEFINITION.REDEF_TABLE(
3 uname => 'C##BB',
4 tname => 'T1',
5 table_compression_type => NULL,
6 table_part_tablespace => 'BB',
7 index_key_compression_type => NULL,
8 index_tablespace => NULL,
9 lob_compression_type => NULL,
10 lob_tablespace => NULL,
11 lob_store_as => NULL);
12 END;
13 /
PL/SQL 过程已成功完成。
再次查看表C##BB.T1的表空间,由USERS变成了BB
C##BB@win12c>select owner,table_name,tablespace_name from dba_tables where owner='C##BB';
OWNER TABLE_NAME TABLESPACE_NAME
---------- ---------- --------------------
C##BB T1 BB
C##BB@win12c>
END