Database Reference
In-Depth Information
The fact that XML is used has allowed the
EXPDP
and
IMPDP
tools to literally leapfrog the old
EXP
and
IMP
tools
with regard to their capabilities. In Chapter 15, we'll take a closer look at these tools. Before we get there, however, let's
see how we can use this
Data Pump
format to quickly extract some data from database A and move it to database B.
We'll be using an “external table in reverse” here.
External tables, originally introduced in Oracle9
i
Release 1, gave us the ability to read flat files—plain old text
files—as if they were database tables. We had the full power of SQL to process them. They were read-only and
designed to get data from outside Oracle in. External tables in Oracle 10
g
Release 1 and above can go the other way:
they can be used to get data out of the database in the
Data Pump
format to facilitate moving the data to another
machine or another platform. To start this exercise, we'll need a
DIRECTORY
object, telling Oracle the location to
unload to:
EODA@ORA12CR1> create or replace directory tmp as '/tmp';
Directory created.
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 * from all_objects
9 /
Table created.
And that literally is all there is to it: we have a file in
/tmp
named
allobjects.dat
that contains the contents of
the query
select * from all_objects
. We can peek at this information:
EODA@ORA12CR1> !strings /tmp/allobjects.dat | head
"EODA"."U"
x86_64/Linux 2.4.xx
AL32UTF8
12.00.00.00.00
001:001:000001:000001
i<?xml version="1.0"?>
<ROWSET>
<ROW>
<STRMTABLE_T>
That's just the head, or top, of the file. Now, using a binary FTP (same caveat as for a DMP file), you can move that
file to any other platform where you have Oracle 12
c
installed and by issuing a
CREATE DIRECTORY
statement (to tell
the database where the file is) and a
CREATE TABLE
statement, such as this:
create table t
( OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19),
CREATED DATE,
LAST_DDL_TIME DATE,
