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;