rebuild table online

原创 2013年12月23日 22:46:10
The main steps of table redefinition are:
1, Verify the source table is able to be online redefine:
BEGIN Dbms_Redefinition.Can_Redef_Table('USER','SOURCE TABLE NAME'); END;
2, Create your empty interim table on new tablespace with the same structure as the source table:
3, Start the redefinition of the source table:
BEGIN Dbms_Redefinition.start_redef_table('USER','SOURCE TABLE NAME','INTERIM TABLE NAME‘); END;
4, Create the constraints, indexes and triggers on the interim table as they are defined on the source table.
5, Sync interim table with source table data:
BEGIN Dbms_Redefinition.SYNC_INTERIM_TABLE('USER','SOURCE TABLE NAME','INTERIM TABLE NAME‘); END;
6, Finish the redefinition of the source table:
BEGIN Dbms_Redefinition.FINISH_REDEF_TABLE('USER','SOURCE TABLE NAME','INTERIM TABLE NAME‘); END;


附:DBMS_REDEFINITION ONLINE REORGANIZATION OF TABLES
PURPOSE
-------

To provide information on the DBMS_REDEFINITION package.
 
SCOPE & APPLICATION
-------------------

Skill Level - Advanced

-----------------------------
 
DBMS_REDEFINITION AND ONLINE REORGANIZATION OF TABLES


DBMS_REDEFINITION
==================

With DBMS_REDEFINITION, you can perform an online reorganization of tables. To
achieve this online reorganization, incrementally maintainable local
materialized views are used. Snapshot logs need to be defined on the master
tables to support incrementally maintainable materialized views. These logs
keep track of the changes to the master tables and are used by the materialized
views during refresh synchronization. To keep table indexes and privileges you must use the
copy_table_dependents procedure.


Restrictions
=============

Tables with the following characteristics cannot be redefined online:
  - [9.0.1]Tables with no primary keys
  - Tables that have materialized view logs defined on them
  - [9i] Tables that are materialized view container tables and AQ tables
  - [10g] Tables that are replicated in an n-way master configuration can
    be redefined, but horizontal subsetting (subset of rows in the table),
    vertical subsetting (subset of columns in the table), and column
    transformations are not allowed.
  - The overflow table of an IOT table
  - Tables with fine-grained access control (row-level security)
  - Tables with BFILE columns
  - Tables with LONG columns can be redefined online, but those columns must be
    converted to CLOBS. Also, LONG RAW columns must be converted to BLOBS.
    Tables with LOB columns are acceptable.
  - Tables in the SYS and SYSTEM schema
  - Temporary tables

 
Other restrictions:
  - A subset of rows in the table
  - Only simple deterministic expressions, sequences, and SYSDATE 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 are being added with no column mappings, 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.
  - [10g] For materialized view logs and queue tables, online redefinition is
    restricted to changes in physical properties.
  - You cannot convert a nested table to a VARRAY.


Privileges Required
====================
Following privileges are needed to run this package:  
  - Execute privilege to DBMS_REDEFINITION
  - Create any table
  - Alter any table
  - Drop any table
  - Lock any table
  - Select any table


Summary of DBMS_REDEFINITION Subprograms:
=========================================

CAN_REDEF_TABLE Procedure:
--------------------------
This procedure determines if a given table can be reorganized online. This is
the first step of the online reorganization process. If the table is not a
candidate for online redefinition, an error message is raised.

SYNTAX

DBMS_REDEFINITION.can_redef_table (
uname IN VARCHAR2,
tname IN VARCHAR2);

CAN_REDEF_TABLE Procedure Parameters:

Parameter          Description
---------          ------------
uname              The schema name of the table.
tname              The name of the table to be reorganized.



START_REDEF_TABLE Procedure:
----------------------------
This procedure initiates the reorganization process. After verifying that the
table can be reorganized online, you create an empty interim table (in the same
schema as the table to be reorganized) with the desired attributes of the
post-reorganization table.

SYNTAX

DBMS_REDEFINITION.start_redef_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
col_mapping IN VARCHAR2 := NULL);

START_REDEF_TABLE Procedure Parameters:

Parameter          Description
----------         ------------
uname              The schema name of the tables.
orig_table         The name of the table to be reorganized.
int_table          The name of the interim table.
col_mapping        The mapping information from the columns in the interim
                   table to the columns in the original table. (This is similar
                   to the column list on the SELECT clause of a query.) If NULL,
                   all the columns in the original table are selected and have
                   the same name after reorganization.

FINISH_REDEF_TABLE Procedure:
----------------------------
This procedure completes the reorganization process.  Before this step you can
create new indexes, triggers, grants, and constraints on the interim table. The
referential constraints involving the interim table must be disabled.  After
completing this step, the original table is locked briefly during this
procedure.

 
SYNTAX

DBMS_REDEFINITION.finish_redef_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2);

FINISH_REDEF_TABLE Procedure Parameters:

Parameter           Description
---------           ------------
uname               The schema name of the tables.
orig_table          The name of the table to be reorganized.
int_table           The name of the interim table.



SYNC_INTERIM_TABLE Procedure:
----------------------------
This procedure keeps the interim table synchronized with the original table.
This step is useful in minimizing the amount of synchronization needed to be
done by finish_reorg_table before completing the online reorganization. This
procedure can be called between long running operations (such as create index)
on the interim table to sync it up with the data in the original table and
speed up subsequent operations.

SYNTAX

DBMS_REDEFINITION.sync_interim_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2);

