Database Administration (MOSC)

MOSC Banner

Errors while DBMS_REDEFINITION

edited Apr 1, 2016 3:56AM in Database Administration (MOSC) 20 commentsAnswered

Hello!!

I'm trying to compress some tables online using DBMS_REDEFINITION.

These are the steps I perform:

1. Verify

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('OMI','HISA_IDDMME',DBMS_REDEFINITION.CONS_USE_PK);

2. Create the interim table

CREATE TABLE ....TABLESPACE TB1 COMPRESS;

3. Execute the redefinition

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('OMI', 'HISA_IDDMME','HISA_IDDMME_INT','COLUMNS.........)

4. Copy dependent objects

DECLARE

num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('OMI', 'HISA_IDDMME','HISA_IDDMME_INT', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);

END;

5. Check errors

select object_name, base_table_name, ddl_txt from  DBA_REDEFINITION_ERRORS;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center