Tuesday, November 01, 2011

Is SQL*Plus Resumable?

I am using resumable space for a lot of my operations. However, it seems as though the SQL*Plus copy command doesn't respect the resumable feature?

Note, I am issuing an "alter session enable resumable timeout 14400" through a login trigger. I know this works because if I do the same type of operation through an INSERT/SELECT through a dblink, my session waits for more space to be added.
user@whouse1.us> select sysdate from dual;

SYSDATE
-------------------
11/01/2011 15:48:45

Elapsed: 00:00:00.03
user@whouse1.us> COPY FROM user1/pw@proddb.us TO user/password@whouse1.us 
append dest_table (f1, f2, f3) using select f1, f2, f3 from source_table;

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 1000 array binds. (copycommit is 1000)
Maximum long size is 80. (long is 80)

ERROR:
ORA-01653: unable to extend table USER.DEST_TABLE by 4096 in tablespace JH_TEST

user@whouse1.us> select sysdate from dual;

SYSDATE
-------------------
11/01/2011 15:49:01

Elapsed: 00:00:00.03
Is RESUMABLE space not available with SQL*Plus copy?