Home » RDBMS Server » Server Utilities » Initiate a DataPump from a Procedure
Initiate a DataPump from a Procedure [message #353932] Wed, 15 October 2008 12:42
tmcallister
Messages: 107
Registered: December 2007
Senior Member
I'm attempting to automate a data loading process, and for a part of this process it seems to me that the ideal method would be to initiate a DataPump from within a package/procedure.

However the complexity of the privileges necessary (roles don't work) seem to indicate that this isn't ... the normal method.

Question #1: Assuming you want to duplicate existing databases in an existing production stream on a regular basis, what is the ideal method to do so?

The current process uses imp/exp/scp/gzip/pipes scripts, and I'd like to update that to direct network impdp/expdp PL/SQL in a package/procedure.

Question #2: How does one grant the 'manage any queue' system privilege?

Using the following I determined all the privileges required:

SELECT DISTINCT 'grant ' || PRIVILEGE || ' to express_loader;'
           FROM role_sys_privs
          WHERE ROLE IN ('EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
       ORDER BY 1


However 2 of these required ... a different method to grant:

grant ADMINISTER RESOURCE MANAGER to <MY_DATA_LOADER>;
grant MANAGE ANY QUEUE to <MY_DATA_LOADER>;

The first I discovered on my own:
BEGIN
SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE
  (GRANTEE_NAME   => '<MY_DATA_LOADER>', 
   PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
   ADMIN_OPTION   => FALSE);
END;


But 'Manage Any Queue' is throwing me for a loop.
Previous Topic: Tricky Loading of file!!
Next Topic: DATABASE ANALYZE ISSUE
Goto Forum:
  


Current Time: Tue Dec 06 13:54:18 CST 2016

Total time taken to generate the page: 0.10835 seconds