Implementation method and implementation of Oracle external table

An external table implementation process based on Oracle_datapump:

First, create external tables and generate DMP files

1, create directory, you need to have create any directory permissions:

CREATE DIRECTORY admin as '/oracle/admin ';

2, create the external table:

sql> CREATE TABLE Emp_xt

Organization EXTERNAL

(

TYPE Oracle_datapump

DEFAULT DIRECTORY Admin

LOCATION (' emp_xt.dmp ')

)

As SELECT * from EMP;

Table created.

3, verify the correctness of the table:

Sql> SELECT * from emp minus select * from Emp_xt;

No rows selected

Create an external table using the resulting pump file

1. Copy the resulting DMP file to another host

2, create directory, you need to have create any directory permissions:

Sql> Create directory Ext_dir as '/oracle/ext_dir ';

3, create the external table:

sql> CREATE TABLE emp_xt2

(

EMPNO Number (4),

Ename VARCHAR2 (10),

JOB VARCHAR2 (9),

MGR Number (4),

HireDate DATE,

SAL number (7,2),

COMM number (7,2),

DEPTNO Number (2)

)

Organization EXTERNAL

(

TYPE Oracle_datapump

DEFAULT DIRECTORY Ext_dir

LOCATION (' emp_xt.dmp ')

);