解析Oracle外部表：数据跨平台移动的利器

使用外部表进行数据移动

外部表的架构

外部表是Oracle数据库中用于访问外部数据文件的机制。它们仅存储元数据，而不包含实际数据。外部表有两种驱动程序：

• ORACLE_LOADER驱动程序：用于读取文本文件。
• ORACLE_DATAPUMP驱动程序：用于读取和写入二进制文件。

架构图解释：

• ext_table (Metadata Only)：这是定义外部表的数据库对象，只包含元数据。
• Server process：服务器进程读取外部表数据并将其加载到PGA中处理。
• PGA：程序全局区，用于存储会话相关的信息。
• ORACLE_LOADER driver：用于加载文本文件。
• ORACLE_DATAPUMP driver：用于加载和卸载二进制文件。

ORACLE_LOADER驱动程序详细举例

1. 使用ORACLE_LOADER驱动程序读取文本文件

在Oracle数据库中，使用外部表读取文本文件的数据时，通常会使用ORACLE_LOADER驱动程序。以下是详细的步骤和示例：

步骤：

1. 创建目录对象：
   目录对象指向服务器文件系统上的目录，该目录包含外部文件。

   CREATE DIRECTORY extab_dat_dir AS '/path/to/data/files';
   CREATE DIRECTORY extab_bad_dir AS '/path/to/bad/files';
   CREATE DIRECTORY extab_log_dir AS '/path/to/log/files';
   

2. 授予权限：
   给用户授予读写目录对象的权限。

   GRANT READ, WRITE ON DIRECTORY extab_dat_dir TO username;
   GRANT READ, WRITE ON DIRECTORY extab_bad_dir TO username;
   GRANT READ, WRITE ON DIRECTORY extab_log_dir TO username;
   

3. 创建外部表：
   定义外部表的结构，并使用ORACLE_LOADER驱动程序加载数据。

   CREATE TABLE extab_employees
   (
     employee_id   NUMBER(4),
     first_name    VARCHAR2(20),
     last_name     VARCHAR2(25),
     hire_date     DATE
   )
   ORGANIZATION EXTERNAL
   (
     TYPE ORACLE_LOADER
     DEFAULT DIRECTORY extab_dat_dir
     ACCESS PARAMETERS
     (
       records delimited by newline
       badfile extab_bad_dir:'empxt%a_%p.bad'
       logfile extab_log_dir:'empxt%a_%p.log'
       fields terminated by ','
       missing field values are null
       (employee_id, first_name, last_name, hire_date char date_format date mask "dd-mon-yyyy")
     )
     LOCATION ('empxt1.dat', 'empxt2.dat')
   )
   PARALLEL REJECT LIMIT UNLIMITED;
   

4. 查询外部表：
   数据可以像普通表一样查询。

   SELECT * FROM extab_employees;
   

示例解释：

• 目录对象：extab_dat_dir指向数据文件目录，extab_bad_dir指向存放错误数据的目录，extab_log_dir指向日志文件目录。
• 外部表定义：外部表extab_employees的结构包括employee_id，first_name，last_name和hire_date字段。
• 加载文本文件：使用ORACLE_LOADER驱动程序从指定的文本文件（如empxt1.dat和empxt2.dat）中加载数据。
• 访问参数：定义数据的格式，如记录分隔符为换行符，字段分隔符为逗号，日期格式为"dd-mon-yyyy"等。

详细示例：

一个销售数据的文本文件，需要加载到Oracle数据库中。

1. 创建目录对象：

   CREATE DIRECTORY sales_dat_dir AS '/path/to/sales/data';
   CREATE DIRECTORY sales_bad_dir AS '/path/to/sales/bad';
   CREATE DIRECTORY sales_log_dir AS '/path/to/sales/log';
   

2. 授予权限：

   GRANT READ, WRITE ON DIRECTORY sales_dat_dir TO sales_user;
   GRANT READ, WRITE ON DIRECTORY sales_bad_dir TO sales_user;
   GRANT READ, WRITE ON DIRECTORY sales_log_dir TO sales_user;
   

3. 创建外部表：

   CREATE TABLE extab_sales
   (
     sale_id        NUMBER(10),
     product_name   VARCHAR2(50),
     quantity_sold  NUMBER(10)