一. 官网对外部表的说明
Managing External Tables
http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/tables013.htm#ADMIN12896
Oracle Database 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 the database with metadata describing an external table, the database 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, for example, select, join, or sort external table data. You can also create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and no indexes can be created, on external tables.
External tables provide a framework to unload the result of an arbitrary SELECT statement into a platform-independent Oracle-proprietary format that can be used by Oracle Data Pump. External tables provide a valuable means for performing basic extraction, transformation, and loading (ETL) tasks that are common for data warehousing.
--Oracle从9i开始支持外部表,外部表的一系列增强是将其设计为ETL的增强工具,并且可以取代部分sqlldr的功能。常规的数据库应用不会常用这个的。
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. When you use external tables to unload data, the metadata is automatically created based on the datatypes in the SELECT statement.
External Table Access Drivers
An access driver is an API that interprets the external data for the database. The access driver runs inside the database, which uses the driver to read the data in the external table. The access driver and the external table layer are responsible for performing the transformations required on the data in the data file so that it matches the external table definition.
Oracle Database provides two access drivers for external tables. The default access driver is ORACLE_LOADER, which 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.
The second access driver, ORACLE_DATAPUMP, lets you unload data—that is, read data from the database and insert it into an external table, represented by one or more external files—and then reload it into an Oracle Database.
External Table Restrictions
The following are restrictions on external tables:
(1)The ANALYZE statement is not supported for gathering statistics for external tables. Use the DBMS_STATS package instead.
(2)Virtual columns are not supported
二. 外部表示例
2.1 创建目录并授权
C:/Users/Administrator.DavidDai>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 12月 28 23:39:31 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn / as sysdba;
已连接。
SQL> CREATE OR REPLACE DIRECTORY Extdump AS 'D:/Backup';
目录已创建。
SQL> GRANT READ,WRITE ON DIRECTORY Extdump TO SYSTEM;
授权成功。
SQL>
2.2. 创建数据文件
在D:/backup 目录下创建2个数据文件,内容如下:
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
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
2.3 创建外部表
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 Extdump
ACCESS PARAMETERS
(
records delimited by newline
badfile Extdump:'empxt%a_%p.bad'
logfile Extdump:'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;
启动并行(good if lots of data to load):
SQL> ALTER SESSION ENABLE PARALLEL DML;
会话已更改
查询外部表的数据:
SQL> select employee_id,first_name,last_name,job_id from admin_ext_employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
----------- -------------------- ------------------------- ----------
360 Jane Janus ST_CLERK
361 Mark Jasper SA_REP
362 Brenda Starr AD_ASST
363 Alex Alda AC_MGR
401 Jesse Cromwell HR_REP
402 Abby Applegate IT_PROG
403 Carol Cousins AD_VP
404 John Richardson AC_ACCOUNT
已选择8行。
2.4 修改外部表
参考下表:
ALTER TABLE Clause | Description | Example |
REJECT LIMIT | Changes the reject limit | ALTER TABLE admin_ext_employees REJECT LIMIT 100; |
PROJECT COLUMN | Determines how the access driver validates rows in subsequent queries: (1)PROJECT COLUMN REFERENCED: the access driver processes only the columns in the select list of the query. This setting may not provide a consistent set of rows when querying a different column list from the same external table. (2)PROJECT COLUMN ALL: the access driver processes all of the columns defined on the external table. This setting always provides a consistent set of rows when querying an external table. This is the default. | ALTER TABLE admin_ext_employees PROJECT COLUMN REFERENCED;
ALTER TABLE admin_ext_employees PROJECT COLUMN ALL; |
DEFAULT DIRECTORY | Changes the default directory specification | ALTER TABLE admin_ext_employees DEFAULT DIRECTORY admin_dat2_dir; |
ACCESS PARAMETERS | Allows access parameters to be changed without dropping and re-creating the external table metadata | ALTER TABLE admin_ext_employees ACCESS PARAMETERS (FIELDS TERMINATED BY ';'); |
LOCATION | Allows data sources to be changed without dropping and re-creating the external table metadata | ALTER TABLE admin_ext_employees LOCATION ('empxt3.txt', 'empxt4.txt'); |
PARALLEL | No difference from regular tables. Allows degree of parallelism to be changed. | No new syntax |
ADD COLUMN | No difference from regular tables. Allows a column to be added to an external table. Virtual columns are not permitted. | No new syntax |
MODIFY COLUMN | No difference from regular tables. Allows an external table column to be modified. Virtual columns are not permitted. | No new syntax |
SET UNUSED | Transparently converted into an ALTER TABLE DROP COLUMN command. Because external tables consist of metadata only in the database, the DROP COLUMN command performs equivalently to the SET UNUSED command. | No new syntax |
DROP COLUMN | No difference from regular tables. Allows an external table column to be dropped. | No new syntax |
RENAME TO | No difference from regular tables. Allows external table to be renamed. | No new syntax |
2.5 和外部表相关的权限问题
(1) External Table上可用的系统权限(System Privileges)
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
SELECT ANY TABLE
(2)External Table 上可用的对象权限(Object Privileges)
ALTER
SELECT
(3)目录权限
READ
WRITE
2.6 删除外部表
可以使用DROP TABLE 删除外部表在数据库中的对象定义(metadata)。 对真实存在的数据文件没有影响。
SQL> drop table ADMIN_EXT_EMPLOYEES;
表已删除
删除外部表和目录之外,还需要注意点,就是如果目录里有多个外部表的话,要确定把相应的关系都断掉在删除目录。 可以通过如下SQL 来查看外部表和目录之间的关系:
SQL> select * from dba_external_locations;
OWNER TABLE_NAME LOCATION
------------------------------ ------------------------------ ------------------
SH SALES_TRANSACTIONS_EXT sale1v3.dat
SYS ADMIN_EXT_EMPLOYEES empxt1.dat
SYS ADMIN_EXT_EMPLOYEES empxt2.dat
三. 使用外部表查询警告日志文件
3.1 创建目录,指定到alert log 目录
SQL> create or replace directory bdump as 'D:/app/Administrator/diag/rdbms/orcl/orcl/trace';
目录已创建。
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------------ --------------------------------------------------
SYS BDUMP D:/app/Administrator/diag/rdbms/orcl/orcl/trace
SYS EXTDUMP D:/Backup
SYS DUMP d:/Backup
SYS SUBDIR D:/app/Administrator/product/11.2.0/dbhome_1/demo/
SYS SS_OE_XMLDIR D:/app/Administrator/product/11.2.0/dbhome_1/demo/
SYS LOG_FILE_DIR D:/app/Administrator/product/11.2.0/dbhome_1/demo/
SYS DATA_FILE_DIR D:/app/Administrator/product/11.2.0/dbhome_1/demo/
SYS XMLDIR c:/ade/aime_dadvfm0254/oracle/rdbms/xml
SYS MEDIA_DIR D:/app/Administrator/product/11.2.0/dbhome_1/demo/
SYS DATA_PUMP_DIR D:/app/Administrator/admin/orcl/dpdump/
SYS ORACLE_OCM_CONFIG_DIR D:/app/Administrator/product/11.2.0/dbhome_1/ccr/s
已选择11行。
3.2 创建外部表
SQL> create table alert_log ( text varchar2(400) )
2 organization external (
3 type oracle_loader
4 default directory BDUMP
5 access parameters (
6 records delimited by newline
7 nobadfile
8 nodiscardfile
9 nologfile)
10 location('alert_orcl.log'))
11 reject limit unlimited;
表已创建。
3.3 查看alert log 表
SQL> select * from alert_log where rownum<10;
TEXT
-------------------------------------------------------------------------------
Tue Nov 02 16:54:23 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
已选择9行。
SQL> select * from alert_log where text like 'ORA-%';
TEXT
----------------------------------------------------------------------------------------
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:/APP/ADMINISTRATOR/ORADATA/ORCL/REDO01.LOG'
ORA-27041: unable to open file
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:/APP/ADMINISTRATOR/ORADATA/ORCL/REDO01.LOG'
ORA-27041: unable to open file
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'D:/APP/ADMINISTRATOR/ORADATA/ORCL/REDO02.LOG'
ORA-27041: unable to open file
ORA-00313: open failed for members of log group 2 of thread 1
TEXT
----------------------------------------------------------------------------------------
ORA-00312: online log 2 thread 1: 'D:/APP/ADMINISTRATOR/ORADATA/ORCL/REDO02.LOG'
ORA-27041: unable to open file
ORA-01155: the database is being opened
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'D:/APP/ADMINISTRATOR/ORADATA/ORCL/REDO03.LOG'
ORA-27041: unable to open file
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'D:/APP/ADMINISTRATOR/ORADATA/ORCL/REDO03.LOG'
ORA-27041: unable to open file
ORA-1507 signalled during: alter database archivelog...
已选择21行。
SQL>
------------------------------------------------------------------------------