Resumable Operations

Oracle provides a method to allow suspended transactions that fail due to lack of space, such as a large sort, lack of space in the temporary, etc. We can keep them suspended for a period of  given time without missing it.

 

Enable resumable operations;

ALTER SESSION ENABLE RESUMABLE

or with a timeout

ALTER SESSION ENABLE RESUMABLE TIMEOUT <number_in_seconds>

 

Disable resumable operations;

ALTER SESSION DISABLE RESUMABLE

 

RESUMABLE_TIMEOUT initialization parameter must be set to a non-zero value for the session/system.

 

What Errors are Correctable?

  • ORA-01653 unable to extend table
  • ORA-01654 unable to extend index
  • ORA-01631 max # extents
  • ORA-01632 max # extents
  • ORA-01536 space quote exceeded for tablespace string

 

Operations resumables

  • DML (Including SELECT)
  • IMPORT / EXPORT
  • DDL LIKE CREATE TABLE (CTAS), CREATE INDEX, ALTER INDEX REBUILD, SOME ALTER TABLE

 

HTH – Antonio NAVARRO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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