外部表和文件导入数据

一、创建目录，路径需要存在
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.