SQL开发知识：Oracle数据加载和卸载的方法

Oracle 数据表跟文本或者文件格式进行交互；即将指定文件内容导入对应的 Oracle 数据表中；或者从 Oracle 数据表导出。 其他数据库中的表跟Oracle数据库进行交互。

若是少量数据；可选择的解决方案有很多。常用的用 Pl/SQL developer工具，或者手动转换为 INSERT 语句，或者通过API。但数据量大；用上面的方法效率太烂了。本文来说说 Oracle 数据的加载和卸载。

Oracle中的DBLINK Oracle加载数据-外部表 Oracle加载数据-sqlldr工具 Oracle卸载数据-sqludr

一. Oracle 中的 DBLINK

在日常工作中；会遇到不同的数据库进行数据对接；每个数据库都有着功能；像Oracle有 DBLINK ; PostgreSQL有外部表。

1.1 Oracle DBlink 语法

CREATE [PUBLIC] DATABASE LINK link
CONNECT TO username
IDENTIFIED BY password
USING connectstring

1.2 Oracle To Mysql

在oracle配置mysql数据库的dblink

二.Oracle加载数据-外部表

ORACLE外部表用来存取数据库以外的文本文件（Text File）或ORACLE专属格式文件。因此，建立外部表时不会产生段、区、数据块等存储结构，只有与表相关的定义放在数据字典中。外部表，顾名思义，存储在数据库外面的表。当存取时才能从ORACLE专属格式文件中取得数据，外部表仅供查询，不能对外部表的内容进行修改（INSERT、UPDATE、DELETE操作）。不能对外部表建立索引。

2.1 创建外部表需要的目录

# 创建外部表需要的目录
SQL create or replace directory DUMP_DIR as /data/ora_ext_lottu
Directory created.
# 给用户授予指定目录的操作权限
SQL GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO lottu;

Grant succeeded.

2.2 外部表源文件lottu.txt

10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

2.3 创建外部表

drop table dept_external purge;

CREATE TABLE dept_external (
deptno NUMBER(6),
dname VARCHAR2(20),
loc VARCHAR2(25)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY DUMP_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
BADFILE lottu.bad
LOGFILE lottu.log
FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY
(
deptno INTEGER EXTERNAL(6),
dname CHAR(20),
loc CHAR(25)
)
)
LOCATION ( lottu.txt )
)
REJECT LIMIT UNLIMITED;

查看数据

SQL select * from dept_external;

DEPTNO DNAME LOC
- -
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

三. Oracle加载数据-sqlldr工具

3.1 准备实验对象

创建文件lottu.txt；和表tbl_load_01。

[oracle@oracle235 ~]$ seq 1000|awk -vOFS= , {print $1, lottu ,systime()-$1} lottu.txt
[oracle@oracle235 ~]$ sqlplus lottu/li0924
SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 13 22:58:34 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL create table tbl_load_01 (id number,name varchar2(10),accountid number);
Table created.

3.2 创建控制文件lottu.ctl

load data
characterset utf8
infile /home/oracle/lottu.txt
truncate into table tbl_load_01
fields terminated by ,
trailing nullcols
optionally enclosed by TRAILING NULLCOLS
(
id ,
name,
accountid
)

3.3 执行sqlldr

[oracle@oracle235 ~]$ sqlldr lottu/ li0924 control=/home/oracle/lottu.ctl log=/home/oracle/lottu.log bad=/home/oracle/lottu.bad
SQL*Loader: Release 11.2.0.4.0 Production on Mon Aug 13 23:10:12 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached logical record count 64
Commit point reached logical record count 128
Commit point reached logical record count 192
Commit point reached logical record count 256
Commit point reached logical record count 320
Commit point reached logical record count 384
Commit point reached logical record count 448
Commit point reached logical record count 512
Commit point reached logical record count 576
Commit point reached logical record count 640
Commit point reached logical record count 704
Commit point reached logical record count 768
Commit point reached logical record count 832
Commit point reached logical record count 896
Commit point reached logical record count 960
Commit point reached logical record count 1000

四.Oracle卸载数据-sqludr

sqludr是将Oracle数据表导出到文本中；是牛人楼方鑫开发的。并非Oracle自带工具；需要下载安装才能使用。

4.1 sqludr安装

[oracle@oracle235 ~]$ unzip sqluldr2linux64.zip
Archive: sqluldr2linux64.zip
inflating: sqluldr2linux64.bin
[oracle@oracle235 ~]$ mv sqluldr2linux64.bin $ORACLE_HOME/bin/sqludr

4.2 查看sqludr帮助

[oracle@oracle235 ~]$ sqludr -
SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1
(@) Copyright Lou Fangxin (AnySQL.net) 2004 2010, all rights reserved.
License: Free for non-commercial useage, else 100 USD per server.
Usage: SQLULDR2 keyword=value [,keyword=value, ]
Valid Keywords:
user = username/password@tnsname
sql = SQL file name
query = select statement
field = separator string between fields
record = separator string between records
rows = print progress for every given rows (default, 1000000)
file = output file name(default: uldrdata.txt)
log = log file name, prefix with + to append mode
fast = auto tuning the session level parameters(YES)
text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
charset = character set name of the target database.
ncharset= national character set name of the target database.
parfile = read command option from parameter file
for field and record, you can use 0x to specify hex character code,
\r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, =0x22 =0x27

4.3 执行sqludr

[oracle@oracle235 ~]$ sqludr lottu/li0924 query= tbl_load_01 file=lottu01.txt field= ,
0 rows exported at 2018-08-13 23:47:55, size 0 MB.
1000 rows exported at 2018-08-13 23:47:55, size 0 MB.
output file lottu01.txt closed at 1000 rows, size 0 MB.

以上所述是小编给大家介绍的SQL开发知识：Oracle数据加载和卸载的方法，大家如有疑问可以留言，或者联系站长。感谢亲们支持！！！

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 SQL开发知识：Oracle数据加载和卸载的方法