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)