Oracle SQLDeveloper – Does Oracle Run Queries to Completion After SQLDeveloper Disconnects?

Networkoracleoracle-sql-developer

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?

Best Answer

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.

Related Question