Oracle 外部表

最新推荐文章于 2024-10-30 10:59:51 发布

iteye_3158

最新推荐文章于 2024-10-30 10:59:51 发布

阅读量126

点赞数

文章标签：数据库

一.官网对外部表的说明

Managing ExternalTables

http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/tables013.htm#ADMIN12896

Oracle Database allows you read-only access to data inexternal 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 isanAPI 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 providestwo access driversfor 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 capabilitieswhich 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

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_idNUMBER(4),

first_nameVARCHAR2(20),

last_nameVARCHAR2(25),

job_idVARCHAR2(10),

manager_idNUMBER(4),

hire_dateDATE,

salaryNUMBER(8,2),

commission_pctNUMBER(2,2),

department_idNUMBER(4),

emailVARCHAR2(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_NAMELAST_NAMEJOB_ID

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

360 JaneJanusST_CLERK

361 MarkJasperSA_REP

362 BrendaStarrAD_ASST

363 AlexAldaAC_MGR

401 JesseCromwellHR_REP

402 AbbyApplegateIT_PROG

403 CarolCousinsAD_VP

404 JohnRichardsonAC_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;

OWNERTABLE_NAMELOCATION

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

SHSALES_TRANSACTIONS_EXTsale1v3.dat

SYSADMIN_EXT_EMPLOYEESempxt1.dat

SYSADMIN_EXT_EMPLOYEESempxt2.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;

OWNERDIRECTORY_NAMEDIRECTORY_PATH

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

SYSBDUMPD:/app/Administrator/diag/rdbms/orcl/orcl/trace

SYSEXTDUMPD:/Backup

SYSDUMPd:/Backup

SYSSUBDIRD:/app/Administrator/product/11.2.0/dbhome_1/demo/

SYSSS_OE_XMLDIRD:/app/Administrator/product/11.2.0/dbhome_1/demo/

SYSLOG_FILE_DIRD:/app/Administrator/product/11.2.0/dbhome_1/demo/

SYSDATA_FILE_DIRD:/app/Administrator/product/11.2.0/dbhome_1/demo/

SYSXMLDIRc:/ade/aime_dadvfm0254/oracle/rdbms/xml

SYSMEDIA_DIRD:/app/Administrator/product/11.2.0/dbhome_1/demo/

SYSDATA_PUMP_DIRD:/app/Administrator/admin/orcl/dpdump/

SYSORACLE_OCM_CONFIG_DIRD:/app/Administrator/product/11.2.0/dbhome_1/ccr/s

已选择11行。

3.2创建外部表

SQL> create table alert_log ( text varchar2(400) )

2organization external (

3type oracle_loader

4default directory BDUMP

5access parameters (

6records delimited by newline

7nobadfile

8nodiscardfile

9nologfile)

10location('alert_orcl.log'))

11reject 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'