Oracle Redefining Tables Online
In any database system, it is occasionally necessary to modify the logical or physical structure of a table to:
(1)Improve the performance of queries or DML
(2)Accommodate application changes
(3)Manage storage
Features of Online Table Redefinition
Online table redefinition enables you to:
(1)Modify the storage parameters of a table or cluster
(2)Move a table or cluster to a different tablespace
(3)Add, modify, or drop one or more columns in a table or cluster
(4)Add or drop partitioning support (non-clustered tables only)
(5)Change partition structure
(6)Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
(7)Change physical properties of a materialized view log or an Oracle Streams Advanced Queuing queue table
(8)Add support for parallel queries
(9)Re-create a table or cluster to reduce fragmentation
(10)Change the organization of a normal table (heap organized) to an index-organized table, or do the reverse.
(11)Convert a relational table into a table with object columns, or do the reverse.
(12)Convert an object table into a relational table or a table with object columns, or do the reverse.
Note:
If it is not important to keep a table available for DML when moving it to another tablespace, you can use the simpler ALTER TABLE MOVE command.
See "Moving a Table to a New Segment or Tablespace".
Online Table Redefinition Examples
Example 1
实验说明:
HR用户new_cust表,所在表空间cust_tbs的SEGMENT SPACE手动管理,性能较差,
通过在线重定义,将HR.new_cust表从cust_tbs表空间迁移到USERS表空间;
SQL> create tablespace cust_tbs datafile '/u01/app/oracle/oradata/orcl/cust_tbs01.dbf' size 10m extent management local SEGMENT SPACE MANAGEMENT MANUAL;
SQL> conn /as sysdba
SQL> create table HR.new_cust(x number constraint pk_new_cust primary key) tablespace cust_tbs;
SQL> insert into HR.new_cust select rownum from dual connect by rownum<=5;
SQL> commit;
SQL> select tablespace_name,table_name from dba_tables where table_name like 'NEW_CUST';
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
CUST_TBS NEW_CUST
SQL> select tablespace_name,segment_space_management from dba_tablespaces where tablespace_name like 'CUST_TBS';
TABLESPACE_NAME SEGMEN
------------------------------ ------
CUST_TBS MANUAL
SQL> conn hr/hr
Connected.
SQL> select index_name,table_name from user_indexes where table_name like '%NEW_CUST';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
PK_NEW_CUST NEW_CUST
SQL> select constraint_name,table_name from user_constraints where table_name like '%NEW_CUST';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
PK_NEW_CUST NEW_CUST
1.Verify that the table is a candidate for online redefinition.
In this case you specify that the redefinition is to be done using primary keys or pseudo-primary keys.
SQL> conn / as sysdba
SQL>
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','NEW_CUST',DBMS_REDEFINITION.CONS_USE_PK);
END;
/
PL/SQL procedure successfully completed.
2.Create an interim table hr.int_NEW_CUST.
SQL> conn hr/hr
SQL> create table hr.int_NEW_CUST(x number,constraint int_pk_NEW_CUST primary key (x)) tablespace users;
3.Start the redefinition process.
SQL>
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'NEW_CUST','int_NEW_CUST','x x',dbms_redefinition.cons_use_pk);
END;
/
Note:
The best approach is to define the interim table with a primary key constraint,
use REGISTER_DEPENDENT_OBJECT to register the primary key constraint and index,
and then copy the remaining dependent objects with COPY_TABLE_DEPENDENTS.
This approach avoids errors and ensures that the redefined table always has a primary key and that the dependent object names do not change.
SQL>
BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
uname => 'HR',
orig_table => 'NEW_CUST',
int_table => 'INT_NEW_CUST',
dep_type => DBMS_REDEFINITION.CONS_INDEX,
dep_owner => 'HR',
dep_orig_name => 'pk_NEW_CUST',
dep_int_name => 'Int_pk_NEW_CUST');
END;
/
PL/SQL procedure successfully completed.
SQL>
BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
uname => 'HR',
orig_table => 'NEW_CUST',
int_table => 'INT_NEW_CUST',
dep_type => DBMS_REDEFINITION.CONS_CONSTRAINT,
dep_owner => 'HR',
dep_orig_name => 'pk_NEW_CUST',
dep_int_name => 'Int_pk_NEW_CUST');
END;
/
PL/SQL procedure successfully completed.
4.Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on hr.int_NEW_CUST.)
注:Register 索引和约束操作要在copy 之前,否则需要完成在线重定之后,手工将索引和约束rebuilt 到users表空间
SQL>
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr', 'NEW_CUST','int_NEW_CUST',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/
PL/SQL procedure successfully completed.
5.Query the DBA_REDEFINITION_ERRORS view to check for errors.
SQL> select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
no rows selected
6.Optionally, synchronize the interim table hr.int_NEW_CUST.
SQL>
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('HR', 'NEW_CUST', 'INT_NEW_CUST');
END;
/
PL/SQL procedure successfully completed.
7.Complete the redefinition.
SQL>
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('HR', 'NEW_CUST', 'INT_NEW_CUST');
END;
/
PL/SQL procedure successfully completed.
8.
SQL> CONN hr/hr
Connected.
SQL> select table_name,tablespace_name from user_tables where table_name like '%NEW_CUST';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
INT_NEW_CUST CUST_TBS
NEW_CUST USERS
SQL> set linesize 100
SQL> select index_name,tablespace_name,table_name from user_indexes where table_name like '%NEW_CUST';
INDEX_NAME TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------ ------------------------------
INT_PK_NEW_CUST CUST_TBS INT_NEW_CUST
PK_NEW_CUST USERS NEW_CUST
Example 4
Redefines a single table partition, moving it to a different tablespace
This example demonstrates redefining a single partition.
It moves the oldest partition of a range-partitioned sales table to a tablespace named TBS_LOW_FREQ.
The table containing the partition to be redefined is defined as follows:
实验说明:
将SALESTABLE表sal03q1分区,由USERS表空间在线迁移到TBS_LOW_FREQ表空间;
SQL> create user steve identified by oracle;
SQL> grant connect,resource to steve;
SQL> conn steve/oracle
SQL> CREATE TABLE salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE users
PARTITION BY RANGE(s_saledate)
(PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));
Table created.
SQL> CREATE INDEX sales_index ON salestable (s_saledate, s_productid, s_custid) LOCAL;
Index created.
SQL> select partition_name, tablespace_name from user_tab_partitions where table_name = 'SALESTABLE';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 USERS
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
Here are the steps. In the following procedure calls, note the extra argument: partition name (part_name).
1.Ensure that salestable is a candidate for redefinition.
SQL> conn / as sysdba
Connected.
SQL>
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname => 'STEVE',
tname => 'SALESTABLE',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
/
PL/SQL procedure successfully completed.
2.Create the interim table in the TBS_LOW_FREQ tablespace.
Because this is a redefinition of a range partition, the interim table is nonpartitioned.
SQL> create tablespace TBS_LOW_FREQ datafile '/u01/app/oracle/oradata/orcl/tbs_low_freq01.dbf' size 10M autoextend on maxsize 2G;
SQL> conn STEVE/oracle
SQL> CREATE TABLE int_salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE tbs_low_freq;
Table created.
3.Start the redefinition process using rowid.
SQL>
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
col_mapping => NULL,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
/
PL/SQL procedure successfully completed.
4.Manually create any local indexes on the interim table.
SQL> conn steve/oracle
Connected.
SQL> CREATE INDEX int_sales_index ON int_salestable
(s_saledate, s_productid, s_custid)
TABLESPACE tbs_low_freq;
Index created.
5.Optionally synchronize the interim table.
SQL>
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
/
PL/SQL procedure successfully completed.
6.Complete the redefinition.
SQL>
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
/
PL/SQL procedure successfully completed.
7.Wait for any long-running queries against the interim table to complete, and then drop the interim table.
The following query shows that the oldest partition has been moved to the new tablespace:
SQL> conn steve/oracle
Connected.
SQL> select partition_name, tablespace_name from user_tab_partitions where table_name = 'SALESTABLE';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 TBS_LOW_FREQ
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS
更详细的内容参见官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN015
Home / Database / Oracle Database Online Documentation 11g Release 2 (11.2) / Database Administration/Database Administrator's Guide/20 Managing Tables/Redefining Tables Online
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2143322/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29785807/viewspace-2143322/