SYNC_INTERIM_TABLE Procedure Parameters:

Parameters          Description
----------          ------------
uname               The schema name of the tables.
orig_table          The name of the table to be reorganized.
int_table           The name of the interim table.


ABORT_REDEF_TABLE Procedure:
----------------------------
This procedure cleans up errors that occur during the reorganization process.
This procedure can also be used to abort the reorganization process any time
after start_reorg_table has been called and before finish_reorg_table is called.

SYNTAX

DBMS_REDEFINITION.abort_redef_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2);

ABORT_REDEF_TABLE Procedure Parameters:

Parameters          Description
----------          ------------
uname               The schema name of the table.
orig_table          The name of the table to be reorganized.
int_table           The name of the interim table.


If we have an encrypted column which is part of primary key then
follow the steps mentioned in the bug below.

Reference: Bug 6034260 DBMS_REDEFINITION.START_REDEF_TABLE FAILS ORA-32412 BECAUSE
OF ENCRYPTED COLUMN

COPY_TABLE_DEPENDENTS  (Procedure)
Copies the dependent objects of the original table to the interim table


COPY_TABLE_DEPENDENTS.dbms_redefinition.copy_table_dependents(
uname               IN  VARCHAR2,
orig_table           IN  VARCHAR2,
int_table             IN  VARCHAR2,
copy_indexes     IN  PLS_INTEGER := 1,
copy_triggers      IN  BOOLEAN := TRUE,
copy_constraints IN  BOOLEAN := TRUE,
copy_privileges   IN  BOOLEAN := TRUE,
ignore_errors      IN  BOOLEAN := FALSE,
num_errors       OUT PLS_INTEGER,
copy_statistics  IN  BOOLEAN := FALSE
copy_mvlog       IN  BOOLEAN := FALSE);

RELATED DOCUMENTS
-----------------

Oracle9i Database Administrator's Guide Part Number A90117-01
Oracle9i Supplied PL/SQL Packages and Types Reference Part Number A89852-02

转alter index rebuild online引发的血案

‘早上起来没有一个人……‘,伸手抓起手机,‘喂,……应用hang住了……rac的一节点在手工shutdown……需要到现场……’。赶紧穿上衣服,拿起电脑往客户办公室赶。路上还接到客户电话具体描述了下故...

索引create|rebuild|rebulid online

对于使用rebuild online 来说, 最大的好处即在于online. 也就是最小化对当前业务的影响.. 创建时的开销应该会大一点, 毕竟一般情况下表会比索引大, 需要排序的内存/硬盘空间自然...

alter index rebuild与alter index rebuild online的区别

一般在什么情况下重构索引 1.当索引所基于表上的DML操作频繁,随着时间推移,索引效率就越来越低,所以就需要重建索引 2.当表被移动另一个表空间时,此表所在的索引会变得无效,也需要重建索引   ...

oracle alter index rebuild online和alter index rebuild的区别

本文用10046事件来解析alter index rebuild与alter index rebuild online的区别 alter index rebuild online实质上是扫描...

office365 sharepoint2013 online 环境:使用Jquery 读取list 并把list数据快速插入HTML table(其他数据格式同理)

office365 sharepoint2013 online 环境:使用Jquery 读取list 并把list数据快速插入HTML table(其他数据格式同理) 这么早写这篇...

在线更改表字段工具比较 pt-online-schema-change VS oak-online-alter-table

在上篇文章中提到了MySQL 5.6 Online DDL,如果是MySQL 5.5的版本在DDL方面是要付出代价的,虽然已经有了Fast index Creation,但是在添加字段还是会锁表的...

Solar Board Rebuild on TI part1

  • 2013年01月05日 09:58
  • 37KB
  • 下载

rebuild.C#与.NET3.5高级程序设计.part3

  • 2011年04月21日 13:45
  • 12.45MB
  • 下载

Rebuild Instance 操作详解 - 每天5分钟玩转 OpenStack(37)

上一节我们讨论了 snapshot,snapshot 的一个重要作用是对 instance 做备份。 如果 instance 损坏了,可以通过 snapshot 恢复,这个恢复的操作就是 Reb...

Solar Board Rebuild on TI part3

  • 2013年01月05日 10:00
  • 25KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:rebuild table online
举报原因:
原因补充:

(最多只允许输入30个字)