IMP with RESUMABLE option

There are cases when we would need to use the RESUMABLE option with IMPORT.

Example: One such case I faced recently where my datafiles were not in the AUTOEXTEND ON mode, because of obvious reasons – so as to give more control to the database admins for controlling the usage of space. However, this rather creates many issues, especially with long running queries using more and more TEMP tablespace, and also with large IMPorts of data into the schema’s. In my case, it was the later, wherein I was importing 1.5G of data into one table in 11.2.0.1 database.

The purpose of this post is to highlight the parameter in Oracle database which helps us control and make the most effective use of the RESUMABLE parameter in IMP.

The command I used in my case was something like: IMP file=*** log=*** tables=*** RESUMABLE=y

Question: How much time would the IMP process wait if I get any space related issues?

Answer: This is determined by the parameter “resumable_timeout” in Oracle. By default it is set to 0.

SQL> show parameter resumable

NAME                                 TYPE        VALUE
———————————— ———– ——————————
resumable_timeout                    integer     0

So, for my case, and since I am using SPFILE for this database, I set it to 1 hour, I.e., 3600, using the below steps:

SQL> alter system set resumable_timeout = 3600 scope=both;

System altered.

SQL>
SQL> show parameter resumable

NAME                                 TYPE        VALUE
———————————— ———– ——————————
resumable_timeout                    integer     3600

What this does is –> It tells Oracle, and in turn the IMP executable, to give me one hour to sort out the space related issues which are faced. When I face any such issues with space in datafiles during IMP process, I would, hence, be getting at least and at max one hour to add more datafiles, or to resize the datafile where the table resides, etc.

Hope this helps.

Advertisements
This entry was posted in database, Oracle, Oracle DBA. Bookmark the permalink.

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