In defence of being lazy


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. 8432553117_444c560d40_z

The benefits of being idle.

Categories: Computing, Database, ToolsTags: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

dbaonTap

What's #OnTapToday

globetrottingteen

If travel is an addiction, I'm afflicted.

The Black Veil One

My Diary Travel, Photography, My Sight About Somethings

Off to Ireland

Trotz Knacks den Schritt ins Ausland wagen

Randy Johnson's Oracle Blog

Oracle DBA Topics Including Unix, Scripting, & Automation

Model For {Change

Creating a positive change.

Globe-T.

Le Bonnet voyageur • The travelling Winter Hat

Another Photography Enthusiast

CLICKS THROUGH MY EYES

corkandspoon

Adventures in food and drink

Break Your Cage

Embracing The Unknown - Travel is not a pause from life, merely an extension of it.

My Girls On Tour

Fashion & Travel Blog by Abigail Royston

NYCQuirk

Two college girls trying to make the most out of living in NYC without breaking the bank!

Don Charisma

because anything is possible with Charisma

Bunny Kitchen

Exploring the possibilities of cruelty free food

Angelart Star

The beautiful picture of angels makes you happy.

%d bloggers like this: