Redefining tables online

redefining tables online ,9i里才有，试了一下，感觉跟create mv on prebiuld table 差不多，只是这些动作ORACLE用DBMS_REDEFINITION 这个包做了这些步骤，而不要DBA手动去更改TABLE.现将ORACLE ONLINE DOC中的内容摘录下来。只是有一点不明，有两种方法来重定义表，PRIMARY KEY ROWID , 根据ROWID没有测试成功，在网上搜的例子也都是以PRIMARY KEY 来演示。

Features of Online Table Redefinition

Online table redefinition enables you to:

• Modify the storage parameters of the table
• Move the table to a different tablespace in the same schema
• Add support for parallel queries
• Add or drop partitioning support
• Re-create the table to reduce fragmentation
• Change the organization of a normal table (heap organized) to an index-organized table and vice versa
• Add or drop a column

The DBMS_REDEFINITION Package

The mechanism for performing online redefinition is the PL/SQL package DBMS_REDEFINITION. Execute privileges on this package is granted to EXECUTE_CATALOG_ROLE. In addition to having execute privileges on this package, you must be granted the following privileges:

• CREATE ANY TABLE
• ALTER ANY TABLE
• DROP ANY TABLE
• LOCK ANY TABLE
• SELECT ANY TABLE

Example of Online Table Redefinition

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 is redefined as follows:

• New columns mgr, hiredate, sal, and bonus (these existed in the original table but were dropped in previous examples) are added.
• The new column bonus is initialized to 0
• The column deptno has it's value increased by 10.
• The redefined table is partitioned by range on empno.

The steps in this redefinition are illustrated below.

1. Verify that the table is a candidate for online redefinition.

   BEGIN
   DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','admin_emp',
           dbms_redefinition.cons_use_pk);
   END;
   /
   
   

2. Create an interim table hr.int_admin_emp.

   CREATE TABLE hr.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_tbs2);
   
   

3. Start the redefinition process.

   BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'admin_emp','int_admin_emp',
          'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',
           dbms_redefinition.cons_use_pk);
   END;
   /
   
   

4. Create any triggers, indexes and constraints on hr.int_admin_emp. During the final step of redefinition, these are transferred back to the original table. Any referential constraints involved on hr.int_admin_emp should be disabled. You can define any grants associated with the interim table. These replace the grants on the original table after the redefinition.

   ALTER TABLE hr.int_admin_emp ADD CONSTRAINT admin_dept_fkey2
        FOREIGN KEY (deptno) REFERENCES hr.departments (department_id);
   ALTER TABLE hr.int_admin_emp MODIFY CONSTRAINT admin_dept_fkey2
        DISABLE KEEP INDEX;
   
   

   The disabled constraint, admin_dept_fkey2, will be enabled automatically as part of the finish redefinition process and will then involve the newly redefined admin_emp table.

5. Optionally, synchronize the interim table hr.int_admin_emp.

   BEGIN 
   DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'admin_emp', 'int_admin_emp');
   END;
   /
   
   

6. Complete the redefinition.

   BEGIN
   DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr', 'admin_emp', 'int_admin_emp');
   END;
   /
   
   

   The table hr.admin_emp is locked in the exclusive mode only for a small window toward the end of this step. After this call the table hr.admin_emp is redefined such that it has all the attributes of the hr.int_admin_emp table.

7. Drop the interim table.

Restrictions

The following restrictions apply to the online redefinition of tables:

• If the table is to be redefined using primary keys, then the table to be redefined and the post-redefinition table must have the same primary key columns. If the table is to be redefined using ROWIDs, then the table must not be an index-organized table.
• Tables that have materialized views and materialized view logs defined on them cannot be online redefined.
• Tables that are materialized view container tables and Advanced Queuing tables cannot be online redefined.
• The overflow table of an index-organized table cannot be online redefined.
• Tables with user-defined types (objects, REFs, collections, typed tables) cannot be online redefined.
• Tables with BFILE columns cannot be online redefined.
• Tables with LONG columns cannot be online redefined. Tables with LOB columns are acceptable.
• The table to be redefined cannot be part of a cluster.
• Tables in the SYS and SYSTEM schema cannot be online redefined.
• Temporary tables cannot be redefined.
• There is no horizontal subsetting support.
• Only simple deterministic expressions can be used when mapping the columns in the interim table to those of the original table. For example, subqueries are not allowed.
• If new columns (which are not instantiated with existing data for the original table) are being added as part of the redefinition, then they must not be declared NOT NULL until the redefinition is complete.
• There cannot be any referential constraints between the table being redefined and the interim table.
• Table redefinition cannot be done NOLOGGING.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/308563/viewspace-171966/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/308563/viewspace-171966/