Online Table Redefinition Enhancements in Oracle Database 11g Release 1
2011-06-09 13:00 Tracy. 阅读(409) 评论(0) 编辑 收藏 举报Online Table Redefinition Enhancements in Oracle Database 11g Release 1
By default, online table redefinitions no longer invalidate dependent objects (PL/SQL, views, synonyms etc.), provided the redefinition does not logically affect them. An exception to this behavior is triggers, which are associated directly with a table.To see this new behavior, first me must create a table with some dependent objects.
If we check the status of the schema objects we can see that all of them are valid.CONN test/test@db11g CREATE TABLE redef_tab ( id NUMBER, description VARCHAR2(50), CONSTRAINT redef_tab_pk PRIMARY KEY (id) ); CREATE VIEW redef_tab_v AS SELECT * FROM redef_tab; CREATE SEQUENCE redef_tab_seq; CREATE OR REPLACE PROCEDURE get_description ( p_id IN redef_tab.id%TYPE, p_description OUT redef_tab.description%TYPE) AS BEGIN SELECT description INTO p_description FROM redef_tab WHERE id = p_id; END; / CREATE OR REPLACE TRIGGER redef_tab_bir BEFORE INSERT ON redef_tab FOR EACH ROW WHEN (new.id IS NULL) BEGIN :new.id := redef_tab_seq.NEXTVAL; END; /
Now we perform an online table redefinition.COLUMN object_name FORMAT A20 SELECT object_name, object_type, status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- REDEF_TAB TABLE VALID REDEF_TAB_SEQ SEQUENCE VALID GET_DESCRIPTION PROCEDURE VALID REDEF_TAB_PK INDEX VALID REDEF_TAB_BIR TRIGGER VALID REDEF_TAB_V VIEW VALID 6 rows selected. SQL>
Finally, we re-check the status of the schema objects.CONN sys/password@db11g AS SYSDBA -- Check table can be redefined EXEC DBMS_REDEFINITION.can_redef_table('TEST', 'REDEF_TAB'); -- Create new table CREATE TABLE test.redef_tab2 AS SELECT * FROM test.redef_tab WHERE 1=2; -- Start Redefinition EXEC DBMS_REDEFINITION.start_redef_table('TEST', 'REDEF_TAB', 'REDEF_TAB2'); -- Optionally synchronize new table with interim data before index creation EXEC DBMS_REDEFINITION.sync_interim_table('TEST', 'REDEF_TAB', 'REDEF_TAB2'); -- Add new PK. ALTER TABLE test.redef_tab2 ADD (CONSTRAINT redef_tab2_pk PRIMARY KEY (id)); -- Complete redefinition EXEC DBMS_REDEFINITION.finish_redef_table('TEST', 'REDEF_TAB', 'REDEF_TAB2'); -- Remove original table which now has the name of the new table DROP TABLE test.redef_tab2; -- Rename the primary key constraint. ALTER TABLE test.redef_tab RENAME CONSTRAINT redef_tab2_pk TO redef_tab_pk;
Notice that theCONN test/test@db11g COLUMN object_name FORMAT A20 SELECT object_name, object_type, status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- REDEF_TAB_SEQ SEQUENCE VALID GET_DESCRIPTION PROCEDURE VALID REDEF_TAB_V VIEW VALID REDEF_TAB2_PK INDEX VALID REDEF_TAB TABLE VALID 5 rows selected. SQL>
GET_DESCRIPTION
procedure and REDEF_TAB_V
view are still valid, but the REDEF_TAB_BIR
trigger is gone. The trigger was still associated with the original table, renamed to REDEF_TAB2
, so when the original table was dropped, the trigger was dropped with it.
In addition to the dependency changes, the
COPY_TABLE_DEPENDENTS
procedure can now also copy statistics and materialized view logs.
本文来自博客园,作者:Tracy.,转载请注明原文链接:https://www.cnblogs.com/tracy/archive/2011/06/09/2076270.html