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?

4 comments:

Joel Garry said...

I would think not.

word: ingenti
word: berseadl

Jeff Hunter said...

Not to be argumentative, but it's in the latest documentation... You would think that it wouldn't matter because the session property is set upon login to the db.

Joel Garry said...

Argue away. It wouldn't be the first time the docs were misleading and we all learned something when someone went "that's odd..."

Hemant's comment in the forum about it being sql*plus not sql was the forehead slapper for me. Looking forward to any trace results you find.

word: halsio

David Aldridge said...

How about if you're logged in to the target account and therefore omit it from the syntax?