Database Reference
In-Depth Information
Data Pump Unload
Oracle9
i
introduced external tables as a method to read external data into the database. Oracle 10
g
introduced the
ability to go the other direction and use a
CREATE TABLE
statement to create external data, to unload data from the
database. Starting with Oracle 10
g
, this data is extracted in a proprietary binary format known as
Data Pump format
,
which is the same format the
EXPDP
and
IMPDP
tools provided by Oracle to move data from database to database use.
Using the external table unload is actually quite easy—as easy as a
CREATE TABLE AS SELECT
statement. To start,
we need a
DIRECTORY
object:
EODA@ORA12CR1> create or replace directory tmp as '/tmp';
Directory created.
Now we are ready to unload data to this directory using a simple
SELECT
statement, for example:
EODA@ORA12CR1> create table all_objects_unload
2 organization external
3 ( type oracle_datapump
4 default directory TMP
5 location( 'allobjects.dat' )
6 )
7 as
8 select
9 *
10 from all_objects
11 /
Table created.
I purposely chose the
ALL_OBJECTS
view because it is a quite complex view with lots of joins and predicates.
This shows you can use this datapump unload technique to extract arbitrary data from your database. We could add
predicates or whatever else we wanted to extract a slice of data.
■
this example shows you can use this to extract arbitrary data from your database. Yes, that is repeated text.
From a security perspective, this does make it rather easy for someone with access to the information to take the
information elsewhere. You need to control access to the set of people who have the ability to create
DIRECTORY
objects
and write to them, and who have the necessary access to the physical server to get the unloaded data.
Note