数据库|mysql教程
oracle_loader,Oracle 外部表,oracl
数据库-mysql教程
sdk 源码,ubuntu网卡删了重配,tomcat安装包形式,爬虫银保监会,直播开源项目php 资讯,祁东SEOlzw
1、创建目录（create any directory）： SQLgt; create user Oracle identified by oracle; 用户已创建。 SQLgt; grant dba to
互助网站asp源码,ubuntu系统增加用户,开机启动三个tomcat,爬虫歪歪漫画,家庭账单管理系统php,东台seo软文lzw
app html源码下载,用pxe安装Ubuntu,怎么给tomcat窗口命名,白色多角爬虫,php改名由来,品牌推广方案思维导图seo公司lzw
1、创建目录（create any directory）：
SQL> create user Oracle identified by oracle;
用户已创建。
SQL> grant dba to oracle;
授权成功。
SQL> grant create any directory to oracle;
授权成功。
SQL> conn oracle/oracle
已连接。
2、创建外部表：
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
——————– —————————————- ——————————————-
—
SYS ADMIN_DIR C:\ADE\aime_vista_ship\oracle/md/admin
SYS DATA_PUMP_DIR d:\oracle\product\10.2.0\admin\orcl\dpdump\
SYS BDUMP D:\oracle\product\10.2.0\admin\orcl\bdump
SYS WORK_DIR C:\ADE\aime_vista_ship\oracle/work
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 )
11 location(‘alert_orcl.log’)
12 )
13 reject limit unlimited
14 /
表已创建。
SQL> select * from alert_log where rownum<10;
TEXT
——————————————————————
Dump file d:\oracle\product\10.2.0\admin\orcl\bdump\alert_orcl.log
Sun May 16 11:25:15
ORACLE V10.2.0.3.0 – Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows NT Version V6.0
CPU : 2 – type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:372M/1021M
Sun May 16 11:25:15
已选择9行。
3、外部表acess paramter获得方式
sqlldr oracle/oracle control=user.ctl external_table=generate_only
eg:
1、user.ctl(语法 可以使用EM DBCONSOLE生成) :
load
infile ‘D:\oracle_file\data.txt’
badfile ‘D:\oracle_file\data.bad’
discardfile ‘D:\oracle_file\data.dis’
errors=50
append
into table user_data
fields terminated by ‘,’ optionally enclosed by ‘”‘
trailing nullcols
(user_name varchar2(20),
user_id number)
2、sqlldr oracle/oracle control=user.ctl external_table=generate_only log=user_data.log
你会在日志中发现：
文件需要 CREATE DIRECTORY 语句
————————————————————————
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS ‘D:\oracle_file\’
用于外部表的 CREATE TABLE 语句:
————————————————————————
CREATE TABLE “SYS_SQLLDR_X_EXT_USER_DATA”
(
“USER_NAME” VARCHAR2(20),
“USER_ID” NUMBER
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE ‘SYS_SQLLDR_XT_TMPDIR_00000′:’data.bad’
DISCARDFILE ‘SYS_SQLLDR_XT_TMPDIR_00000′:’data.dis’
LOGFILE ‘user_data.log_xt’
READSIZE 1048576
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
“USER_NAME” CHAR(255)
TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘,
“USER_ID” CHAR(255)
TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
)
)
location
(
‘data.txt’
)
)REJECT LIMIT UNLIMITED
，
