Data Pump Import Scheduler Utilities

Purpose

Execute a Data Pump import to copy a customer-provided dump file into the target custom schema.

This procedure supports customers moving from DAS to RDS. Refreshing the custom schema helps move bulk data as part of a broader customer-managed porting exercise. Only TABLE, VIEW, MATERIALIZED_VIEW, and TRIGGER objects are supported for import. This process is not intended to provide a lift-and-shift migration of DAS-based customizations.

This procedure may be used for initial seeding or re-run multiple times during the customer lifecycle. The import performs an object-level replace, so objects with the same name in the target schema are replaced by the contents of the source dump file.

The process is designed to be re-run safely without development intervention.

Preparation for Import

This is a user-managed activity. Before running the import, prepare the source export and Object Storage artifacts as follows.

Create a dedicated Object Storage bucket. Create one OCI Object Storage bucket dedicated to this import activity, with one bucket used per schema. The bucket should contain only the Data Pump artifacts for this request, including dump files and logs.
Export the source schema. Run a Data Pump schema export (expdp) of the source schema to a customer-controlled filesystem.
Preserve all dump pieces. If the export produces multiple dump pieces, such as DUMPFILE=...%U.dmp , confirm that all pieces are created successfully and uploaded to the dedicated bucket.
Freeze DDL during export. Do not run CREATE, ALTER, DROP, TRUNCATE, RENAME,or other DDL against in-scope objects while the export is running. DDL changes during export can cause errors or inconsistent results and may require a new export.
Upload export artifacts. Upload all dump files and the Data Pump export log to the dedicated Object Storage bucket.
Review the export log before import. Review the export log for errors before starting the import. If errors are present, resolve them and re-export before proceeding. The export log should always be reviewed alongside the import log when validating results.

Import Scope and Limitations

Only the following database object types are imported:

TABLE_VIEW
MATERIALIZED_VIEW
TRIGGER

All other object types are ignored.

Additional limitations and preparation requirements:

Grants and privileges are excluded and must be reapplied separately if needed.
Sequences are excluded.
If table data depends on sequences, identity columns, or default-sequence behavior, copy the required data into a staging or duplicate table that does not depend on sequence-generated values before export. This ensures the required key values are materialized in the exported table data.
Because the import uses object replacement semantics, existing objects of the same name in the target schema are replaced by the imported definitions and data.
Objects that already exist in the target schema but are not present in the source dump file are not removed by this process.
This procedure supports bulk data movement and selected schema objects only. It does not provide full migration of DAS-based customizations.

What This Script Provides

`submit_import_job`

Creates and immediately starts a DBMS_SCHEDULER job of type PLSQL_BLOCK. The job launches a Data Pump import from a PAR URL and then detaches so the import can continue asynchronously.

`copy_import_log_to_par`

Uploads a Data Pump import log file from DATA_PUMP_DIR to an Object Storage PAR URL.

Notes

This version does not require the import itself to be a stored procedure job action.
The scheduler job runs an anonymous PL/SQL block.
auto_drop is set to FALSE so you can inspect the scheduler job later.
The scheduler job status only tells you whether the launch block succeeded.
The actual import status is checked separately in USER_DATAPUMP_JOBS.
This version assumes the dump file URL is a PAR URL. Because of that, it uses a dummy credential (DUMMY_CRED) rather than a real Object Storage credential. If you are not using a PAR URL, replace that approach with a real credential strategy for your environment.
DBMS_OUTPUT.PUT_LINE calls are included in the job block so the submitted argument values are visible in scheduler logging where available.

Full Script

CREATE OR REPLACE PROCEDURE submit_import_job(
	p_par_url IN VARCHAR2,
	p_source_schema IN VARCHAR2,
	p_target_schema IN VARCHAR2,
	p_log_filename IN VARCHAR2,
	p_job_name OUT VARCHAR2
) AUTHID CURRENT_USER
AS
	l_job_action CLOB;
BEGIN
	p_job_name := 'IMP_DP_' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF3');
	l_job_action :=
		q'[
