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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
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;
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.