Use of external tables in oracle9i

Use of external tables in Oracle Oracle9i

Description: This post describes the primary use of the oracle9i external table (External table).


What is an external table?

External table is similar to a regular table, the definition can be seen in the Oracle Concept manual, and the following points need to be noted:

The syntax created by > is similar to: "Create TABLE ... Organization EXTERNAL "
> data in the external organization of the database, is the operating system files.
> The logo of the operating system file in the database is mapped through a logical directory.
> data is read-only. (external table is equivalent to a read-only virtual table)
> You cannot run any DML operations on the above, and you cannot create an index.
> can query operations and connections. can operate in parallel.

Example:

Suppose the following two data files:

1 Description of Flat file data
Suppose the following two flat files
1.dat:

7369,smith,clerk,7902,17-dec-80,100,0,20
7499,allen,salesman,7698,20-feb-81,250,0,30
7521,ward,salesman,7698,22-feb-81,450,0,30
7566,jones,manager,7839,02-apr-81,1150,0,20


2.dat:

7654,martin,salesman,7698,28-sep-81,1250,0,30
7698,blake,manager,7839,01-may-81,1550,0,30
7934,miller,clerk,7782,23-jan-82,3500,0,10


(to have read and write access to the directory in the operating system.) ）

2 Create a logical directory and make appropriate authorization:


Sql> CREATE DIRECTORY TestDir as ' D:\TEMP\ ';
The directory has been created.

Sql> GRANT READ on DIRECTORY testdir to DEMO;
The authorization was successful.

Sql> GRANT WRITE on DIRECTORY testdir to DEMO;
The authorization was successful.


Note: After creating the logical directory, copy the flat file to the directory, and note that the file name should not be written incorrectly.

3 Creating an external table

Sql> ED
File Afiedt.buf is written

1 CREATE TABLE DEMO. EXT
2 (emp_id number (4),
3 ename VARCHAR2 (12),
4 Job Varchar2 (12),
5 mgr_id Number (4),
6 HireDate Date,
7 Salary Number (8),
8 Comm Number (8),
9 dept_id Number (2))
Ten Organization EXTERNAL
One (TYPE Oracle_loader
DEFAULT DIRECTORY TestDir
PARAMETERS ACCESS RECORDS delimited by newline
FIELDS terminated by ', ')
15* LOCATION (' 1.DAT ', ' 2.DAT ')
Sql>/

Table has been created.


4 Select operations to see if correct:


Sql> SELECT * from DEMO. EXT;

emp_id ename JOB mgr_id hiredate SALARY COMM dept_id
------ ------- ---------- ------- --------- -------- ---------- -------
7369 SMITH Clerk 7902 17-dec-80 100 0 20
7499 ALLEN salesman 7698 20-feb-81 250 0 30
7521 WARD salesman 7698 22-feb-81 450 0 30
7566 JONES MANAGER 7839 02-apr-81 1150 0 20
7654 MARTIN salesman 7698 28-sep-81 1250 0 30
7698 BLAKE MANAGER 7839 01-may-81 1550 0 30
7934 MILLER Clerk 7782 23-jan-82 3500 0 10




If you want to get information about the external table:


Sql> DESC Dba_external_tables;

Name
-----------------------------------------------------------------
OWNER
table_name
Type_owner
Type_name
Default_directory_owner
Default_directory_name
Reject_limit
Access_type
Access_parameters

Sql> SELECT Owner,table_name,default_directory_name,access_parameters
2 from
3 dba_external_tables;

OWNER table_name Default_directory_name access_parameters
------------------ ------------------------------ ------------------------------
DEMO EXT testdir RECORDS delimited by newline
FIELDS terminated by ', '


If the DBA wants to know the location of the flat file, use the following query:


sql> desc dba_external_locations;
Name
-----------------------------------------
OWNER
table_name
LOCATION
Directory_owner
Directory_name

Sql> select * from Dba_external_locations;

OWNER table_name LOCATION DIR directory_name
------------------------------------------------------------------------
DEMO EXT 1.DAT SYS TestDir
DEMO EXT 2.DAT SYS TestDir


Reference Document: Oracle9i Database Administration Guide