DECLARE
	l_dump_filename VARCHAR2(32767) := ]' || DBMS_ASSERT.ENQUOTE_LITERAL(p_par_url) || q'[;
	l_log_filename VARCHAR2(4000) := ]' || DBMS_ASSERT.ENQUOTE_LITERAL(p_log_filename) || q'[;
	l_source_schema VARCHAR2(128) := ]' || DBMS_ASSERT.ENQUOTE_LITERAL(UPPER(p_source_schema)) || q'[;
	l_target_schema VARCHAR2(128) := ]' || DBMS_ASSERT.ENQUOTE_LITERAL(UPPER(p_target_schema)) || q'[;
	l_credential VARCHAR2(128) := 'DUMMY_CRED';
	l_datapump_dir VARCHAR2(30) := 'DATA_PUMP_DIR';
	l_parallel PLS_INTEGER := 24;
	l_datapump_handle NUMBER;
	l_count PLS_INTEGER;
BEGIN
	SELECT COUNT(*)
		INTO l_count
		FROM USER_CREDENTIALS
	WHERE UPPER(credential_name) = UPPER(l_credential);
	IF l_count = 0 THEN
		DBMS_CLOUD.CREATE_CREDENTIAL(
			credential_name => l_credential,
			username => 'dummy_user',
			password => 'dummy_password'
		);
	END IF;
	DBMS_OUTPUT.PUT_LINE('p_par_url=' || l_dump_filename);
	DBMS_OUTPUT.PUT_LINE('p_source_schema=' || l_source_schema);
	DBMS_OUTPUT.PUT_LINE('p_target_schema=' || l_target_schema);
	DBMS_OUTPUT.PUT_LINE('p_log_filename=' || l_log_filename);
	l_datapump_handle := DBMS_DATAPUMP.OPEN(
		operation => 'IMPORT',
		job_mode => 'TABLE'
	);
	DBMS_DATAPUMP.ADD_FILE(
		handle => l_datapump_handle,
		filename => l_dump_filename,
		directory => l_credential,
		filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE
	);
	DBMS_DATAPUMP.ADD_FILE(
		handle => l_datapump_handle,
		filename => l_log_filename,
		directory => l_datapump_dir,
		filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
	);
	DBMS_DATAPUMP.METADATA_FILTER(
		handle => l_datapump_handle,
		name => 'INCLUDE_PATH_EXPR',
		value => q'| IN (
			'TABLE',
			'VIEW',
			'MATERIALIZED_VIEW',
			'TRIGGER'
		) |'
	);
	DBMS_DATAPUMP.METADATA_FILTER(
		handle => l_datapump_handle,
		name => 'SCHEMA_LIST',
		value => '''' || l_source_schema || ''''
	);
	DBMS_DATAPUMP.METADATA_REMAP(
		handle => l_datapump_handle,
		name => 'REMAP_SCHEMA',
		old_value => l_source_schema,
		value => l_target_schema
	);
	DBMS_DATAPUMP.SET_PARAMETER(
		handle => l_datapump_handle,
		name => 'METRICS',
		value => 1
	);
		DBMS_DATAPUMP.SET_PARAMETER(
		handle => l_datapump_handle,
		name => 'LOGTIME',
		value => 'ALL'
	);
	DBMS_DATAPUMP.SET_PARAMETER(
		handle => l_datapump_handle,
		name => 'TABLE_EXISTS_ACTION',
		value => 'REPLACE'
	);
	DBMS_DATAPUMP.SET_PARALLEL(
		handle => l_datapump_handle,
		degree => l_parallel
	);
	DBMS_DATAPUMP.START_JOB(l_datapump_handle);DBMS_DATAPUMP.DETACH(l_datapump_handle);
EXCEPTION
	WHEN OTHERS THEN
		BEGIN
			IF l_datapump_handle IS NOT NULL THEN
				DBMS_DATAPUMP.DETACH(l_datapump_handle);
			END IF;
		EXCEPTION
			WHEN OTHERS THEN NULL;
		END;
		RAISE;
