Unloading data using External Table and Data Pump

Just for being complete and to update people on a minor detail, here is a quick example of a data pump driven External Table.

SQL> CREATE TABLE EMP_50
2 ORGANIZATION EXTERNAL
3 ( TYPE oracle_datapump
4 DEFAULT DIRECTORY dmp_dir
5 LOCATION (‘emp_50.dmp'))
6 )
7 AS SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 50
8 ;

Table Created

SQL> SELECT COUNT(1) FROM EMP_50;

COUNT(1)
----------
45

There are a couple of things here. First of all, you can happily use the wildcards in the select these days. I noticed some blog posts declaring you cannot do this, on my 11.1.0.7 install I could nicely do this.

Secondly, and this is why I think this is quite a nice thing to do, you can filter data using regular SQL. As in my typical examples, I used a simple where clause, but there is nothing stopping you from writing real extract clauses for ETL jobs.

Data pump gives you a nice way to unload data and avoid using DB Links. I found the following on another blog out there on how to transfer the file from a remote box (original post here - look in the comments):

Additional info also after 10g; Transferring a File to a Different Database(10g ASM aware)

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
SOURCE_DIRECTORY_OBJECT => ‘SOURCE_DIR’,
SOURCE_FILE_NAME => ‘exm_old.txt’,
DESTINATION_DIRECTORY_OBJECT => ‘DEST_DIR’,
DESTINATION_FILE_NAME => ‘exm_new.txt’
DESTINATION_DATABASE => ‘US.ACME.COM’);
END;

In order to transfer a file the other way around, you must replace the PUT_FILE procedure with the GET_FILE procedure. You can monitor copying progress using V$SESSION_LONGOPS view.
---

This makes this all a very nice way of running ETL from Oracle to Oracle, avoiding those dreaded database links...

Obviously, things like ftp and scp also work on moving files around, but I kind of like the DBMS_FILE_TRANSFER example.

Comments:

Thanks for the article. I would like to use this feature to unload a very large database. Do you know if there is any way to pause/stop/resume the process like you would a Datapump job. Appreciate your feedback. thanks.

Posted by mathew on August 04, 2009 at 11:11 PM PDT #

I believe you cannot... the external table has no hooks for starting and stopping... Corrections welcome. JP

Posted by Jean-Pierre on August 05, 2009 at 12:15 AM PDT #

Post a Comment:
Comments are closed for this entry.
About

The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
4
5
6
7
8
9
10
11
12
13
14
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today