外部表
一、向数据库加载CSV文件
使用外部表和SQL,可以向数据库加载小型或非常大的CSV文件。
下面是使用外部表访问OS CSV文件的步骤。
1. 创建指向OS CSV文件的数据库目录对象(directory)
2. 为创建外部表的用户分配目录对象的读写权限。
3. 运行CREATE TABLE …ORGANIZATION EXTERNAL语句
4. 使用sqlplus或plsql访问CSV文件的内容
二、操作示例
例子的文件名称为test0223.csv,位于/home/oracle目录中,文件内容为:
1. 创建目录对象
2. 授权,例子使用DBA用户,无需授权
如果是其他用户则需要
grant read,write on directory exa_dir to userxxx;
3. 创建外部表
Create table external_et(
ext_id number,
test_date date,
remark varchar2(32)
)
ORGANIZATION EXTERNAL(
type oracle_loader
default DIRECTORY exa_dir
access PARAMETERS(
RECORDS delimited by newline
fields terminated by '|'
missing field values are null
(ext_id,
test_date CHAR date_format DATE mask "yyyymmdd",
remark
)
)
LOCATION('test0223.csv')
)
REJECT limit UNLIMITED;
4. 查询表
5. 可查看外部表元数据
SELECT t.OWNER,t.table_name,t.default_directory_name,t.access_parameters FROM dba_external_tables t;
6. 使用外部表查看文本文件
1.例如使用外部表查看告警文件
SELECT value FROM v$diag_info where name='Diag Trace';
输出结果:
/u01/app/oracle/.../trace
2.创建目录对象
create or replace DIRECTORY t_loc as '/u01/app/oracle/.../trace';
3.创建外部表
create table alert_log_file1(
alert_text varchar2(4000))
ORGANIZATION EXTERNAL
( type oracle_loader
default DIRECTORY t_loc
access PARAMETERS(
RECORDS delimited by newline
nobadfile
nologfile
nodiscardfile
fields terminated by '#$~=ui$X'
missing field values are null
(alert_text)
)
LOCATION('alert_tradedb1.log')
)REJECT limit UNLIMITED;
7. 使用外部表卸载、加载数据(例子中目录对象 dp不再描述)
1. 原表 inv(
ID NUMBER,
DESC VARCHAR2(32)
)包含数据。
2. Create table inv_et
ORGANIZATION EXTERNAL
( type oracle_datapump
default DIRECTORY dp
LOCATION('inv.dmp')
As SELECT * FROM inv;
上面命令创建了两个事物:
一、根据INV表的结构和数据,创建了外部表INV_ET
二、生成跨平台的数据泵文件inv.dmp
这样可以将inv.dmp复制到其他数据库服务器,并根据该文件创建外部表
3. Create table inv_dw
(
ID NUMBER,
DESC VARCHAR2(32)
)
ORGANIZATION EXTERNAL
( type oracle_datapump
default DIRECTORY dp
LOCATION('inv.dmp');
这样就可以通过 SELECT * FROM inv_dw;访问数据
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30109892/viewspace-1993127/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30109892/viewspace-1993127/