外部表简易使用

oracle外部表

注：今天在练习监控告警日志时想到使用外部表，突然觉得外部表在日常处理文件时有一定的好处，便把外部表的使用记录下来，其中语法来之《数据库管理艺术》和部分博客
简介：
外部表只能在Oracle 9i之后来使用。简单地说，外部表，是指不存在于数据库中的表。通过向Oracle提供描述外部表的元数据，我们可以把一个操作系统文件当成一个只读的数据库表，就像这些数据存储在一个普通数据库表中一样来进行访问。外部表是对数据库表的延伸。（这句话百度的）

样例一：对oracle的 告警日志建立外部表语法：

create or replace directory bdump as ‘/data/oracle/diag/rdbms/orcl/orcl/alert’；
create table alert_logs
(
text varchar2(2000)
)
organization external
(
type oracle_loader
default directory bdump
access parameters
(
records delimited by newline
fields
reject rows with all null fields
)
location
(
‘log.xml’
)
)
reject limit unlimited;
#之后就可以在数据库中直接查询alert_logs这个表

样例二： 自己建立了一个文件，每个字段以逗号作为分隔符，然后以外部表读取

create or replace directory bdump as ‘/home/oracle/wcg’；
创建自定义文件：wcg_test.txt
A,B
C,D
建立外部表：
create table alert_wcg_test
(
v_clo1 varchar2(2000),
v_clo2 varchar2(2000)
)
organization external
(
type oracle_loader
default directory bdump
access parameters
(
records delimited by newline
fields terminated by ‘,’
reject rows with all null fields
badfile directory:‘alert_wcg_test_bad.txt’
logfile directory:‘alert_wcg_test_bad.log’
)
location
(
‘wcg_test.txt’
)
)
reject limit unlimited;

语法说明：

**ORGANIZATION EXTERNAL：**关键字，必须要有。以表明定义的表为外部表
**type oracle_loader：**定义外部表的缺省方式，只能只读方式实现文本数据的装载。它还有另一种方式type ORACLE_DATAPUMP
**type ORACLE_DATAPUMP：**支持对数据的装载与卸载，数据文件必须为二进制dump文件。可以从外部表提取数据装载到内部表，也可以从内部表卸载数据作为二进制文件填充到外部表。
**default directory bdump：**缺省的目录指明了外部文件所在的路径
**access parameters：**描述如何对外部表进行访问
**records delimited by newline：**定义如何识别数据行 DELIMITED BY ‘XXX’——换行符，常用newline定义换行，并指明字符集。对于特殊的字符则需要单独定义，如特殊符号，可以使用OX’十六位值’，
例如tab(/t)的十六位是9，则DELIMITEDBY0X’09’； cr(/r)的十六位是d，那么就是DELIMITEDBY0X’0D’。
**SKIP X：**跳过X行数据，有些文件中第一行是列名，需要跳过第一行，则使用SKIP 1。
**fields terminated by ‘,’ ENCLOSED BY ‘"’：**定义字段分隔符，常用的如下：FIELDS：TERMINATED BY ','表示字段分割符为逗号。ENCLOSED BY '"'表示字段引用符，例如当前定义的双引号，在插入数据库时不会把双引号当成字段内容插入
例如一行数据格式如：“abc”,“a”“b,”“c,”。使用参数TERMINATED BY ‘,’ ENCLOSED BY '"'后，系统会读到两个字段，第一个字段的值是abc，第二个字段值是ab而不是"a""b。
**LRTRIM：**删除首尾空白字符。
**MISSING FIELD VALUES ARE NULL：**某些字段空缺值都设为NULL。
**location：**定义了外部表的位置
REJECT LIMIT UNLIMITED：在创建外部表时最后加入LIMIT子句，表示可以允许错误的发生个数。默认值为零。设定为UNLIMITED则错误不受限制

样例三：使用外部表导出数据库表数据

这样的导出方式只能是type oracle_datapump，导出的文件格式是dmp文件（如果想导出成其他文件格式可以使用UTL_FILE程序包，
在另一篇sql*loader博客中有一个简单的UTL_FILE程序包使用样例），这样导出的dmp文件。导出的dmp文件同样可以直接使用外部表的方式读取
create or replace directory wcg_test_dir as ‘/home/oracle/wcg’;

