This article is about getting a database export dump without having access to the expdp tool nor the filesystem. In some cases you just don’t have access to the databaseserver and no one is available (like a dba), still you need sufficient accessrights. Note: this is merely an excersize, if you’re planning on doing this notify your dba anyway, this article is not about secretly getting a database dump :-)
For this to work, you don’t need access to the expdp tool or the filesystem but you need some rights on a directory object (preferable read/write) and have execute rights on the DBMS_DATAPUMP package.
Every step is mentioned here, maybe in your situation you can skip some steps.
1. Create a test user
CREATE USER TSTUSER IDENTIFIED BY "testing" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK; GRANT UNLIMITED TABLESPACE TO TSTUSER; GRANT CREATE SESSION TO TSTUSER; GRANT CREATE TABLE TO TSTUSER; GRANT EXECUTE ON SYS.DBMS_DATAPUMP TO TSTUSER; GRANT EXP_FULL_DATABASE TO TSTUSER;
2. Create a directory object where the output will get written to
CREATE OR REPLACE DIRECTORY DATADIR_FLATF AS '[D:\directorypath]'; GRANT READ, WRITE ON DIRECTORY DATADIR_FLATF TO TSTUSER;
3. Create a controltextfile. In this file the filenames should be present which represent your dump files. Save your controlfile ‘lob_data.txt’ to the DATADIR_FLATF location.
DECLARE
out UTL_FILE.FILE_TYPE;
BEGIN
out := UTL_FILE.FOPEN('DATADIR_FLATF', 'lob_data.txt' , 'W');
UTL_FILE.PUT_LINE(out , '1,TWO_SCHEMAS.DMP,');
UTL_FILE.PUT_LINE(out , '2,TWO_SCHEMAS.LOG,');
UTL_FILE.FCLOSE(out);
END;
/
content of file is now (extra , at the end is not a typo):
1,TWO_SCHEMAS.DMP,
2,TWO_SCHEMAS.LOG,
4. Create a datapump export using DBMS_DATAPUMP
DECLARE
handle NUMBER;
status VARCHAR2(20);
BEGIN
handle := DBMS_DATAPUMP.OPEN ('EXPORT', 'SCHEMA');
DBMS_DATAPUMP.ADD_FILE (handle, 'TWO_SCHEMAS.DMP', 'DATADIR_FLATF');
DBMS_DATAPUMP.ADD_FILE (handle, 'TWO_SCHEMAS.LOG', 'DATADIR_FLATF', null, DBMS_DATAPUMP.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER (handle, 'SCHEMA_EXPR', 'IN (''SCHEMA1'',''SCHEMA2'')');
DBMS_DATAPUMP.START_JOB (handle);
DBMS_DATAPUMP.WAIT_FOR_JOB (handle, status);
DBMS_OUTPUT.PUT_LINE('Status : ' || status);
END;
/
5. Create an external table to gain access
CREATE TABLE lob_tab (
number_content NUMBER(10),
blob_content BLOB
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DATADIR_FLATF
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE DATADIR_FLATF:'lob_tab_%a_%p.bad'
LOGFILE DATADIR_FLATF:'lob_tab_%a_%p.log'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
number_content CHAR(10),
blob_filename CHAR(100)
)
COLUMN TRANSFORMS (blob_content FROM LOBFILE (blob_filename) FROM (DATADIR_FLATF) BLOB)
)
LOCATION ('lob_data.txt')
)
PARALLEL 2
REJECT LIMIT UNLIMITED
/
6. Test query
SELECT number_content, DBMS_LOB.getlength(blob_content) AS blob_length FROM lob_tab;
7. If you encounter the following
ORA-12801: error signaled in parallel query server P000 ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-29400: data cartridge error error opening file D:\directorypath\TWO_SCHEMAS.DMP
place extra , at the end of each line maybe?
8. Download the files to your local OS using your favorite tool
And there you have it. A complete dump of your schema choice locally available without having the expdp tool installed on your system.
9. Note, if you do have access to the filesystem, it looks similar to this
10. Drop tstuser and objects
DROP USER TSTUSER CASCADE;
