4

The query is a

CREATE TABLE t as SELECT .....; 

statement. Can I reliably count on Oracle to complete this after I disconnect?

After I re-connect, how can I check on the status of this query?

  • DDL is autocommitted implicitly. When you say "disconnect" do you mean accidentally during a long running transaction? – Vérace Jun 9 '14 at 3:29
  • Yes. When I work from home the internet connection is unreliable and I lose connection intermittently. – Ming-Chih Kao Jun 9 '14 at 5:26
  • I Googled "sqldeveloper autocommit disconnect" but didn't find anything. Why don't you just test by disconnecting your cable/modem/dongle while running a long transaction and check the results? – Vérace Jun 9 '14 at 7:51
  • I have tried it and in the few instances I tried it Oracle did complete the statement. I was hoping to have more confidence in submitting a statement and then logging off immediately. – Ming-Chih Kao Jun 9 '14 at 12:21
  • You could check out the SQLDeveloper forum at Oracle. (I had given this as an "answer" - it should have been a comment - answer deleted). – Vérace Jun 11 '14 at 11:35
5

Most likely, it will finish a DDL command behind the scenes, but I wouldn't bet my job on it. What if your CTAS action runs out of tablespace? You would never see the error. If you have an unrealiable Internet connection, then the best solution is probably

Simple/basic solution

  1. Use VNC or RDP to "jump" to a desktop or server that is in your datacenter. Run SQL Developer or whatever client on that desktop OS. If you get disconnected, you just reconnect VNC and you're where you left off. Your DB client never breaks its connection to the Oracle server.

Way cooler Oracle Scheduler "nohup" solution.

But if you can't do that, or just want to do something cooler with Oracle, you could fire off your DDL inside a Scheduler Job in Oracle. This is basically like using NOHUP in Unix if you're familiar. You invoke DBMS_SCHEDULER.CREATE_JOB and then a job slave process will proceed on the Oracle server running as your same user to complete the work. When you submit the job, it will return immediately with no error or confirmation other than "Anonymous block completed.", which just means that you've queued up an asynchronous job. If you get disconnected, it won't matter. You can log back in and check the status of the job via the USER_SCHEDULER_JOB_RUN_DETAILS built-in view.

I just did this in SQL Developer (note, user needs to have CREATE JOB privilege, and obviously CREATE TABLE, too):

-- submit this as a background job
BEGIN
  dbms_scheduler.create_job ( 
      job_name => 'MY_BACKGROUND_JOB'
    , job_type => 'PLSQL_BLOCK'    
    , job_action => 'BEGIN EXECUTE IMMEDIATE ''CREATE TABLE t AS SELECT * FROM all_objects''; END;'
    , enabled => TRUE
    , auto_drop => TRUE
  );
END;

...

-- Waited here a few seconds/minutes here for the job to complete.

...

-- Couple of checks to make sure the table got created
SELECT created FROM user_objects WHERE object_name='T';

CREATED           
-------------------
06/09/2014 18:06:42 


SELECT count(*) FROM t;

  COUNT(*)
----------
     59907 

Here's the status check. Note the status check says SUCCESS and error 0, so all good.

-- Check on last run of job with this name
SELECT * FROM
( SELECT job_name, status, error#, actual_start_date, log_date
  FROM user_scheduler_job_run_details
  WHERE job_name='MY_BACKGROUND_JOB'
  ORDER BY log_date DESC )
WHERE rownum=1;

enter image description here

I ran the job again just for kicks and I got a FAILED with error 955 which is expected since the table t already exists.

  • I like the 2nd suggestion. I'd usually use sqlplus/nohup myself, but think it's time to change my ways – Philᵀᴹ Jun 11 '14 at 10:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.