Resumable Import Sessions
Karthikeyan Subramaniam, www.hpglobalsoft.com

Overview  

It is very tough for a DBA to determine the size of the data that would get populated into each tablespace of the database when he/she imports the data from the dump file(s) given by another DBA. While importing from export dump files, DBAs used to be unsure if the import would succeed without any “lack of space issue” in the respective tablespaces, prior to the release of oracle 9i. To get rid of this issue, Oracle has introduced a method that enables the Import session to withstand for a stipulated time without terminating the session as such. This way, the DBA can skip the space related problems during the Import session.

New Import parameters in Oracle 9i 

In oracle 9i, three parameters have been introduced for import Utility.  These are RESUMABLE, RESUMABLE_NAME and RESUMABLE_TIMEOUT. 

How Resumable Import works 

The RESUMABLE parameter sets the import session to suspend the import temporarily until the time set for RESUMABLE_TIMEOUT is expired.  This occurs whenever the import session encounters a “lack of space issue” while writing to the tablespace (conceptually, while allocating extents for existing objects or segments for new objects in the tablespace). By default, the RESUMABLE parameter is not set for any import session. It needs to be explicitly set to ‘Y’, if the DBA prefers this method. The amount of time to suspend the operation is determined by the RESUMABLE_TIMEOUT parameter that is mentioned in seconds. The question may arise now, How does a DBA know when the import session encounters the “lack of space issue” in any tablespace? The “Text string” assigned to the parameter RESUMABLE_NAME informs the DBA of this . The assigned string will be written into the database ALERT log file residing in the “background_dump_dest” location. On seeing the text string, (by manual monitoring or automated scripts monitoring ORA errors in alert log file) in the alert log file, the DBA can increase space in the respective tablespace by increasing the datafile size or adding new datafiles.

Example

C:\>imp system/manager file=test.dmp fromuser=user1 touser=user2 ignore=y 
resumable=y resumable_name=continue resumable_timeout=18000

In this example, a DBA is using the import utility to import user1’s schema objects into users2’s schema. Now the import hangs as one of the indexes is not able to allocate extent in a tablespace. The part of the Alert log file shows this issue as given below.

…………………..
………………….
Tue Oct 12 16:41:15 2004
ORA-1654: unable to extend index USER2.TEST1_I by 8 in tablespace USER2_TABLESPACE
statement in resumable session 'continue' was suspended due to
ORA-01654: unable to extend index USER2.TEST1_I by 8 in tablespace USER2_TABLESPACE 
…………………………………..
…………………………………..

The text assigned to RESUMABLE_NAME has been written into the alert log file. Now the DBA adds a new datafile to the tablespace USER2_TABLESPACE and the import session resumes automatically and completes the import session successfully.  Part of the alert log file is given below. The DBA can increase the size of the existing datafiles of the tablespace USER2_TABLESPACE instead of adding new files.

SQL> alter tablespace USER2_TABLESPACE add datafile 'C:\Test\USER2_TABLESPACE_01.DBF' size 10M;

Tablespace altered.

After increasing the size of the tablespace by adding a new datafile, the import operation continues and writes to the Alert log file as given below.

………………….
…………………
Tue Oct 12 16:47:19 2004
alter tablespace USER2_TABLESPACE add datafile 'C:\Test\.........
Tue Oct 12 16:47:19 2004
Completed: alter tablespace USER2_TABLESPACE add datafile 'C:\Test….
Tue Oct 12 16:47:20 2004
statement in resumable session 'continue' was resumed
…………………..
…………………