在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/test

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
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             VALID

6 rows selected.

Now we perform. an online table redefinition.

CONN / 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;

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/test

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
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             VALID
6 rows selected.
Now we perform. an online table redefinition.
CONN / 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;
 
Finally, we re-check the status of the schema objects. 
检查对象的状态
 
CONN test/test

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
视图和存储过程都是正确的

11g 在线表定义的增强