Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

Online Table Redefinition Enhancements in Oracle Database 11g Release 1 - Take advantage of the reduced invalidations associated with online table redefintions in 11g.



Romeo Vasileniuc said...

I'm not sure if it is a bug or feature but it seems that you are allowed to redefine a table with the flashback archive attribute.

After the redefinition the old flashback table is preserved and stored under the redefined name.

Before:
SYS@ocp:1>select table_name, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME from dba_flashback_archive_tables;

TABLE_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME
-------------------- ----------------------- -----------------------------------------------------
MY_TEST MY_FBDA SYS_FBA_HIST_79543

SYS@ocp:1>

After:
SYS@ocp:1>select table_name, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME from dba_flashback_archive_tables;

TABLE_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME
-------------------- ----------------------- -----------------------------------------------------
MY_TEST_REDEF MY_FBDA SYS_FBA_HIST_79543

SYS@ocp:1>


Gary S said...

COPY_TABLE_DEPENDENTS Procedure
This procedure clones the dependent objects of the table being redefined onto the
interim table and registers the dependent objects. This procedure does not clone the
already registered dependent objects.
This subprogram is used to clone the dependent objects like grants, triggers,
constraints and privileges from the table being redefined to the interim table (which
represents the post-redefinition table).
Syntax
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);

Tim... said...

Hi.

COPY_TABLE_DEPENDENTS is not an enhancement in 11gR1. It was present in 10gR2.

The point of the article is to discuss how the new dependancy management affects the redefinition. Recreating the trigger manually or using the COPY_TABLE_DEPENDENTS procedure has nothing to do with this.

Cheers

Tim...
Prepared Statement Error: Table './oraclebasecms/cms_page_comment_uuids' is marked as crashed and should be repaired