Can we Pause a runing process urgently !

[Ezhilarasan]

Hi,

Consider the scenario I started a Import for a big table.
I estimated freespace is enough, but suddenly someone used more space, my big table import mostly done, but it will fail soon. In this case adding datafile to tablespace may take
time, so I want to Pause the process first ( or if possible database ), then I will increase the space in the tablespace
and then want to continue that process.

I am wondering, is it possible ! It will be great.
Otherwise I have to Redo the big table Import again.

Thanks
R.Ezhil

[John Palmer]

kill -STOP to suspend.
kill -CONT to restart.

Regards,
John

[Duncan Edmonstone]

I seem to remember that Oracle doesn't recover very well from send STOP signals to processes... This might be risky...

Cheers

Duncan

[Zafar A. Mohammed_1]

If you STOP & CONTINUE then it might sometime corrupt the data. Its better to estimate at your import file and then start the process.

Thanks
Zafar

[Yogeeraj_1]

hi,

i fear that this might not be possible.

However, it is always possible to modify the datafile parameter autoextend parameter on the fly!

E.g.
ALTER DATABASE DATAFILE '/d01/oradata/yddb/indx_lmt01.dbf' AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED;

This is the only rescue i fear (although i think triggers can also come to the rescue -- that would be too complicated!).

good luck.

best regards
Yogeeraj

[Indira Aramandla]

Hi,

I do not think there is a way of suspending and then continuing the oracle imp (import) process. The data imported will not be consistent and import will fail with error if tried to do so. Its like complete or nothing for preserving consistency.

In your tablespace (datafile) make sure there is enough free space (contiguous bytes)of free space for the tablespace to hold the table extents. Also coalesce the tablespace after you have truncated your table data.

Or if you have more space on your disk and you can afford to keep this table into another tablespace, owned by the same user, then create a new tablespace and create the table into this tablespace. Do not give quota on this new tablespace to other users, and this will prevent from others writing on to this tablespace. Then import your table data.

If your table to be imported is very large, then there are ways to improve the import process and enable it to import it faster.

1. To speed import, you should increase BUFFER, value and set COMMIT=N during the import.

2. Have sufficient rollback segment space

3. Import with INDEXES=N and then use the INDEXFILE option to create a script which can be used to create the indexes subsequent to the import.

Hope this helps.