Shulou(Shulou.com)06/01 Report--

Oracle Externale Tables

Creating External Tables You create external tables using the CREATE TABLE statement with an ORGANIZATION EXTERNAL clause. This statement creates only metadata in the data dictionary.

Lab: Create external tables and load data;

EXAMPLE: Creating an External Table and Loading Data

In this example, the data for the external table resides in the two text files empxt1.dat and empxt2.dat.

The file empxt1.dat contains the following sample data:

Data 1:

[oracle@chen test]$ cat 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

The file empxt2.dat contains the following sample data:

Data 2:

[oracle@chen test]$ cat 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 an external table statement as follows:

[root@chen ~]# mkdir /flatfiles/{data,log,bad} -p

[root@chen ~]# chown oracle.oinstall /flatfiles/ -R

[oracle@chen test]$ cat create_external.sql

CONNECT / AS SYSDBA;

-- Set up directories and grant access to hr

CREATE OR REPLACE DIRECTORY admin_dat_dir

AS '/flatfiles/data';

CREATE OR REPLACE DIRECTORY admin_log_dir

AS '/flatfiles/log';

CREATE OR REPLACE DIRECTORY admin_bad_dir

AS '/flatfiles/bad';

GRANT READ ON DIRECTORY admin_dat_dir TO hr;

GRANT WRITE ON DIRECTORY admin_log_dir TO hr;

GRANT WRITE ON DIRECTORY admin_bad_dir TO hr;

-- hr connects. Provide the user password (hr) when prompted.

CONNECT hr

-- create the external table

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;

validation data

SQL> set linesize 1000

SQL> select * from admin_ext_employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID MANAGER_ID HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL

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

401 Jesse Cromwell HR_REP 203 17-MAY-01 7000 0 40 jcromwel

402 Abby Applegate IT_PROG 103 17-MAY-01 9000 .2 60 aapplega

403 Carol Cousins AD_VP 100 17-MAY-01 27000 .3 90 ccousins

404 John Richardson AC_ACCOUNT 205 17-MAY-01 5000 0 110 jrichard

360 Jane Janus ST_CLERK 121 17-MAY-01 3000 0 50 jjanus

361 Mark Jasper SA_REP 145 17-MAY-01 8000 .1 80 mjasper

362 Brenda Starr AD_ASST 200 17-MAY-01 5500 0 10 bstarr

363 Alex Alda AC_MGR 145 17-MAY-01 9000 .15 80 aalda

8 rows selected.

External tables do not create corresponding table segments in the database;

SQL> select * from user_segments;

no rows selected

External tables are read-only. DML operations cannot be performed.

SQL> delete admin_ext_employees where employee_id=363;

delete admin_ext_employees where employee_id=363

*

ERROR at line 1:

ORA-30657: operation not supported on external organized table

Statistics for external tables cannot be collected by analyze (they can be collected by dbms_stats)

SQL> analyze table admin_ext_employees compute statistics;

analyze table admin_ext_employees compute statistics

*

ERROR at line 1:

ORA-30657: operation not supported on external organized table

SQL> exec dbms_stats.gather_schema_stats('HR');

PL/SQL procedure successfully completed.

More detailed information: http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11706

Welcome to pay attention to my Weixin Official Accounts "IT Little Chen," learn together and grow together!!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.