External Tables
You can access data in external sources as if it were in a table in the database. Oracle allows you read-only access to data in external tables. External tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided. By providing Oracle with metadata describing an external table, Oracle is able to expose the data in the external table as if it were data residing in a regular database table. The external data can be queried directly and in parallel using SQL.
You can SELECT JOIN or SORT external table data. You can also create views and synonyms for external tables. However, no DML operations are possible, and no indexes can be created on external tables.
The means of defining the metadata for external tables is through the CREATE TABLE … ORGANIZATION EXTERNAL statement. This external table definition can be thought of as a view that allows running any SQL query against external data without requiring that the external data first be loaded into the database. An access driver is the actual mechanism used to read the external data in the table.
Oracle provides an access driver for external tables. It allows the reading of data from external files using the Oracle loader technology. The ORACLE_LOADER access driver provides data mapping capabilities which are a subset of the control file syntax of SQL*Loader utility.
Oracle’s external tables feature provides a valuable means for performing basic extraction, transformation and transportation tasks that are common for data warehousing.
Creating External Tables
You create external table using the ORGANIZATION EXTERNAL clause of the CREATE TABLE statement. You are not in fact creating a table; that is, an external table does not have any extents associated with it. Rather, you are creating metadata in the data dictionary that enables you to access external data.
The file empxt1.dat contains the following sample data:
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:
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
The following SQL statements create an external table in the hr schema named
admin_ext_employees and load its data into the hr.employees table.
CONNECT / AS SYSDBA; -- Set up directories and grant
access to hr CREATE OR REPLACE DIRECTORY
admin_dat_dir AS
'/net/dlsun301/private6/examples/submitted/ADMIN/flatfiles/data'; CREATE OR REPLACE DIRECTORY
admin_log_dir AS
'/net/dlsun301/private6/examples/submitted/ADMIN/flatfiles/log'; CREATE OR REPLACE DIRECTORY
admin_bad_dir AS
'/net/dlsun301/private6/examples/submitted/ADMIN/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;
Connect as HR
CONNECT hr/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; -- enable parallel for loading
(good if lots of data to load) ALTER SESSION ENABLE PARALLEL
DML; -- load the data in hr employees
table INSERT INTO employees
(employee_id, first_name, last_name, job_id, manager_id, hire_date, salary,
commission_pct, department_id, email) SELECT * FROM
admin_ext_employees;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/18447/viewspace-176579/ ，如需转载，请注明出处，否则将追究法律责任。
