Unlike old utilities exp and imp DataPump operates on files located on database server. This is not always convenient, there should be ways to transfer files to and from database server machine to client. Package UTL_FILE provides methods to read and write files as well as delete and rename them; however there is no method to list files in server directories.
AlderPump versions 2.2 and newer offer two methods for server-side directory browsing: via an internal Oracle package, or a Java stored procedure.
Picking method right for your situation
Both PL/SQL and Java approaches are supported equally; choice depends on your situation. Once either (or both) method is configured, AlderPump detects this automatically upon connecting to database; PL/SQL is preferred over Java if both are available.
As a side note, both methods allow browsing of arbitrary folder, but AlderPump restricts this functionality by only allowing access to folders exposed via DIRECTORY objects - for security reasons.
PL/SQL method employs undocumented package SYS.DBMS_BACKUP_RESTORE normally used by RMAN. Procedure searchFiles() populates in-memory table with files matching the mask. The table then queried by AlderPump.
For further details please refer to Christopher Poole's page:
Java method creates a stored procedure and PL/SQL wrapper around it. Procedure stores names and sizes of files (filtering out directories) in a temporary CLOB which is returned to AlderPump. The procedure can be created in any schema; in fact it can be also created in several schemas. AlderPump is using:
Comparison of PL/SQL and Java methods:
|Compatibility||Works on any 10g+ database||Requires Java support on database server|
|Uses documented features||Employs undocumented package SYS.DBMS_BACKUP_RESTORE||Yes|
|Extra objects created||Single view in SYS schema, grants on SYS package||Java procedure and PL/SQL wrapper in one or more schemas; JAVAUSERPRIV grant to each AlderPump user|
|Requires SYSDBA privilege on installation||Yes||No|
|Requires SYSDBA privilege on use||No||No|
|Runtime overhead||Memory consumtion depends on number of files in the requested directory and all its subfolders. File lengths fetched separately.||Memory consumtion depends on number of files in the requested directory (but JVM footprint is substantial, on some platforms it consumes 1G of RAM even while idle.).|
|Ease of removal||Easy and straightforward||Review needed before revoking grants.|
As rule of thumb, we recommend Java method if Java already installed and used on the database. If Java not used, consider PL/SQL approach.
Installation script is ConfigureDirBrowsingPLSQL.sql located in AlderPump folder.
sqlplus -s sys/password@tns as sysdba @ConfigureDirBrowsingPLSQL.sql
the script creates view around built-in table and grants public access to it. Public access is safe because view contents is private.
GRANT EXECUTE ON SYS.DBMS_BACKUP_RESTORE TO user_name;
SELECT 'revoke execute on sys.dbms_backup_restore from '|| grantee||';' AS SqlText FROM dba_tab_privs WHERE owner = 'SYS' AND table_name = 'DBMS_BACKUP_RESTORE' ;
DROP VIEW sys.x_$krbmsft;
Installation script is ConfigureDirBrowsingJAVA.sql located in AlderPump folder.
sqlplus user/password@tns @ConfigureDirBrowsingJAVA.sql
the script creates java source named "ap$_Dir" and PL/SQL wrapper function "ap$_DirLs" (both names are case sensitive).
GRANT EXECUTE ON "ap$_DirLs" TO user_name; GRANT JAVAUSERPRIV TO user_name;
For convenience, you may combine these two privileges into a role. Please note that roles take effect on re-connection.
DROP FUNCTION "ap$_DirLs"; DROP JAVA SOURCE "ap$_Dir";
SELECT grantee FROM dba_role_privs WHERE granted_role = 'JAVAUSERPRIV' AND grantee NOT IN (SELECT name FROM sys.ku_noexp_tab WHERE obj_type='SCHEMA') ;Revoke statement might look like:
REVOKE JAVAUSERPRIV FROM user_name;
AlderPump checks for available browsing methods upon connecting to database; it should be restarted to recognize the changes.
© 2007-2012 AlderProgs Consulting Ltd.