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.
] 
Blog
Search
Help
Members
Register
Login
Home
