Oracle External Tables

1. Introduction to External Tables

The external table is used after oracle9i. An external table is a table in which the definition of a type of table exists in the database and the data does not exist in the database.

External tables can be select,join,sort during a database operation or you can create views and synonyms on external tables. However, you cannot perform DML operations on external tables and create indexes.

The external table provides two access drivers. One kind oracle_loader another kind of oracle_datapump. The default driver is Oracle_loader.

The Oracle_loader driver reads data from an external file. Oracle_loader the syntax for creating external tables and Sql*loader utility have the same similarities.

Oracle_datapump first unload the data from the external table and then reload the table through the external table. Detailed below section analysis.

Additionally, the statistics collection for external tables supports dbms_stats packages but does not support analyze.

and the external table does not support virtual columns.

2. Create an external table

[email protected] mydir]$ cat Samp1.txt

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

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

sql> Create or replace directory Mydir as '/home/oracle/mydir ';

Directory created.

Sql> Grant Read,write on directory Mydir to Scott;

Grant succeeded.

Sql> Conn Scott/tiger

Connected.

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 Mydir

ACCESS PARAMETERS

(

Records delimited by newline

Badfile mydir: ' Badxt%a_%p.bad '

LogFile Mydir: ' Logxt%a_%p.log '

Fields terminated by ', '

Missing field values is 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 (' Samp1.txt ')

)

PARALLEL REJECT LIMIT UNLIMITED;

PARALLEL--Specifies the degree of parallelism when querying

REJECT LIMIT UNLIMITED--Specify the number of external table query errors

Perform a successful look at the resulting log.

[Email protected] mydir]$ Ls-al

Total 20

Drwxr-xr-x 2 Oracle Oinstall 4096 Nov 20 16:12.

DRWX------5 Oracle Oinstall 4096 Nov 20 15:53.

-rw-r--r--1 Oracle Oinstall 1413 Nov 16:12 Logxt000_13688.log

-rw-r--r--1 Oracle Oinstall 1555 Nov 16:12 Logxt000_16408.log

-rw-r--r--1 Oracle Oinstall 480 Nov 15:53 samp1.txt

[email protected] mydir]$ cat Logxt000_13688.log

LOG file opened at 11/20/15 16:12:03

Field Definitions for Table Admin_ext_employees

Record format delimited by NEWLINE

Data in file have same endianness as the platform

Rows with all null fields is accepted

Fields in Data Source:

employee_id CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

First_Name CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

Last_Name CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

job_id CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

manager_id CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

Hire_date CHAR (80)

Date datatype date, date mask dd-mon-yyyy

Terminated by ","

Trim whitespace same as SQL Loader

SALARY CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

commission_pct CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

department_id CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

EMAIL CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

[email protected] mydir]$ cat Logxt000_16408.log

LOG file opened at 11/20/15 16:12:03

Field Definitions for Table Admin_ext_employees

Record format delimited by NEWLINE

Data in file have same endianness as the platform

Rows with all null fields is accepted

Fields in Data Source:

employee_id CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

First_Name CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

Last_Name CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

job_id CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

manager_id CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

Hire_date CHAR (80)

Date datatype date, date mask dd-mon-yyyy

Terminated by ","

Trim whitespace same as SQL Loader

SALARY CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

commission_pct CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

department_id CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

EMAIL CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

Date Cache Statistics for table admin_ext_employees

Max size:1000

Entries:1

Hits:7

misses:0

Logxt000_16408.log log more statistics than logxt000_13688.log tail.

View external Tables

Sql> select Employee_id,first_name from Admin_ext_employees;

employee_id first_name

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

Jane

361 Mark

362 Brenda

363 Alex

401 Jesse

402 Abby

403 Carol

404 John

3. External Table Modification

The ability to modify external table access parameters for use with ALTER table under face sentences

Reject Limit:alter TABLE admin_ext_employees reject limit N;

Project Column:alter TABLE admin_ext_employees Project column referenced;

ALTER TABLE admin_ext_employees PROJECT COLUMN all;

Default Dectory:alter TABLE admin_ext_employees default DIRECTORY admin_dat2_dir;

To view modified properties

Sql> select Owner,table_name,reject_limit,default_directory_name,property from Dba_external_tables;

OWNER table_name reject_lim default_directory_name Property

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

SCOTT admin_ext_employees UNLIMITED MYDIR All

4. Preprocessing external Tables

sql> Create or replace directory Mydir as '/home/oracle/mydir ';

Directory created.

Sql> Grant Read,write,execute on directory Mydir to Scott;

Grant succeeded.

Create a file under/home/oracle/mydir

[email protected] mydir]$ cat uncompress

/BIN/GZIP-CD $

[Email protected] mydir]$ chmod +x uncompress

Sql> Conn Scott/tiger

Connected.

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 Mydir

ACCESS PARAMETERS

(

Records delimited by newline

Preprocessor Exec_file_dir: ' uncompress '

badfile mydir: ' Badxt%a_%p.bad '

LogFile Mydir: ' Logxt%a_%p.log '

Fields terminated by ', '

Missing field values is 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 (' samp1.txt.gz ')

)

PARALLEL

REJECT LIMIT UNLIMITED;

5. Deletion of external tables using the DROP TABLE statement This statement simply deletes the media in the database and has no effect on the actual data.

sql> drop table admin_ext_employees;

Table dropped.

6. System permissions and objects for external tables.

System permissions for external tables

CREATE any TABLE

ALTER any TABLE

DROP any TABLE

SELECT any TABLE

object permissions for external tables

Alter

SELECT

This article is from the "unity of Knowledge" blog, please be sure to keep this source http://dovelauren.blog.51cto.com/9876026/1715857

Oracle External Tables