一、创建目录,路径需要存在
SQL> conn / as sysdba;
Connected.
SQL> create or replace directory sqldr as '/opt/oracle/sqldr';
Directory created.
二、创建表空间
SQL> create tablespace jerry datafile '/u01/app/oracle/oradata/orcl/jerry01.dbf' size 100m autoextend on segment space management auto;
Tablespace created.
三、创建用户
SQL> create user jerry identified by jerry default tablespace jerry;
User created.
四、授权给用户
SQL> grant connect,resource,dba to jerry;
Grant succeeded.
五、授权给用户在目录上的操作权限
SQL> grant read, write on directory sqldr to jerry;
Grant succeeded.
SQL> col DIRECTORY_PATH for a40
SQL> select * from dba_directories where directory_name='SQLDR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ----------------------------------------
SYS SQLDR /opt/oracle/sqldr
六、创建外部表
SQL> conn jerry/jerry
Connected.
SQL> create table users (
2 username varchar2(30),
3 user_id number,
4 password varchar2(30)
5 )
6 organization external
7 (
8 type oracle_loader
9 default directory sqldr
10 access parameters
11 (
12 records delimited by newline characterset zhs16gbk
13 badfile 'SQLDR':'users.bad'
14 discardfile 'SQLDR':'users.dis'
15 LOGFILE 'SQLDR' : 'users.log'
16 readsize 1048576
17 fields terminated by "," optionally enclosed by '"' ldrtrim
18 missing field values are null
19 reject rows with all null fields
20 (
21 username char(30)
22 terminated by "," optionally enclosed by '"',
23 user_id char(30)
24 terminated by "," optionally enclosed by '"',
25 password char(30)
26 terminated by "," optionally enclosed by '"'
27 )
28 )
29 location('data.txt')
30 )
31 reject limit unlimited;
Table created.
SQL> select * from users;
no rows selected
七、在/opt/oracle/sqldr放置数据文件data.txt(路径即在/opt/oracle/sqldr下面)
SQL> ! vi data.txt
sys,1,abcd
scott,2,bcda
SQL> select * from users;
USERNAME USER_ID PASSWORD
------------------------------ ---------- ------------------------------
sys 1 abcd
scott 2 bcda
八、加载数据外部文件。
SQL> create table testusers(username varchar2(30),user_id varchar2(30),password varchar2(30));
Table created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
九、从外部文件直接导入数据
[oracle@oraclelinux sqldr]$ sqlldr jerry/jerry control=import.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on Tue Jul 30 10:59:43 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 2
[oracle@oraclelinux sqldr]$ sqlplus jerry/jerry
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 30 11:00:03 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 900
SQL> select * From testusers;
USERNAME USER_ID PASSWORD
------------------------------ ------------------------------ ------------------------------
sys 1 abcd
scott 2 bcda
十、查看控制文件内容
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oraclelinux sqldr]$ ls
data.txt import.ctl import.log users.dis users.log
[oracle@oraclelinux sqldr]$ vi import.ctl
load
infile '/opt/oracle/sqldr/data.txt'
badfile '/opt/oracle/sqldr/users.bad'
discardfile '/opt/oracle/sqldr/users.dis'
append
into table testusers
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
username char(30),
user_id char(30),
password char(30)
)
[oracle@oraclelinux sqldr]$ sqlplus jerry/jerry
SQL> select * from testusers;
USERNAME USER_ID PASSWORD
------------------------------ ------------------------------ ------------------------------
sys 1 abcd
scott 2 bcda
SQL>
SQL> delete from testusers;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from testusers;
no rows selected
十一、从外部表导入数据
SQL> insert /*+append*/ into testusers select * from users;
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from testusers;
USERNAME USER_ID PASSWORD
------------------------------ ---------- ------------------------------
sys 1 abcd
scott 2 bcda
十二、删除外部表
SQL> drop table users;
Table dropped.
十三、删除目录
SQL> conn / as sysdba;
QL> drop directory SQLDR;
Directory dropped.
SQL> conn / as sysdba;
Connected.
SQL> create or replace directory sqldr as '/opt/oracle/sqldr';
Directory created.
二、创建表空间
SQL> create tablespace jerry datafile '/u01/app/oracle/oradata/orcl/jerry01.dbf' size 100m autoextend on segment space management auto;
Tablespace created.
三、创建用户
SQL> create user jerry identified by jerry default tablespace jerry;
User created.
四、授权给用户
SQL> grant connect,resource,dba to jerry;
Grant succeeded.
五、授权给用户在目录上的操作权限
SQL> grant read, write on directory sqldr to jerry;
Grant succeeded.
SQL> col DIRECTORY_PATH for a40
SQL> select * from dba_directories where directory_name='SQLDR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ----------------------------------------
SYS SQLDR /opt/oracle/sqldr
六、创建外部表
SQL> conn jerry/jerry
Connected.
SQL> create table users (
2 username varchar2(30),
3 user_id number,
4 password varchar2(30)
5 )
6 organization external
7 (
8 type oracle_loader
9 default directory sqldr
10 access parameters
11 (
12 records delimited by newline characterset zhs16gbk
13 badfile 'SQLDR':'users.bad'
14 discardfile 'SQLDR':'users.dis'
15 LOGFILE 'SQLDR' : 'users.log'
16 readsize 1048576
17 fields terminated by "," optionally enclosed by '"' ldrtrim
18 missing field values are null
19 reject rows with all null fields
20 (
21 username char(30)
22 terminated by "," optionally enclosed by '"',
23 user_id char(30)
24 terminated by "," optionally enclosed by '"',
25 password char(30)
26 terminated by "," optionally enclosed by '"'
27 )
28 )
29 location('data.txt')
30 )
31 reject limit unlimited;
Table created.
SQL> select * from users;
no rows selected
七、在/opt/oracle/sqldr放置数据文件data.txt(路径即在/opt/oracle/sqldr下面)
SQL> ! vi data.txt
sys,1,abcd
scott,2,bcda
SQL> select * from users;
USERNAME USER_ID PASSWORD
------------------------------ ---------- ------------------------------
sys 1 abcd
scott 2 bcda
八、加载数据外部文件。
SQL> create table testusers(username varchar2(30),user_id varchar2(30),password varchar2(30));
Table created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
九、从外部文件直接导入数据
[oracle@oraclelinux sqldr]$ sqlldr jerry/jerry control=import.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on Tue Jul 30 10:59:43 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 2
[oracle@oraclelinux sqldr]$ sqlplus jerry/jerry
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 30 11:00:03 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 900
SQL> select * From testusers;
USERNAME USER_ID PASSWORD
------------------------------ ------------------------------ ------------------------------
sys 1 abcd
scott 2 bcda
十、查看控制文件内容
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oraclelinux sqldr]$ ls
data.txt import.ctl import.log users.dis users.log
[oracle@oraclelinux sqldr]$ vi import.ctl
load
infile '/opt/oracle/sqldr/data.txt'
badfile '/opt/oracle/sqldr/users.bad'
discardfile '/opt/oracle/sqldr/users.dis'
append
into table testusers
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
username char(30),
user_id char(30),
password char(30)
)
[oracle@oraclelinux sqldr]$ sqlplus jerry/jerry
SQL> select * from testusers;
USERNAME USER_ID PASSWORD
------------------------------ ------------------------------ ------------------------------
sys 1 abcd
scott 2 bcda
SQL>
SQL> delete from testusers;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from testusers;
no rows selected
十一、从外部表导入数据
SQL> insert /*+append*/ into testusers select * from users;
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from testusers;
USERNAME USER_ID PASSWORD
------------------------------ ---------- ------------------------------
sys 1 abcd
scott 2 bcda
十二、删除外部表
SQL> drop table users;
Table dropped.
十三、删除目录
SQL> conn / as sysdba;
QL> drop directory SQLDR;
Directory dropped.