We recently bought another company and we are in the process of integrating our systems with theirs. One requirement was to import their daily business data into our Business Intelligence system for reporting. We agreed they would provide an Oracle datapump export file for us to import. We wanted to automate this and have a non-privileged application owner perform the import. I created a stored procedure that could run the import as the system user and granted execute on the procedure to the application owner. The dbms_datapump package has lots of options and it was fun exploring them to find the best way of doing this.
Setup –
Run as sys :
grant execute on dbms_lock to system;
Run as system:
CREATE OR REPLACE DIRECTORY SOURCE AS '/nfsmount/exp/incoming'; CREATE OR REPLACE PROCEDURE SYSTEM.SCHEMA_IMPORT ( dumpfilename IN VARCHAR2) IS h1 NUMBER; -- data pump job handle job_state VARCHAR2 (30); status ku$_Status; -- data pump status job_not_exist EXCEPTION; PRAGMA EXCEPTION_INIT (job_not_exist, -31626); BEGIN h1 := DBMS_DATAPUMP.open (operation => 'IMPORT', job_mode => 'SCHEMA', job_name => NULL); DBMS_DATAPUMP.set_parameter (h1, 'TABLE_EXISTS_ACTION', 'TRUNCATE'); DBMS_DATAPUMP.add_file (h1, dumpfilename, 'SOURCE'); DBMS_DATAPUMP.add_file (h1, dumpfilename || '.log', 'SOURCE', NULL, DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); DBMS_DATAPUMP.metadata_remap (h1, 'REMAP_SCHEMA', 'FROMSCHEMA', 'TOSCHEMA'); DBMS_DATAPUMP.start_job (h1); job_state := 'UNDEFINED'; BEGIN WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED') LOOP status := DBMS_DATAPUMP.get_status ( handle => h1, mask => DBMS_DATAPUMP.ku$_status_job_error + DBMS_DATAPUMP.ku$_status_job_status + DBMS_DATAPUMP.ku$_status_wip, timeout => -1); job_state := status.job_status.state; DBMS_LOCK.sleep (10); END LOOP; EXCEPTION WHEN job_not_exist THEN DBMS_OUTPUT.put_line ('job finished'); END; COMMIT; END; / grant execute on schema_import to APP_OWNER;
Execution –
Run as APP_OWNER:
EXECUTE SYSTEM.SCHEMA_IMPORT('FILENAME.DMP')
Pingback: Automating and delegating datapump imports with PL/SQL – All Things Oracle
any live example or test
LikeLike
Hi Sajid,
You need a target database where you can run this. A schema expdp dump file from another database should go into the directory location. example: ‘/nfsmount/exp/incoming’
Steve
LikeLike
Tried it, but got an error out:
ERROR at line 1:
ORA-31626: job does not exist
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 1137
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 5259
ORA-06512: at “EXEMPLARBAK.SCHEMA_IMPORT”, line 10
ORA-06512: at line 1
LikeLike
Hi Jack,
This post is nine years old. I think Oracle may have changed some of their built-in stored procedures since then. If you need the functionality, you can probably get it working with a few changes. If you are successful, please post it back here in a comment.
Thanks!
Steve
LikeLike