END;
]';
	DBMS_SCHEDULER.CREATE_JOB(
		job_name => p_job_name,
		job_type => 'PLSQL_BLOCK',
		job_action => l_job_action,
		start_date => SYSTIMESTAMP,
		enabled => TRUE,
		auto_drop => FALSE,
		comments => 'Launch Data Pump import from PAR URL'
);
END;
/
SHOW ERRORS
CREATE OR REPLACE PROCEDURE copy_import_log_to_par(
	p_log_filename IN VARCHAR2,
	p_target_par_url IN VARCHAR2,
	p_directory_name IN VARCHAR2 DEFAULT 'DATA_PUMP_DIR'
	) AUTHID CURRENT_USER
AS
BEGIN
	DBMS_CLOUD.PUT_OBJECT(
		credential_name => NULL,
		object_uri => p_target_par_url,
		directory_name => p_directory_name,
		file_name => p_log_filename
	);
END;
/
SHOW ERRORS

How To Use `submit_import_job`

DECLARE
	l_job_name VARCHAR2(128);
BEGIN
	submit_import_job(
		p_par_url => 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../o/mfcs_rds_exp_%U.dmp',
		p_source_schema => 'MFCS_RDS_CUSTOM',
		p_target_schema => 'BC_RDS_CUSTOM',
		p_log_filename => 'mfcs_rds_imp.log',
		p_job_name => l_job_name
	);
	DBMS_OUTPUT.PUT_LINE('Submitted scheduler job: ' || l_job_name);
END

How To Use `copy_import_log_to_par`

BEGIN
	copy_import_log_to_par(
		p_log_filename => 'mfcs_rds_imp.log',
		p_target_par_url => 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../o/mfcs_rds_imp.log'
	);
END;
/

How To Query Scheduler Job Status

This shows whether the scheduler block itself succeeded or failed:

SELECT 	log_id,
		job_name,
		status,
		error#,
		log_date,
		req_start_date,
		actual_start_date,
		run_duration,
		additional_info
FROM	user_scheduler_job_run_details
WHERE 	job_name = :job_name
ORDER 	BY log_id DESC;

This shows general scheduler events:

SELECT 	log_id,
		job_name,
		operation,
		status, 
		log_date,
		additional_info
FROM	user_scheduler_job_log 
WHERE 	job_name = :job_name 
ORDER 	BY log_id DESC;

How To Query Data Pump Import Status

This tells you whether the import itself is still running:

ELECT 	owner_name, 
		job_name, 
		operation, 
		job_mode, 
		state,
		degree,
		attached_sessions
FROM	user_datapump_jobs 
ORDER 	BY job_name;

Typical interpretation:

RUNNING or EXECUTING : import is active
NOT RUNNING: job exists but is not currently executing
no row found: the Data Pump job may have completed and cleaned itself up

Typical Sequence

Run the full script once to create both procedures.
Call submit_import_ job.
Check USER SCHEDULER JOB_RUN_DETAILS to confirm the launch block succeeded.
Check USER_DATAPUMP_JOBS to monitor the actual import.
After the import finishes, call copy_import_log_to_par.
Retrieve the uploaded log from Object Storage.

Optional Cleanup

BEGIN
		DBMS_SCHEDULER.DROP_JOB(job_name => :job_name, force => TRUE);
END;

Basic Troubleshooting

If the scheduler job fails, query USER_SCHEDULER_JOB_RUN_DETAILS and review STATUS, ERROR#, and ADDITIONAL_INFO.
If the scheduler job succeeds but the import does not complete, query USER_DATAPUMP_JOBS to see whether the Data Pump job is still running.
If the import does not behave as expected, review both the export log and the import log, since some failures originate in the export and only surface later during import.

If the import log file is not where you expect, confirm the log file name and check access to DATA_PUMP_DIR.

If the dump file cannot be opened, verify that the PAR URL is complete, unexpired, and points to the expected dump file object or sequence.

If schema remap results are not as expected, confirm the source schema name in the dump file and the requested target schema name.

If uploading the log file fails, verify that the destination PAR URL allows upload to the intended object path.

For additional guidance, consult the Oracle documentation for DBMS_SCHEDULER, DBMS_DATAPUMP, DBMS_CLOUD, and Autonomous Database Data Pump import.

F Data Pump Import Scheduler Utilities