Automation, Scheduling, and Data Movement
Automation, Scheduling, and Data Movement tests DBMS_SCHEDULER, Data Pump and the loader tools that let routine work run without a person.
DBMS_SCHEDULER
Objects are programs, schedules, jobs, job classes and chains. A job has a job_type (PLSQL_BLOCK, STORED_PROCEDURE, EXECUTABLE, CHAIN), a repeat_interval in calendar syntax such as 'FREQ=DAILY;BYHOUR=2;BYMINUTE=30', and an enabled flag; CREATE_JOB defaults to enabled, so pass enabled=>FALSE when building first. Monitor with DBA_SCHEDULER_JOBS, DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_JOB_RUN_DETAILS, and use RUN_JOB for ad-hoc runs. Privileges are CREATE JOB plus EXECUTE on the program; job classes map to resource consumer groups.
Data Pump
expdp and impdp run on the database server and use DIRECTORY objects, so the OS path must exist there with READ and WRITE granted to the operating user. Useful parameters include SCHEMAS, TABLES, CONTENT, PARALLEL, REMAP_SCHEMA, REMAP_TABLESPACE, NETWORK_LINK for importing directly from a remote database, and SQLFILE to emit DDL without importing. Jobs are visible in DBA_DATAPUMP_JOBS.
Worked example
You must refresh DEV from PROD nightly. Create a database link PROD_LINK from DEV, a directory object DEV_PUMP on the DEV server, then schedule impdp with NETWORK_LINK=PROD_LINK, REMAP_SCHEMA=PROD:DEV and REMAP_TABLESPACE=PROD_DATA:DEV_DATA. No dump file is created because the job reads from the remote database and writes into DEV, which also avoids needing storage sized for a full export.
Traps
Assuming expdp writes to the client machine; forgetting directory object privileges; omitting REMAP_TABLESPACE when the source tablespace is absent on the target; relying on legacy DBMS_JOB; creating a scheduler job in the root when the object lives in a PDB.