After you clone your pluggable database

Recently I cloned one my (pluggable) databases to a throwaway copy so that I could do some testing of an upcoming application deployment against comparable data volumes to make sure that the deployment would go smoothly. The clone process worked fine; that’s one of the things I enjoy about pluggable databases, just single CREATE command and you’re pretty much done. But shortly after I’d completed the clone, a colleague who also works on the database shot through a Slack message

“Hey, I just got a duplicate copy of the task list email?”

Whoops! That’s the problem with clones – they are…hmmm…what’s the word I’m looking for? Ah yes, CLONES!

This means that once the clone as completed, things like scheduler jobs and other automated tasks in your source database will be transferred along with the clone to your new database. If you have scheduler jobs that run in your database, and you’d rather they do not run on your cloned version of that database, here’s a quick script to handle that using the AFTER CLONE mode of database triggers.

create or replace 
trigger post_clone_cleanup after clone on pluggable database
begin 
  for i in ( select job_name from dba_scheduler_jobs
             where [sensible criteria for your system]
           )
    dbms_scheduler.disable(i.job_name);
  end loop;
end; 
/ 

Note that you could also be a little more brutal and simply turn off the job queue entirely after cloning

create or replace 
trigger post_clone_cleanup after clone on pluggable database
begin 
  execute immediate 'alter system set job_queue_processes = 0 scope=both';
end; 
/ 

but I’d recommend that should not be your default course of action, because the database itself uses the scheduler for various standard maintenance activities that you might want to continue in your fresh clone.

Note that if you also have jobs submitted via the older DBMS_JOB mechanism, then as long as you are on 19c or above, then these are also scheduler jobs

SQL> variable j number
SQL> exec dbms_job.submit(:j,'begin null;end;',sysdate+100);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select job_name from dba_scheduler_jobs;

JOB_NAME
---------------------------------------------------------------------
PURGE_LOG
FILE_WATCHER
PMO_DEFERRED_GIDX_MAINT_JOB
CLEANUP_NON_EXIST_OBJ
CLEANUP_ONLINE_IND_BUILD
CLEANUP_TAB_IOT_PMO
CLEANUP_TRANSIENT_TYPE
CLEANUP_TRANSIENT_PKG
CLEANUP_ONLINE_PMO
FILE_SIZE_UPD
ORA$AUTOTASK_CLEAN
HM_CREATE_OFFLINE_DICTIONARY
DRA_REEVALUATE_OPEN_FAILURES
ORA$PREPLUGIN_BACKUP_JOB
BSLN_MAINTAIN_STATS_JOB
FGR$AUTOPURGE_JOB
RSE$CLEAN_RECOVERABLE_SCRIPT
SM$CLEAN_AUTO_SPLIT_MERGE
LOAD_OPATCH_INVENTORY
XMLDB_NFS_CLEANUP_JOB
DBMS_JOB$_541
ORDS_HOUSEKEEPING_JOB
ATE_MAINT_EVERY_1_HOUR
ATE_MAINT_EVERY_4_HOUR
PERF_GENERATE_AWR_JOB
ORACLE_APEX_BACKUP
ORACLE_APEX_PURGE_SESSIONS
ORACLE_APEX_MAIL_QUEUE
ORACLE_APEX_WS_NOTIFICATIONS
ORACLE_APEX_DAILY_MAINTENANCE
ORACLE_APEX_REST_SOURCE_SYNC
ORACLE_APEX_AUTOMATIONS
ORACLE_APEX_AUTO_APPROVAL
ORACLE_APEX_DICTIONARY_CACHE
ORACLE_APEX_ISSUE_EMAILS

35 rows selected.

so the first trigger above should take care of it without any additional code needed.

(Shout out to Nilay Panchal who pointed me to the ‘after clone’ trigger)