Oracle数据加载与卸载全解析

### Oracle数据加载与卸载全解析 在数据处理的过程中，数据的加载与卸载是非常重要的环节。本文将详细介绍Oracle数据库中数据加载与卸载的相关技术，包括外部表的使用、数据泵卸载、SQLLDR工具的使用等内容。 #### 外部表的使用与预处理 外部表是Oracle 9i及以后版本提供的一个功能，它在很大程度上可以替代SQLLDR。使用外部表时，可以利用SQLLDR将过去的控制文件进行转换，还能通过坏文件检测和处理错误，同时也涉及一些多用户相关的问题。此外，在从外部表进行选择操作时，可以使用预处理技术来执行操作系统命令。 下面通过一个示例展示如何从外部表进行选择操作： ```sql SQL> select * from csv2; COL1 COL2 -------------------- -------------------- emergency data load now more data must load data ``` 为了确认每行中多余的 `^M` 字符是否已被移除，可以检查 `COL2` 的长度： ```sql SQL> select length(col2) from csv2; LENGTH(COL2) ------------ 8 14 ``` 如果隐藏的 `^M` 字符未被移除，`COL2` 的长度至少会比列中的字符数据长1个字节。 预处理指令 `PREPROCESSOR` 具有强大的功能，下面是几个具体示例： 1. **操作对象为shell脚本**：没有数据文件，操作系统命令的输出就是从外部表选择时返回的数据。 2. **操作压缩文件**：预处理脚本对压缩文件进行操作，实现内容的实时解压缩，并使用 `find`、`cut` 和 `zgrep` 等命令对列和/或行进行额外过滤。 3. **操作CSV文件**：使用 `sed` 脚本在显示数据之前移除不需要的字符，这在DOS和UNIX/Linux平台之间传输文件时是常见需求。 #### 数据泵卸载 Oracle可以以一种专有的二进制格式（即数据泵格式）从表中提取数据，这种格式与Oracle提供的 `EXPDP` 和 `IMPDP` 工具用于在数据库之间移动数据的格式相同。Oracle使用外部表和 `CREATE TABLE` 语句来执行数据卸载功能。 数据泵卸载非常简单，类似于 `CREATE TABLE AS SELECT` 语句。具体操作步骤如下： 1. 创建一个 `DIRECTORY` 对象： ```sql $ sqlplus eoda/foo@PDB1 SQL> create or replace directory tmp as '/tmp'; Directory created. ``` 2. 使用简单的 `SELECT` 语句将数据卸载到该目录： ```sql SQL> create table all_objects_unload organization external ( type oracle_datapump default directory TMP location( 'allobjects.dat' ) ) as select * from all_objects; Table created. ``` 这里选择 `ALL_OBJECTS` 视图是因为它是一个复杂的视图，包含许多连接和谓词，这表明可以使用数据泵卸载技术从数据库中提取任意数据。 3. 将 `allobjects.dat` 复制到另一台服务器，并提取DDL以在该服务器上重新创建表： ```sql SQL> set serverout on long 100000 pages 100 SQL> select dbms_metadata.get_ddl( 'TABLE', 'ALL_OBJECTS_UNLOAD' ) from dual; ``` 4. 在另一个数据库中加载提取的数据： ```sql SQL> insert /*+ append */ into some_table select * from all_objects_unload; ``` 需要注意的是，可以创建压缩和加密的转储文件，但这些功能需要Oracle企业版以及高级压缩选项（用于压缩）和高级安全选项（用于加密）。 #### SQLLDR工具 SQLLDR是Oracle的高速批量数据加载器，可将各种平面文件格式的数据快速加载到Oracle数据库中。它有两种操作模式： - **常规路径**：SQLLDR会代表用户使用SQL插入语句加载数据。 - **直接路径**：SQLLDR在这种模式下不使用SQL，而是直接格式化数据库块。 下面是一个使用SQLLDR的简单示例： 1. 创建一个简单的控制文件 `demo1.ctl`： ```plaintext (1) LOAD DATA (2) INFILE * (3) INTO TABLE DEPT (4) FIELDS TERMINATED BY ',' (5) (DEPTNO, DNAME, LOC) (6) BEGINDATA (7) 10,Sales,Virginia (8) 20,Accounting,Virginia (9) 30,Consulting,Virginia (10) 40,Finance,Virginia ``` 各部分解释如下： |行号|命令|解释| |----|----|----| |(1)|LOAD DATA|告诉SQLLDR要执行数据加载操作| |(2)|INFILE *|表示要加载的数据包含在控制文件本身中| |(3)|INTO TABLE DEPT|指定要将数据加载到 `DEPT` 表中| |(4)|FIELDS TERMINATED BY ','|表示数据以逗号分隔| |(5)|(DEPTNO, DNAME, LOC)|指定要加载的列及其在输入数据中的顺序和数据类型| |(6)|BEGINDATA|表示输入数据描述结束，接下来的行是要加载到 `DE