So today I wanted to export a 35GB partitioned table and really didn’t feel like going through the process of using DataPump; the thought of creating a parameter file for one lousy table didn’t appeal to me. So I used the old trusty Export (exp). It barfed with an internal consistency error. Hmmmm. I can’t be bothered solving that one for now, I just want my data. No Toad, you are not going to put 38 million rows into Excel or a pretty INSERT script file. I don’t think so.
Why make it simple eh? Let’s use the DBMS_DATAPUMP API to do it. Heck, yeah!
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_dp_handle NUMBER;
l_last_job_state VARCHAR2 (30) := ‘UNDEFINED’;
l_job_state VARCHAR2 (30) := ‘UNDEFINED’;
l_sts KU$_STATUS;
BEGIN
l_dp_handle :=
DBMS_DATAPUMP.open (operation => ‘EXPORT’,
job_mode => ‘TABLE’,
remote_link => NULL,
job_name => ‘TX_EXPORT’,
version => ‘LATEST’);
DBMS_DATAPUMP.add_file (handle => l_dp_handle,
filename => ‘SPB.dmp’,
directory => ‘DATAPUMP_EXP’);
DBMS_DATAPUMP.add_file (handle => l_dp_handle,
filename => ‘SPB.log’,
directory => ‘DATAPUMP_EXP’,
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle,
name => ‘NAME_EXPR’,
VALUE => ‘= ”TRANSACTION_AD”’);
DBMS_DATAPUMP.start_job (l_dp_handle);
DBMS_DATAPUMP.detach (l_dp_handle);
END;
/
So there it is. What did I do?
- Define some variables
- Open an export job for type TABLE and gave it a job name
- Added a file to receive the data, referencing the pre-created and r/w granted directory
- Added a file to receive the log, ditto on the directory
- Created the filter that uses a name expression to export the single table “TRANSACTION_AD”
- Started the job
- Detached the session (I suspect if I had not done this, the session would not return until the job completed; I wanted to walk away and get coffee and have a natter).
That was it. I ended up with a pair of dump/log files that I could use elsewhere for my nefarious purposes. 
The benefits of being idle.
