There are a number of ways to transfer data between Oracle Databases, one of which is to use the PL/SQL Datapump API – DBMS_DATAPUMP. If you wish to avail yourself of this utility but find the syntax a bit fiddly, you always have the option of getting SQLDeveloper to do (most of) it for you. What we’re talking about here is how to persuade the SQLDeveloper DB module to :
Create and execute a custom Datapump export job
do most of the work creating an import of a subset of the exported data
I’m determined to adopt a positive mental attitude this year.
When the train company explains delays by saying we have the wrong kind of sunshine, I prefer to marvel at the fact that the sun is shining at all in the depths of an English Winter. Let’s face it, it’s a rare enough phenomenon in the summer.
The slow-running of the train caused by this rare natural phenomenon also gives me more time to write this post.
There’s more “good” news – Datapump Import tends to be rather slow when it comes to applying optimizer statistics.
This is because it insists on doing it one row at a time.
All of which provides us with an opportunity to work from home optimize our import job… by not bothering importing the stats.
“Hang on”, you’re thinking, “won’t that mean you have to re-gather stats after the import, which probably won’t be that quick either ?”
It was a dark, stormy night in Redwood Shores. Only a single light burned at Oracle Towers. The Marketing Department was still locked in conference.
Countless flip-chart sheets littered the room, the result of thought-showers, story-boarding and numerous break-out imagineering sessions.
The challenge with which they had grappled all this time ? How to re-brand the long-time staple, but not particularly exciting export/import utility.
Suddenly, one nameless alpha-male ( and it must surely have been a man) rose to his feet, propelled by a lightning strike of inspiration. In a great, booming voice, dripping with testosterone, pelvis-thrusting beneath his ample girth for added emphasis, he announced to the room, “I know, let’s call it Data Pump !”
The name may have changed, the odd bell-and-whistle added, but the purpose remains unchanged. Export/Import ( Data Pump, if you must), is a utility for transferring objects and data from one Oracle instance to another, irrespective of the Operating System on which either the source or target database is running. Continue reading →