DBMS_REDEFINITION用法
来源：互联网 发布：php post 源代码 编辑：程序博客网 时间：2024/05/16 10:37
D) DBMS_REDEFINITION
example 1 :
/*非分区表自定义为分区表,同时增加字段 */
This example illustrates online redefinition of the previously created table hr.admin_emp, which at this point only contains columns: empno, ename, job, deptno.
The table isredefined as follows:
1.Online Table Redefinition Examples
qn.admin_emp
create table admin_emp (
empno NUMBER(5) PRIMARY KEY
,ename VARCHAR2(15) NOT NULL
,job VARCHAR2(10)
,deptno NUMBER(3) NOT NULL
)
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
/* 必须要权限，否则DBMS_REDEFINITION运行报错 */
grant execute on DBMS_REDEFINITION to qn ;
grant EXECUTE_CATALOG_ROLE to qn ; --Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE.
grant CREATE TABLE to qn ;
grant CREATE MATERIALIZED VIEW to qn ; /* 说明同步过程 需要物化视图 */
grant CREATE INDEX to qn ;
The CREATE TRIGGER privilege is also required to execute the COPY_TABLE_DEPENDENTS procedure.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('QN','admin_emp',DBMS_REDEFINITION.CONS_USE_PK);
END;
2.Create an interim table hr.int_admin_emp
create tablespace admin_tbs datafile size 200m ;
create tablespace admin_tb2 datafile size 200m ;
CREATE TABLE int_admin_emp
( empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
deptno NUMBER(3) NOT NULL,
bonus NUMBER (7,2) DEFAULT(1000))
PARTITION BY RANGE(empno)
(PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tb2);
3.Start the redefinition process
/* 对应字段才能同步数据，否则只能同步部分数据 */
/* dbms_redefinition.cons_use_pk 与 CONS_USE_ROWID
/* 自定义方法:cons_use_pk 根据primary key
CONS_USE_ROWID 根据rowid
*/
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('qn', 'admin_emp','int_admin_emp','empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',dbms_redefinition.cons_use_pk);
END;
4.Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on hr.int_admin_emp.)
/* 自动制复制triggers, indexes, materialized view logs, grants, , constraints */
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('QN', 'admin_emp','int_admin_emp',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/
Note that the ignore_errors argument is set to TRUE for this call. The reason is that the interim table was created with a primary key constraint,
and when COPY_TABLE_DEPENDENTS attempts to copy the primary key constraint and index from the original table, errors occurs. You can ignore these errors,
but you must run the query shown in the next step to see if there are other errors.
5.Query the DBA_REDEFINITION_ERRORS view to check for errors.
/* 要查询报错 ，不并是所有报错都是需要执行的 */
sys@RAC> select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
OBJECT_NAME BASE_TABLE_NAME DDL_TXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS_C0011092 ADMIN_EMP CREATE UNIQUE INDEX "QN"."TMP$$_SYS_C00110920" ON "QN"."INT_ADMIN_EMP" ("EMPNO")
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"
SYS_C0011090 ADMIN_EMP ALTER TABLE "QN"."INT_ADMIN_EMP" MODIFY ("ENAME" CONSTRAINT "TMP$$_SYS_C00110900" NOT NULL ENABLE NOVALIDATE)
SYS_C0011091 ADMIN_EMP ALTER TABLE "QN"."INT_ADMIN_EMP" MODIFY ("DEPTNO" CONSTRAINT "TMP$$_SYS_C00110910" NOT NULL ENABLE NOVALIDATE)
SYS_C0011092 ADMIN_EMP ALTER TABLE "QN"."INT_ADMIN_EMP" ADD CONSTRAINT "TMP$$_SYS_C00110920" 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 NOVALIDATE
6.Optionally, synchronize the interim table hr.int_admin_emp.
/* 这步会将数据从admin_emp刷新到中间表int_admin_emp，没有改变表名 */
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('QN', 'admin_emp', 'int_admin_emp');
END;
7.Complete the redefinition
/* 这步会再次装数据从admin_emp刷新到中间表int_admin_emp，出现exclusive mode模式,int_admin_emp 改为admin_emp,admin_emp改为int_admin_emp */
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('qn', 'admin_emp', 'int_admin_emp');
END;
8. Wait for any long-running queries against the interim table to complete, and then
drop the interim table.
select dbms_metadata.get_ddl('TABLE','ADMIN_EMP') from dual;
example2 .
/* 自定义转换更改字段为对像类型的表结构 object type */
This example redefines a table to change columns into object attributes. The redefined table gets a new column that is an object type.
create table CUSTOMER(
CID NUMBER PRIMARY KEY
,NAME VARCHAR2(30)
,STREET VARCHAR2(100)
,CITY VARCHAR2(30)
,STATE VARCHAR2(2)
,ZIP NUMBER(5) )
CREATE TYPE ADD_T AS OBJECT (
STREET VARCHAR2(100),
CITY VARCHAR2(30),
STATE VARCHAR2(2),
ZIP NUMBER(5,0) )
1. Verify that the table is a candidate for online redefinition. Specify that the redefinition is to be done using primary keys or pseudo-primary keys.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('QN','CUSTOMER',DBMS_REDEFINITION.CONS_USE_PK);
END;
2. Create the interim table int_customer.
CREATE TABLE INT_CUSTOMER(
CID NUMBER,
NAME VARCHAR2(30),
ADDR ADD_T);
Note that no primary key is defined on the interim table. When dependent objects are copied in step 5, the primary key constraint and index are copied.
3.Because CUSTOMER is a very large table, specify parallel operations for the next step.
alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;
4.Start the redefinition process using primary keys.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'QN',
orig_table => 'CUSTOMER',
int_table => 'INT_CUSTOMER',
col_mapping => 'cid cid, name name,ADD_T(street, city, state, zip) addr');
END;
Note that addr_t(street, city, state, zip) is a call to the object constructor
5. Copy dependent objects
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( 'QN','CUSTOMER','INT_CUSTOMER',DBMS_REDEFINITION.CONS_ORIG_PARAMS,TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
END;
Note that for this call, the final argument indicates that table statistics are to be copied to the interim table.
6.Optionally synchronize the interim table.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('QN', 'CUSTOMER', 'INT_CUSTOMER');
END;
7.7. Complete the redefinition.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('QN', 'CUSTOMER', 'INT_CUSTOMER');
END;
qn@RAC> select a.addr.state from CUSTOMER a ;
AD
--
Y
Example 3
/* 在线自定义转换时，中间表字段与原表名不一样，且有指定字段的约束 ,需要REGISTER_DEPENDENT_OBJECT来注册 */
This example addresses the situation where a dependent object must be manually created and registered.
1. Create the interim table INT_T1 and create an index Int_Index1 on column C2.
create table INT_T1 (
c2 number
,name varchar2(20) ) ;
create index Int_Index1 on int_t1(c2) ;
create table T1 (
c1 number
,name varchar2(20) ) ;
create index Index1 on t1(c1) ;
2. Ensure that T1 is a candidate for online redefinition with CAN_REDEF_TABLE, and then begin the redefinition process with START_REDEF_TABLE.
create table T1 (
c1 number
,name varchar2(20) ) ;
create index Index1 on t1(c1) ;
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('QN','T1',DBMS_REDEFINITION.CONS_USE_ROWID);
END;
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'QN',
orig_table => 'T1',
int_table => 'INT_T1',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
col_mapping => 'c1 c2,name name');
END;
3. Register the original (Index1) and interim (Int_Index1) dependent objects.
BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
uname => 'QN',
orig_table => 'T1',
int_table => 'INT_T1',
dep_type => DBMS_REDEFINITION.CONS_INDEX,
dep_owner => 'QN',
dep_orig_name => 'Index1',
dep_int_name => 'Int_Index1');
END;
4. Use COPY_TABLE_DEPENDENTS to copy the remaining dependent objects.
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( 'QN','T1','INT_T1',DBMS_REDEFINITION.CONS_ORIG_PARAMS,TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
END;
select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
5. Optionally synchronize the interim table.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('QN', 'T1','INT_T1');
END;
6. Complete the redefinition and drop the interim table
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('QN', 'T1', 'INT_T1');
END;
BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE('QN', 'T1', 'INT_T1');
END;
Example 4
/* 分区表的分区转换到其它的表空间 */
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:
create tablespace tbs_low_freq datafile size 200m ;
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')));
CREATE INDEX sales_index ON salestable (s_saledate, s_productid, s_custid) LOCAL;
1. Ensure that salestable is a candidate for redefinition.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname => 'QN',
tname => 'SALESTABLE',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
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.
CREATE TABLE int_salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE tbs_low_freq;
3. Start the redefinition process using rowid.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'QN',
orig_table => 'salestable',
int_table => 'int_salestable',
col_mapping => NULL,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
4. Manually create any local indexes on the interim table.
CREATE INDEX int_sales_index ON int_salestable(s_saledate, s_productid, s_custid) TABLESPACE tbs_low_freq;
5. Optionally synchronize the interim table.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'QN',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
6. Complete the redefinition.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'QN',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE('QN', 'salestable', 'int_salestable');
END;
example 1 :
/*非分区表自定义为分区表,同时增加字段 */
This example illustrates online redefinition of the previously created table hr.admin_emp, which at this point only contains columns: empno, ename, job, deptno.
The table isredefined as follows:
1.Online Table Redefinition Examples
qn.admin_emp
create table admin_emp (
empno NUMBER(5) PRIMARY KEY
,ename VARCHAR2(15) NOT NULL
,job VARCHAR2(10)
,deptno NUMBER(3) NOT NULL
)
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
/* 必须要权限，否则DBMS_REDEFINITION运行报错 */
grant execute on DBMS_REDEFINITION to qn ;
grant EXECUTE_CATALOG_ROLE to qn ; --Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE.
grant CREATE TABLE to qn ;
grant CREATE MATERIALIZED VIEW to qn ; /* 说明同步过程 需要物化视图 */
grant CREATE INDEX to qn ;
The CREATE TRIGGER privilege is also required to execute the COPY_TABLE_DEPENDENTS procedure.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('QN','admin_emp',DBMS_REDEFINITION.CONS_USE_PK);
END;
2.Create an interim table hr.int_admin_emp
create tablespace admin_tbs datafile size 200m ;
create tablespace admin_tb2 datafile size 200m ;
CREATE TABLE int_admin_emp
( empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
deptno NUMBER(3) NOT NULL,
bonus NUMBER (7,2) DEFAULT(1000))
PARTITION BY RANGE(empno)
(PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tb2);
3.Start the redefinition process
/* 对应字段才能同步数据，否则只能同步部分数据 */
/* dbms_redefinition.cons_use_pk 与 CONS_USE_ROWID
/* 自定义方法:cons_use_pk 根据primary key
CONS_USE_ROWID 根据rowid
*/
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('qn', 'admin_emp','int_admin_emp','empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',dbms_redefinition.cons_use_pk);
END;
4.Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on hr.int_admin_emp.)
/* 自动制复制triggers, indexes, materialized view logs, grants, , constraints */
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('QN', 'admin_emp','int_admin_emp',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/
Note that the ignore_errors argument is set to TRUE for this call. The reason is that the interim table was created with a primary key constraint,
and when COPY_TABLE_DEPENDENTS attempts to copy the primary key constraint and index from the original table, errors occurs. You can ignore these errors,
but you must run the query shown in the next step to see if there are other errors.
5.Query the DBA_REDEFINITION_ERRORS view to check for errors.
/* 要查询报错 ，不并是所有报错都是需要执行的 */
sys@RAC> select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
OBJECT_NAME BASE_TABLE_NAME DDL_TXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS_C0011092 ADMIN_EMP CREATE UNIQUE INDEX "QN"."TMP$$_SYS_C00110920" ON "QN"."INT_ADMIN_EMP" ("EMPNO")
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"
SYS_C0011090 ADMIN_EMP ALTER TABLE "QN"."INT_ADMIN_EMP" MODIFY ("ENAME" CONSTRAINT "TMP$$_SYS_C00110900" NOT NULL ENABLE NOVALIDATE)
SYS_C0011091 ADMIN_EMP ALTER TABLE "QN"."INT_ADMIN_EMP" MODIFY ("DEPTNO" CONSTRAINT "TMP$$_SYS_C00110910" NOT NULL ENABLE NOVALIDATE)
SYS_C0011092 ADMIN_EMP ALTER TABLE "QN"."INT_ADMIN_EMP" ADD CONSTRAINT "TMP$$_SYS_C00110920" 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 NOVALIDATE
6.Optionally, synchronize the interim table hr.int_admin_emp.
/* 这步会将数据从admin_emp刷新到中间表int_admin_emp，没有改变表名 */
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('QN', 'admin_emp', 'int_admin_emp');
END;
7.Complete the redefinition
/* 这步会再次装数据从admin_emp刷新到中间表int_admin_emp，出现exclusive mode模式,int_admin_emp 改为admin_emp,admin_emp改为int_admin_emp */
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('qn', 'admin_emp', 'int_admin_emp');
END;
8. Wait for any long-running queries against the interim table to complete, and then
drop the interim table.
select dbms_metadata.get_ddl('TABLE','ADMIN_EMP') from dual;
example2 .
/* 自定义转换更改字段为对像类型的表结构 object type */
This example redefines a table to change columns into object attributes. The redefined table gets a new column that is an object type.
create table CUSTOMER(
CID NUMBER PRIMARY KEY
,NAME VARCHAR2(30)
,STREET VARCHAR2(100)
,CITY VARCHAR2(30)
,STATE VARCHAR2(2)
,ZIP NUMBER(5) )
CREATE TYPE ADD_T AS OBJECT (
STREET VARCHAR2(100),
CITY VARCHAR2(30),
STATE VARCHAR2(2),
ZIP NUMBER(5,0) )
1. Verify that the table is a candidate for online redefinition. Specify that the redefinition is to be done using primary keys or pseudo-primary keys.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('QN','CUSTOMER',DBMS_REDEFINITION.CONS_USE_PK);
END;
2. Create the interim table int_customer.
CREATE TABLE INT_CUSTOMER(
CID NUMBER,
NAME VARCHAR2(30),
ADDR ADD_T);
Note that no primary key is defined on the interim table. When dependent objects are copied in step 5, the primary key constraint and index are copied.
3.Because CUSTOMER is a very large table, specify parallel operations for the next step.
alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;
4.Start the redefinition process using primary keys.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'QN',
orig_table => 'CUSTOMER',
int_table => 'INT_CUSTOMER',
col_mapping => 'cid cid, name name,ADD_T(street, city, state, zip) addr');
END;
Note that addr_t(street, city, state, zip) is a call to the object constructor
5. Copy dependent objects
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( 'QN','CUSTOMER','INT_CUSTOMER',DBMS_REDEFINITION.CONS_ORIG_PARAMS,TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
END;
Note that for this call, the final argument indicates that table statistics are to be copied to the interim table.
6.Optionally synchronize the interim table.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('QN', 'CUSTOMER', 'INT_CUSTOMER');
END;
7.7. Complete the redefinition.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('QN', 'CUSTOMER', 'INT_CUSTOMER');
END;
qn@RAC> select a.addr.state from CUSTOMER a ;
AD
--
Y
Example 3
/* 在线自定义转换时，中间表字段与原表名不一样，且有指定字段的约束 ,需要REGISTER_DEPENDENT_OBJECT来注册 */
This example addresses the situation where a dependent object must be manually created and registered.
1. Create the interim table INT_T1 and create an index Int_Index1 on column C2.
create table INT_T1 (
c2 number
,name varchar2(20) ) ;
create index Int_Index1 on int_t1(c2) ;
create table T1 (
c1 number
,name varchar2(20) ) ;
create index Index1 on t1(c1) ;
2. Ensure that T1 is a candidate for online redefinition with CAN_REDEF_TABLE, and then begin the redefinition process with START_REDEF_TABLE.
create table T1 (
c1 number
,name varchar2(20) ) ;
create index Index1 on t1(c1) ;
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('QN','T1',DBMS_REDEFINITION.CONS_USE_ROWID);
END;
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'QN',
orig_table => 'T1',
int_table => 'INT_T1',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
col_mapping => 'c1 c2,name name');
END;
3. Register the original (Index1) and interim (Int_Index1) dependent objects.
BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
uname => 'QN',
orig_table => 'T1',
int_table => 'INT_T1',
dep_type => DBMS_REDEFINITION.CONS_INDEX,
dep_owner => 'QN',
dep_orig_name => 'Index1',
dep_int_name => 'Int_Index1');
END;
4. Use COPY_TABLE_DEPENDENTS to copy the remaining dependent objects.
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( 'QN','T1','INT_T1',DBMS_REDEFINITION.CONS_ORIG_PARAMS,TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
END;
select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
5. Optionally synchronize the interim table.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('QN', 'T1','INT_T1');
END;
6. Complete the redefinition and drop the interim table
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('QN', 'T1', 'INT_T1');
END;
BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE('QN', 'T1', 'INT_T1');
END;
Example 4
/* 分区表的分区转换到其它的表空间 */
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:
create tablespace tbs_low_freq datafile size 200m ;
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')));
CREATE INDEX sales_index ON salestable (s_saledate, s_productid, s_custid) LOCAL;
1. Ensure that salestable is a candidate for redefinition.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname => 'QN',
tname => 'SALESTABLE',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
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.
CREATE TABLE int_salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE tbs_low_freq;
3. Start the redefinition process using rowid.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'QN',
orig_table => 'salestable',
int_table => 'int_salestable',
col_mapping => NULL,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
4. Manually create any local indexes on the interim table.
CREATE INDEX int_sales_index ON int_salestable(s_saledate, s_productid, s_custid) TABLESPACE tbs_low_freq;
5. Optionally synchronize the interim table.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'QN',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
6. Complete the redefinition.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'QN',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE('QN', 'salestable', 'int_salestable');
END;
0 0
- DBMS_REDEFINITION用法
- dbms_redefinition
- DBMS_REDEFINITION子程序概要
- 在线重定义表(dbms_redefinition)
- DBMS_REDEFINITION在线重定义表
- 分区：在线重定义：DBMS_REDEFINITION
- DBMS_REDEFINITION在线重定义表
- DBMS_REDEFINITION在线重定义表
- How To Partition Existing Table Using DBMS_Redefinition
- 使用Dbms_Redefinition转换普通表-》分区表
- 使用DBMS_REDEFINITION包在线重定义分区表
- 使用DBMS_REDEFINITION在线重定义分区表
- 基于 dbms_redefinition 在线重定义表
- Partition-方案四：通过 DBMS_REDEFINITION 方法
- How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]
- How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]
- How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]
- 使用DBMS_REDEFINITION在线切换普通表到分区表
- iOS项目添加VPN
- JNI完全指南(二)——类与异常
- spacemacs c++ 智能提示
- Ubuntu15.04源码安装Node.js
- php去除空数组且数组键名重置
- DBMS_REDEFINITION用法
- mysql安装与配置
- rabbitmq http.api
- 精心整理python自动化测试小技巧二
- mybatis中常用的标签
- python读取文件并上传数据库
- 洛谷 P1629 邮递员送信
- log4j配置
- HDU 3622 Bomb Game(2-SAT)
