oracle定义表注意事项,【TABLE】oracle表在线重定义注意事项

最新推荐文章于 2024-01-25 08:15:56 发布

龙憩

最新推荐文章于 2024-01-25 08:15:56 发布

阅读量170

点赞数

文章标签： oracle定义表注意事项

下面是无法使用在线重定义的一些情况：

点击(此处)折叠或打开

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 --注意LOB相关表,LONG类型必须转换为CLOBS. LONG RAW=>BLOBS

converted to CLOBS.Also,LONG RAW columns must be converted to BLOBS.

Tables with LOB columns are acceptable.

-Tablesinthe SYSandSYSTEM 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

- Create any index

- Create any trigger

下面是相关包的说明

点击(此处)折叠或打开

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);