DBMS_REDEFINITION Failed Now What?

I was cruising along with the dbms_redefinition of a very large table in our QA environment.  The goal was changing the table to a partition table.  Then the unthinkable happened I received a space error.

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TEST.TABLE1 partition P201212
by 8192 in tablespace USERS
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 52
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 1646
ORA-06512: at line 1

This particular database is on a shared server with several QA databases and we do just squeak by with space.  Unfortunately it’s not on a SAN so I can’t just have space allocated.

In order to recover from this error I must run the ABORT_REDEF_TABLE procedure.

begin dbms_redefinition.abort_redef_table(‘TEST’,’TABLE1′,’TABLE1_INTERIM’);

With the parameters being schema, original table name and interim table name.

This procedure drops all logs and tables that were created during this process, but not the interim table.  If you have decided to not perform this action at all you will need to drop that table separately.

Once this procedure is complete you can continue start the redefinition process over with the START_REDEF_TABLE procedure.

Advertisements

Tagged: ,

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: