Use oracle_datapump to create an external table

1. Create an External table using datapump. The data comes from an internal entity table.

-- First create the scott. countries entity table for experiment

SQL> create table scott. countries (

2 country_id number,

3 country_name varchar2 (30 ),

4 country_reigion varchar2 (30)

5 );

 

Table created.

 

SQL> insert into scott. countries values (1, 'America ', 'America ');

 

1 row created.

 

SQL> insert into scott. countries values (2, 'China', 'zhuzhou ');

 

1 row created.

 

SQL> insert into scott. countries values (3, 'Japan ', 'dongjing ');

 

1 row created.

 

SQL> commit;

 

Commit complete.

-- Refer to the case where the official datapump creates an External table (whose data comes from an internal entity table), as shown below:

 

 

Create an External table after making the following changes (in red)

Create table scott. countries_ext

ORGANIZATION EXTERNAL

(

TYPE Oracle_DATAPUMP

Default directory dir -- generate the dmp file storage path

LOCATION ('countries _ ext. dmp ') -- generate the name of the external file

)

As select * FROM scott. countries; -- data comes FROM the entity table

-- Execute the statement above to create an external table

SQL> CREATE TABLE scott. countries_ext

2 ORGANIZATION EXTERNAL

3 (

4 TYPE ORACLE_DATAPUMP

5 default directory dir

6 LOCATION ('countries _ ext. dmp ')

7)

8 as select * FROM scott. countries;

 

Table created.

 

SQL> select * from scott. countries_ext;

 

COUNTRY_ID COUNTRY_NAME COUNTRY_REIGION

----------------------------------------------------------------------

1 AMERICA america

2 china zhuzhou

3. JAPAN dongjing

The external entity table is successfully created.

-- Check the directory where the dir external file is stored and check whether the countries_ext.dmp file is generated.

SQL> select * from dba_directories;

 

OWNER DIRECTORY_NAME DIRECTORY_PATH

--------------------------------------------------------------------------------
 
SYS DATA_PUMP_DIR/u01/app/oracle/product/10.2.0/db_1/rdbms/log/

Sys dir/home/oracle/dir

[Oracle @ gc1 dir] $ cd/home/oracle/dir

[Oracle @ gc1 dir] $ ls

COUNTRIES_EXT_14214.log countries_ext.dmp ldr. ctl ldr. log PROD_MY_12177.log PROD_MY_14213.log PROD_MY_14214.log prod_my.data
 
2. Create an External table with datapump from an external dmp File

-- The gc2 machine creates an External table to store the file directory, which is the same as the gc1

[Oracle @ gc2 dbs] $ cd/home/oracle

[Oracle @ gc2 ~] $ Mkdir dir

[Oracle @ gc2 ~] $ Cd dir

-- Create a file directory for the database and grant operation permissions to all users.

SQL> create directory dir as '/home/oracle/dir ';

 

Directory created.

 

SQL> grant all on directory dir to public;

 

Grant succeeded.

 

For more details, please continue to read the highlights on the next page:

Oracle 10 GB external table created

Oracle_loader type External table

Oracle_datapump type External table

Oracle External table maintenance

Oracle External table, or back up a single table to an external file

1 2 Next Page