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
 
 
Search WWH ::




Custom Search