Database Utilities (MOSC)

MOSC Banner

DBMS_REDEFINITION.REDEF_TABLE dies; need to know interim table name to abort

edited Jan 23, 2019 10:49AM in Database Utilities (MOSC) 3 commentsAnswered ✓

I have a bunch of partitioned tables with LOB columns to move from one tablespace to another, so DBMS_REDEFINITION.REDEF_TABLE seemed like the perfect tool to move all the LOB objects and index partitions and table partitions.  All went well with the first invocation:

SYS@test01> exec dbms_redefinition.redef_table('TECHTEST', 'RAW_BLUEFISH', lob_tablespace=>'FACT_DATA32B')PL/SQL procedure successfully completed.

Then I attempted to use the lob_compression_type=> parameter to set the  compression level to MEDIUM -- unfortunately the parameter value should have been COMPRESS MEDIUM:

BEGIN dbms_redefinition.redef_table('TECHTEST', 'RAW_MANUAL_PAYROLL', lob_tablespace=>'FACT_DATA32B', lob_compression_type=>'MEDIUM'); END;*ERROR at line 1:ORA-23540: Redefinition not defined or initiatedORA-06512: at "SYS.DBMS_REDEFINITION", line 5076ORA-39096: invalid input value 'MEDIUM' for parameter lob_compression_typeORA-06512: at "SYS.DBMS_SYS_ERROR", line 95ORA-06512: at "SYS.DBMS_REDEFINITION", line 3241ORA-06512: at "SYS.DBMS_REDEFINITION", line 3478ORA-06512: at "SYS.DBMS_REDEFINITION", line 4143ORA-06512: at "SYS.DBMS_REDEFINITION", line 5168ORA-06512: at line 1

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