2012-11-3 - 11-4 11gR2 "ADMINISTRATOR'S GUIDE" page 585 - 646

 

 

Methods for Loading Tables -- 加载导入表的方法

 

Removing Unused Columns -- 移除表的字段
In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is
specified. This clause causes a checkpoint to be applied after processing the specified
number of rows, in this case 250. Checkpointing cuts down on the amount of undo
logs accumulated during the drop column operation to avoid a potential exhaustion of
undo space.
ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;
有一个CHECKPOINT的选项，250行就往下写，避免UNDO空间的不足。

Performing Online Redefinition with DBMS_REDEFINITION -- 在线重定义
Online Table Redefinition Examples
一般的步骤如下：

The steps in this redefinition are illustrated below.
1. Verify that the table is a candidate for online redefinition. In this case you specify
that the redefinition is to be done using primary keys or pseudo-primary keys. -- 看是否适合做在线重定义
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. Copy dependent objects. (Automatically create any triggers, indexes, materialized
view logs, grants, and constraints on hr.int_admin_emp.) -- 建立相关的依赖对象，如触发器，索引等
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr', 'admin_emp','int_admin_emp',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

Note that the ignore_errors argument is set to TRUE for this call. The reason is
that the interim table was created with a primary key constraint, and when COPY_
TABLE_DEPENDENTS attempts to copy the primary key constraint and index from
the original table, errors occurs. You can ignore these errors, but you must run the
query shown in the next step to see if there are other errors.
注意这个IGNORE_ERRORS的参数。

 

5. Query the DBA_REDEFINITION_ERRORS view to check for errors. -- 查看COPY的过程中是否有ERRORS。
SQL> select object_name, base_table_name, ddl_txt from
DBA_REDEFINITION_ERRORS;

 

6. Optionally, synchronize the interim table hr.int_admin_emp. -- 可选项，先同步下，免得最后一步FINISH挂住很久
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/

 

7. Complete the redefinition. -- FINISH
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.

8. Wait for any long-running queries against the interim table to complete, and then
drop the interim table.

Privileges Required for the DBMS_REDEFINITION Package
Execute privileges on the DBMS_REDEFINITION package are 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
The following additional privileges are required to execute COPY_TABLE_
DEPENDENTS:
 -- CREATE ANY TRIGGER
 -- CREATE ANY INDEX