create table wcg_test_exp
organization external (
type oracle_datapump
default directory wcg_test_dir
location (‘wcg_test_exp.dmp’)
)
as select * from wcg_ldr_test;
#执行之后查询的结果就会保存到wcg_test_exp.dmp文件中。wcg_ldr_test表为数据库中现有的表，我的建表语句为：
create table WCG_LDR_TEST
(
v_id NUMBER(18),
v_name VARCHAR2(4000),
v_text VARCHAR2(4000),
v_date DATE,
v_test VARCHAR2(64)
);

样例四：使用外部表读取dmp文件

create or replace directory wcg_test_dir as ‘/home/oracle/wcg’;
create table WCG_TEST_EXP
(
v_id NUMBER(18),
v_name VARCHAR2(4000),
v_text VARCHAR2(4000),
v_date DATE,
v_test VARCHAR2(4000)
)
organization external
(
type ORACLE_DATAPUMP
default directory WCG_TEST_DIR
location (WCG_TEST_DIR:‘wcg_test_exp.dmp’)
)
reject limit 0;
#wcg_test_exp.dmp为我在样例三中导出的文件

样例五：sql*loader生成外部表创建语句：

sqlloader具有直接生成外部表创建语句的功能。当前样例将使用sqlloader 对文件wcg_ldr_test.dat生成其外部表的创建等语句
其中关键字 external_table=generate_only必须配置
建表：
create table WCG_LDR_TEST
(
v_id NUMBER(18),
v_name VARCHAR2(4000),
v_text VARCHAR2(4000),
v_date DATE,
v_test VARCHAR2(64)
);
数据文件wcg_ldr_test.dat：
312,“34”,56789,ABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcjBCDEFG834sdkajsyuebcjuhgduabcjABCDEFG834sdkajsyuebcjuhgduabcj12w1reregergfwef
12,dn,fjk8nc,iojdno1111j
34,qdw,qcerve,vbrgbj

控制文件wcg_ldr_test.ctl：
load data
DISCARDFILE test.dsc

insert
into table wcg_ldr_test
fields terminated ‘,’
(
v_id integer external,
v_name char,
v_text char,
v_test char(1000)
)

参数文件wcg_ldr_test.par：
userid=wcg/123456
control=wcg_ldr_test.ctl
log=wcg_ldr_test.log
errors=99
data=wcg_ldr_test.dat
bindsize=512000
silent=all
parallel=false
external_table=generate_only

执行：
sqlldr parfile=wcg_ldr_test.par
最后在log文件中生成如下的语句：
CREATE TABLE “SYS_SQLLDR_X_EXT_WCG_LDR_TEST”
(
“V_ID” NUMBER(18),
“V_NAME” VARCHAR2(4000),
“V_TEXT” VARCHAR2(4000),
“V_TEST” VARCHAR2(4000)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY WCG_TEST_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE ‘WCG_TEST_DIR’:‘wcg_ldr_test.bad’
DISCARDFILE ‘WCG_TEST_DIR’:‘test.dsc’
LOGFILE ‘wcg_ldr_test.log_xt’
READSIZE 1048576
FIELDS TERMINATED BY “,” LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
“V_ID” CHAR(255)
TERMINATED BY “,”,
“V_NAME” CHAR(255)
TERMINATED BY “,”,
“V_TEXT” CHAR(255)
TERMINATED BY “,”,
“V_TEST” CHAR(1000)
TERMINATED BY “,”
)
)
location
(
‘wcg_ldr_test.dat’
)
)REJECT LIMIT UNLIMITED

INSERT statements used to load internal tables:

INSERT /*+ append */ INTO WCG_LDR_TEST
(
V_ID,
V_NAME,
V_TEXT,
V_TEST
)
SELECT
“V_ID”,
“V_NAME”,
“V_TEXT”,
“V_TEST”
FROM “SYS_SQLLDR_X_EXT_WCG_LDR_TEST”

statements to cleanup objects created by previous statements:

DROP TABLE “SYS_SQLLDR_X_EXT_WCG_LDR_TEST”