Oracle 11g 数据库表分区在线表定义的增强

在11g 以前,在线表定义时 其他对象比如视图和存储过程
与这个表有关系的都会失效,变成invalid
先看10g 的
SQL>select * from v$version;
BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

CONN test/testCREATE TABLE redef_tab ( id NUMBER, description VARCHAR2(50), CONSTRAINT redef_tab_pk PRIMARY KEY (id));CREATE VIEW redef_tab_v ASSELECT * 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) ASBEGIN 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
BEGIN
select redef_tab_seq.NEXTVAL into :new.id from dual;
END;/这个我们检查对象:COLUMN object_name FORMAT A20
SQL> SELECT object_name, object_type, status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
REDEF_TAB TABLE VALID
REDEF_TAB_PK INDEX VALID
REDEF_TAB_V VIEW VALID
REDEF_TAB_SEQ SEQUENCE VALID
GET_DESCRIPTION PROCEDURE VALID
REDEF_TAB_BIR TRIGGER VALID6 rows selected.
Now we perform. an online table redefinition.CONN / AS SYSDBA-- Check table can be redefinedEXEC DBMS_REDEFINITION.can_redef_table('TEST', 'REDEF_TAB');-- Create new tableCREATE TABLE test.redef_tab2 ASSELECT * FROM test.redef_tab WHERE 1=2;-- Start RedefinitionEXEC DBMS_REDEFINITION.start_redef_table('TEST', 'REDEF_TAB', 'REDEF_TAB2');-- Optionally synchronize new table with interim data before index creationEXEC 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 redefinitionEXEC DBMS_REDEFINITION.finish_redef_table('TEST', 'REDEF_TAB', 'REDEF_TAB2');-- Remove original table which now has the name of the new tableDROP TABLE test.redef_tab2;-- Rename the primary key constraint.ALTER TABLE test.redef_tab RENAME CONSTRAINT redef_tab2_pk TO redef_tab_pk;Finally, we re-check the status of the schema objects. 检查对象的状态COLUMN object_name FORMAT A20
SQL> SELECT object_name, object_type, status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
REDEF_TAB_V VIEW INVALID
REDEF_TAB_SEQ SEQUENCE VALID
GET_DESCRIPTION PROCEDURE INVALID
REDEF_TAB2_PK INDEX VALID
REDEF_TAB TABLE VALID
可以看到 视图和存储过程都变成不正确了现在我们看11g的SQL> select * from v$version; BANNER

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
CONN test/testCREATE TABLE redef_tab ( id NUMBER, description VARCHAR2(50), CONSTRAINT redef_tab_pk PRIMARY KEY (id));CREATE VIEW redef_tab_v ASSELECT * 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) ASBEGIN 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
BEGIN
select redef_tab_seq.NEXTVAL into :new.id from dual;
END;/这个我们检查对象:COLUMN object_name FORMAT A20
SQL> SELECT object_name, object_type, status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
REDEF_TAB TABLE VALID
REDEF_TAB_PK INDEX VALID
REDEF_TAB_V VIEW VALID
REDEF_TAB_SEQ SEQUENCE VALID
GET_DESCRIPTION PROCEDURE VALID
REDEF_TAB_BIR TRIGGER VALID6 rows selected.
Now we perform. an online table redefinition.CONN / AS SYSDBA-- Check table can be redefinedEXEC DBMS_REDEFINITION.can_redef_table('TEST', 'REDEF_TAB');-- Create new tableCREATE TABLE test.redef_tab2 ASSELECT * FROM test.redef_tab WHERE 1=2;-- Start RedefinitionEXEC DBMS_REDEFINITION.start_redef_table('TEST', 'REDEF_TAB', 'REDEF_TAB2');-- Optionally synchronize new table with interim data before index creationEXEC 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 redefinitionEXEC DBMS_REDEFINITION.finish_redef_table('TEST', 'REDEF_TAB', 'REDEF_TAB2');-- Remove original table which now has the name of the new tableDROP TABLE test.redef_tab2;-- Rename the primary key constraint.ALTER TABLE test.redef_tab RENAME CONSTRAINT redef_tab2_pk TO redef_tab_pk; Finally, we re-check the status of the schema objects. 检查对象的状态 CONN test/testCOLUMN object_name FORMAT A20SELECT object_name, object_type, status FROM user_objects;OBJECT_NAME OBJECT_TYPE STATUS-------------------- ------------------- -------REDEF_TAB_SEQ SEQUENCE VALIDGET_DESCRIPTION PROCEDURE VALIDREDEF_TAB_V VIEW VALIDREDEF_TAB2_PK INDEX VALIDREDEF_TAB TABLE VALID视图和存储过程都是正确的