个人总结
这个文档写的不是很详细吼.只给出了重定义的大致操作过程.没有详细的解释.| HOW TO ADD (OR DROP) A COLUMN USING DBMS_REDEFINITION [ID 1120704.1] | |||||
| Modified 14-OCT-2010 Type HOWTO Status PUBLISHED | |||||
In this Document
Goal
Solution
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.1 - Release: 10.1 to 11.2Information in this document applies to any platform.
Goal
This note will discuss and demonstrate the use of dbms_redefinition to add / drop columns in a tableSolution
1) Determine if the table can be redefined by using : DBMS_REDEFINITION.CAN_REDEF_TABLE
2) Create an interim table with the new desired structure
* adding any new columns if desired
* removing any undesired columns
3) Start the redefinition using : DBMS_REDEFINITION.START_REDEF_TABLE
4) If table dependents exist (indexes, triggers, constraints) copy the table dependents using : DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
5) finish the redefinition using : DBMS_REDEFINITION.FINISH_REDEF_TABLE
6) drop the interim table (the old / original table that needed colunms added)
CASE STUDY #1 (Add a 4th column of type CLOB between the 2nd and 3rd columns)
create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;
-- CREATE THE ORIGINAL TABLE
CREATE TABLE TEST (
COL1 NUMBER,
COL2 VARCHAR2(20),
COL4 DATE);
-- CREATE OUR INTERIM TABLE AND INCLUDE OUR NEW COLUMN
CREATE TABLE TEST_INTERIM (
COL1 NUMBER,
COL2 VARCHAR2(20),
COL3 CLOB,
COL4 DATE);
-- DETERMINE IF THE ORIGINAL TABLE CAN BE REDEFINED ONLINE
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','TEST', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
-- BEGIN THE REDEFINITION
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'TEST',
orig_table => 'TEST',
int_table => 'TEST_INTERIM',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
-- IF THE TABLE HAD HAD DEPENDENCIES (INDEXES ... CONSTRAINTS ... TRIGGERS)
-- THIS WOULD BE THE POINT AT WHICH THEY WOULD HAVE BEEN COPIED
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('TEST',
'TEST',
'TEST_INTERIM',
dbms_redefinition.cons_orig_params ,
TRUE,
TRUE,
TRUE,
FALSE,
error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
-- FINISH THE REDEFINITION
exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST','TEST','TEST_INTERIM');
-- EXAMINE THE RESULTS
DESC TEST
-- Name Null? Type
-- ----------------------------------------- -------- ----------------------------
-- COL1 NUMBER
-- COL2 VARCHAR2(20)
-- COL3 CLOB
-- COL4 DATE
DESC TEST_INTERIM
-- Name Null? Type
-- ----------------------------------------- -------- ----------------------------
-- COL1 NUMBER
-- COL2 VARCHAR2(20)
-- COL4 DATE
-- DROP THE INTERIM TABLE
DROP TABLE TEST_INTERIM;
CASE STUDY #2 (Remove the 3rd column)
create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;
-- CREATE THE ORIGINAL TABLE
CREATE TABLE TEST (
COL1 NUMBER,
COL2 VARCHAR2(20),
COL3 CLOB,
COL4 DATE);
-- CREATE OUR INTERIM TABLE AND INCLUD OUR NEW COLUMN
CREATE TABLE TEST_INTERIM (
COL1 NUMBER,
COL2 VARCHAR2(20),
COL4 DATE);
-- DETERMINE IF THE ORIGINAL TABLE CAN BE REDEFINED ONLINE
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','TEST', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
-- BEGIN THE REDEFINITION
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'TEST',
orig_table => 'TEST',
int_table => 'TEST_INTERIM',
col_mapping => 'COL1 COL1, COL2 COL2, COL4 COL4',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
-- IF THE TABLE HAD HAD DEPENDENCIES (INDEXES ... CONSTRAINTS ... TRIGGERS)
-- THIS WOULD BE THE POINT AT WHICH THEY WOULD HAVE BEEN COPIED
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('TEST',
'TEST',
'TEST_INTERIM',
dbms_redefinition.cons_orig_params ,
TRUE,
TRUE,
TRUE,
FALSE,
error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
-- FINISH THE REDEFINITION
exec dbms_redefinition.finish_redef_table('TEST','TEST','TEST_INTERIM');
-- EXAMINE THE RESULTS
DESC TEST
-- Name Null? Type
-- ----------------------------------------- -------- ----------------------------
-- COL1 NUMBER
-- COL2 VARCHAR2(20)
-- COL4 DATE
DESC TEST_INTERIM
-- Name Null? Type
-- ----------------------------------------- -------- ----------------------------
-- COL1 NUMBER
-- COL2 VARCHAR2(20)
-- COL3 CLOB
-- COL4 DATE
-- DROP THE INTERIM TABLE
DROP TABLE TEST_INTRIM;