How to manage external tables in Oracle

The external table is the table structure that is stored in the data dictionary, and the table data is stored in the OS file's tables. By using an external table, you can not only query the data of the OS files in the database, but also use the Insert method to load the OS file data into the database to implement the functionality provided by Sql*loader. After you create an external table, you can query the data for the external table, execute the connection query on the external table, or sort the data for the external table. Note that you cannot perform DML modifications on the external table, nor can you index on the external table.

1, the establishment of external tables

--Preparation work:

[ORACLE@SOLARIS10 ~] $mkdir/export/home/oracle/dat

[ORACLE@SOLARIS10 ~] $CD/export/home/oracle/dat

[Oracle@solaris10 dat] $vi empxt1.dat

360,jane,janus,st_clerk,121,17-may-2001,3000,0,50,jjanus

361,mark,jasper,sa_rep,145,17-may-2001,8000,.1,80,mjasper

362,brenda,starr,ad_asst,200,17-may-2001,5500,0,10,bstarr

363,alex,alda,ac_mgr,145,17-may-2001,9000,.15,80,aalda

[Oracle@solaris10 dat] $vi empxt2.dat

401,jesse,cromwell,hr_rep,203,17-may-2001,7000,0,40,jcromwel

402,abby,applegate,it_prog,103,17-may-2001,9000,.2,60,aapplega

403,carol,cousins,ad_vp,100,17-may-2001,27000,.3,90,ccousins

404,john,richardson,ac_account,205,17-may-2001,5000,0,110,jrichard

--Create the corresponding directory:

Sql> Conn/as SYSDBA

Connected.

sql> Create or replace directory Admin_dat_dir

2 as '/export/home/oracle/dat ';

Directory created.

sql> Create or replace directory Admin_log_dir

2 as '/export/home/oracle/log ';

Directory created.

sql> Create or replace directory Admin_bad_dir

2 as '/export/home/oracle/bad ';

Directory created.

Sql>!

[ORACLE@SOLARIS10 ~] $mkdir/export/home/oracle/{log,bad}

[ORACLE@SOLARIS10 ~] $ls

1 Documents core dat local.login shell

1.sql afiedt.buf cr_anny_db.sql hell.txt local.profile x86

Desktop Bad Cr_dict.sql LOCAL.CSHRC log

--Authorize Scott to access the established directory

Sql> grant Read on directory Admin_dat_dir to Scott;

Grant succeeded.

Sql> Grant Write on directory Admin_log_dir to Scott;

Grant succeeded.

Sql> Grant Write on directory Admin_bad_dir to Scott;

Grant succeeded.

--Create an external table

Sql> Conn Scott/tiger

Connected.

Sql>

CREATE TABLE Admin_ext_employees

(employee_id number (4),

First_Name VARCHAR2 (20),

Last_Name VARCHAR2 (25),

job_id VARCHAR2 (10),

MANAGER_ID Number (4),

Hire_date date,

Salary number (8,2),

commission_pct number (2,2),

DEPARTMENT_ID Number (4),

Email VARCHAR2 (25)

)

Organization EXTERNAL

(

TYPE Oracle_loader

DEFAULT DIRECTORY Admin_dat_dir

ACCESS PARAMETERS

(

Records delimited by newline

Badfile admin_bad_dir: ' Empxt%a_%p.bad '

LogFile admin_log_dir: ' Empxt%a_%p.log '

Fields terminated by ', '

Missing field values are null

(employee_id, first_name, last_name, job_id, manager_id,

hire_date Char date_format date Mask "dd-mon-yyyy",

Salary, commission_pct, department_id, email

)

)

LOCATION (' Empxt1.dat ', ' Empxt2.dat ')

)

PARALLEL

REJECT LIMIT Unlimited;

Table created.

Sql> select * from tab;

Tname Tabtype Clusterid

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

DEPT TABLE

EMP TABLE

BONUS TABLE

Salgrade TABLE

TEST TABLE

Admin_ext_employees TABLE

6 rows selected.

--Querying external table records

Sql> select * from Admin_ext_employees;

employee_id first_name last_name job_id manager_id hire_date SALARY commission_pct EMAIL

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

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/