解析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),
     sale_date      DATE
   )
   ORGANIZATION EXTERNAL
   (
     TYPE ORACLE_LOADER
     DEFAULT DIRECTORY sales_dat_dir
     ACCESS PARAMETERS
     (
       records delimited by newline
       badfile sales_bad_dir:'salext%a_%p.bad'
       logfile sales_log_dir:'salext%a_%p.log'
       fields terminated by ','
       missing field values are null
       (sale_id, product_name, quantity_sold, sale_date char date_format date mask "dd-mon-yyyy")
     )
     LOCATION ('sales_data1.dat', 'sales_data2.dat')
   )
   PARALLEL REJECT LIMIT UNLIMITED;
   
   复制

4. 查询外部表：

   SELECT * FROM extab_sales;
   
   复制

ORACLE_DATAPUMP驱动程序详细举例

使用ORACLE_DATAPUMP驱动程序读取和写入二进制文件

在Oracle数据库中，使用外部表进行数据导出和导入时，通常会使用ORACLE_DATAPUMP
驱动程序。以下是详细的步骤和示例：

步骤：

1. 创建目录对象：

   CREATE DIRECTORY ext_dir AS '/path/to/dump/files';
   
   复制

2. 授予权限：

   GRANT READ, WRITE ON DIRECTORY ext_dir TO username;
   
   复制

3. 创建外部表：

   CREATE TABLE ext_emp_query_results
   (
     first_name     VARCHAR2(20),
     last_name      VARCHAR2(25),
     department_name VARCHAR2(30)
   )
   ORGANIZATION EXTERNAL
   (
     TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY ext_dir
     LOCATION ('emp1.exp', 'emp2.exp', 'emp3.exp')
   )
   PARALLEL
   AS
   SELECT e.first_name, e.last_name, d.department_name
   FROM employees e, departments d
   WHERE e.department_id = d.department_id AND d.department_name IN ('Marketing', 'Purchasing');
   
   复制

示例解释：

• 目录对象：ext_dir
  指向转储文件目录。
• 外部表定义：外部表ext_emp_query_results
  的结构包括first_name
  ，last_name
  和department_name
  字段。
• 数据泵驱动程序：使用ORACLE_DATAPUMP
  驱动程序导出数据到二进制文件（如emp1.exp
  ，emp2.exp
  和emp3.exp
  ）。
• 查询数据：通过查询数据库中的现有表，将结果导出到外部表中。

另一个详细示例：

导出一个订单数据表的查询结果。

1. 创建目录对象：

   CREATE DIRECTORY orders_dir AS '/path/to/orders/dump';
   
   复制

2. 授予权限：

   GRANT READ, WRITE ON DIRECTORY orders_dir TO orders_user;
   
   复制

3. 创建外部表：

   CREATE TABLE ext_order_query_results
   (
     order_id       NUMBER(10),
     customer_name  VARCHAR2(50),
     order_amount   NUMBER(10,2)
   )
   ORGANIZATION EXTERNAL
   (
     TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY orders_dir
     LOCATION ('order1.exp', 'order2.exp', 'order3.exp')
   )
   PARALLEL
   AS
   SELECT o.order_id, c.customer_name, o.order_amount
   FROM orders o, customers c
   WHERE o.customer_id = c.customer_id AND o.order_date > SYSDATE - 30;
   
   复制

4. 查询外部表：

   SELECT * FROM ext_order_query_results;
   
   复制

通过以上详细示例，展示了如何使用ORACLE_LOADER和ORACLE_DATAPUMP驱动程序创建外部表，并进行数据的加载和查询操作。

外部表的好处

1. 直接使用外部文件中的数据或加载到另一个数据库：数据可以直接从外部文件中使用，无需先加载到内部表中。
2. 直接查询和连接外部数据：可以直接查询和连接外部数据与数据库中的表，无需先加载。
3. 复杂查询结果可以卸载到外部文件：复杂查询的结果可以直接卸载到外部文件。
4. 合并来自不同来源的生成文件以进行加载：可以将来自不同来源的生成文件合并以进行加载。

ORACLE_LOADER驱动程序

创建一个使用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;

复制

ORACLE_DATAPUMP驱动程序

创建一个使用ORACLE_DATAPUMP驱动程序的外部表示例：

CREATE TABLE ext_emp_query_results
(
  first_name     VARCHAR2(20),
  last_name      VARCHAR2(25),
  department_name VARCHAR2(30)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY ext_dir
  LOCATION ('emp1.exp', 'emp2.exp', 'emp3.exp')
)
PARALLEL
AS
SELECT e.first_name, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND d.department_name IN ('Marketing', 'Purchasing');

复制

查询和操作外部表

查询外部表

直接查询外部表中的数据：

SELECT * FROM extab_employees;

复制

将外部表与内部表连接查询

将外部表与内部表进行连接查询：

SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM departments d, extab_employees e
WHERE d.department_id = e.department_id;

复制

将外部表中的数据附加到内部表

将外部表的数据加载到内部表中：

INSERT /*+ APPEND */ INTO hr.employees
SELECT * FROM extab_employees;

复制

查看外部表的信息

可以使用数据字典视图查看外部表的相关信息：

• [DBA|ALL|USER]_EXTERNAL_TABLES：查看外部表的具体属性。
• [DBA|ALL|USER]_EXTERNAL_LOCATIONS：查看数据来源。
• [DBA|ALL|USER]_TABLES：查看所有表。
• [DBA|ALL|USER]_TAB_COLUMNS：查看表的列。
• [DBA|ALL|USER]_DIRECTORIES：查看目录对象。

DBA|ALL|USER]_EXTERNAL_TABLES

此视图显示外部表的具体属性。可以查看外部表的名称、所属模式、驱动程序类型等信息。

• DBA_EXTERNAL_TABLES：显示所有外部表的信息，适用于DBA用户。
• ALL_EXTERNAL_TABLES：显示用户有权限访问的所有外部表的信息。
• USER_EXTERNAL_TABLES：显示当前用户创建的所有外部表的信息。

-- 查看当前用户创建的外部表信息
SELECT table_name, owner, type_name, default_directory_owner, default_directory_name
FROM user_external_tables;

-- 示例输出
TABLE_NAME         | OWNER  | TYPE_NAME     | DEFAULT_DIRECTORY_OWNER | DEFAULT_DIRECTORY_NAME
-------------------------------------------------------------------------------------------
EXTAB_EMPLOYEES    | HR     | ORACLE_LOADER | HR                      | EXTAB_DAT_DIR
EXT_ORDER_RESULTS  | SALES  | ORACLE_DATAPUMP | SALES                  | ORDERS_DIR

复制

2. [DBA|ALL|USER]_EXTERNAL_LOCATIONS

此视图显示外部表的数据来源。可以查看外部表使用的数据文件及其路径信息。

• DBA_EXTERNAL_LOCATIONS：显示所有外部表的数据来源，适用于DBA用户。
• ALL_EXTERNAL_LOCATIONS：显示用户有权限访问的所有外部表的数据来源。
• USER_EXTERNAL_LOCATIONS：显示当前用户创建的所有外部表的数据来源。

-- 查看当前用户创建的外部表的数据来源
SELECT table_name, file_name
FROM user_external_locations;

-- 示例输出
TABLE_NAME         | FILE_NAME
---------------------------------------
EXTAB_EMPLOYEES    | empxt1.dat
EXTAB_EMPLOYEES    | empxt2.dat
EXT_ORDER_RESULTS  | order1.exp
EXT_ORDER_RESULTS  | order2.exp

复制

3. [DBA|ALL|USER]_TABLES

此视图显示所有表的信息，包括普通表和外部表。可以查看表的名称、所属模式、表类型等信息。

• DBA_TABLES：显示所有表的信息，适用于DBA用户。
• ALL_TABLES：显示用户有权限访问的所有表的信息。
• USER_TABLES：显示当前用户创建的所有表的信息。

-- 查看当前用户创建的所有表的信息
SELECT table_name, owner, table_type
FROM user_tables;

-- 示例输出
TABLE_NAME         | OWNER  | TABLE_TYPE
-----------------------------------------
EMPLOYEES          | HR     | TABLE
DEPARTMENTS        | HR     | TABLE
EXTAB_EMPLOYEES    | HR     | EXTERNAL TABLE
EXT_ORDER_RESULTS  | SALES  | EXTERNAL TABLE

复制

4. [DBA|ALL|USER]_TAB_COLUMNS

此视图显示表的列信息。可以查看表中各列的名称、数据类型、数据长度等信息。

• DBA_TAB_COLUMNS：显示所有表的列信息，适用于DBA用户。
• ALL_TAB_COLUMNS：显示用户有权限访问的所有表的列信息。
• USER_TAB_COLUMNS：显示当前用户创建的所有表的列信息。

-- 查看当前用户创建的所有表的列信息
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns;

-- 示例输出
TABLE_NAME         | COLUMN_NAME  | DATA_TYPE     | DATA_LENGTH
--------------------------------------------------------------
EMPLOYEES          | EMPLOYEE_ID  | NUMBER        | 22
EMPLOYEES          | FIRST_NAME   | VARCHAR2      | 20
EMPLOYEES          | LAST_NAME    | VARCHAR2      | 25
EXTAB_EMPLOYEES    | EMPLOYEE_ID  | NUMBER        | 22
EXTAB_EMPLOYEES    | FIRST_NAME   | VARCHAR2      | 20
EXTAB_EMPLOYEES    | LAST_NAME    | VARCHAR2      | 25
EXT_ORDER_RESULTS  | ORDER_ID     | NUMBER        | 22
EXT_ORDER_RESULTS  | CUSTOMER_NAME| VARCHAR2      | 50

复制

5. [DBA|ALL|USER]_DIRECTORIES

此视图显示目录对象的信息。可以查看目录对象的名称、路径及所属模式。

• DBA_DIRECTORIES：显示所有目录对象的信息，适用于DBA用户。
• ALL_DIRECTORIES：显示用户有权限访问的所有目录对象的信息。
• USER_DIRECTORIES：显示当前用户创建的所有目录对象的信息。

-- 查看当前用户创建的所有目录对象的信息
SELECT directory_name, directory_path
FROM user_directories;

-- 示例输出
DIRECTORY_NAME     | DIRECTORY_PATH
-------------------------------------------
EXTAB_DAT_DIR      | /path/to/data/files
ORDERS_DIR         | /path/to/orders/dump
SALES_DAT_DIR      | /path/to/sales/data

复制

示例一：管理HR部门的外部表

1. 创建目录对象：

   CREATE DIRECTORY extab_dat_dir AS '/home/oracle/data';
   GRANT READ, WRITE ON DIRECTORY extab_dat_dir TO hr;
   
   复制

2. 创建外部表：

   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_dat_dir:'empxt%a_%p.bad'
       logfile extab_dat_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;
   
   复制

3. 查询数据字典视图：

   -- 查看外部表的具体属性
   SELECT table_name, owner, type_name, default_directory_owner, default_directory_name
   FROM user_external_tables;
   
   -- 查看外部表的数据来源
   SELECT table_name, file_name
   FROM user_external_locations;
   
   -- 查看当前用户创建的所有表的信息
   SELECT table_name, owner, table_type
   FROM user_tables;
   
   -- 查看当前用户创建的所有表的列信息
   SELECT table_name, column_name, data_type, data_length
   FROM user_tab_columns;
   
   -- 查看当前用户创建的所有目录对象的信息
   SELECT directory_name, directory_path
   FROM user_directories;
   
   复制

示例二：管理销售部门的外部表

1. 创建目录对象：

   CREATE DIRECTORY sales_dat_dir AS '/home/oracle/sales_data';
   GRANT READ, WRITE ON DIRECTORY sales_dat_dir TO sales;
   
   复制

2. 创建外部表：

   CREATE TABLE ext_order_results
   (
     order_id       NUMBER(10),
     customer_name  VARCHAR2(50),
     order_amount   NUMBER(10,2)
   )
   ORGANIZATION EXTERNAL
   (
     TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY sales_dat_dir
     LOCATION ('order1.exp', 'order2.exp', 'order3.exp')
   )
   PARALLEL
   AS
   SELECT o.order_id, c.customer_name, o.order_amount
   FROM orders o, customers c
   WHERE o.customer_id = c.customer_id AND o.order_date > SYSDATE - 30;
   
   复制

3. 查询数据字典视图：

   -- 查看外部表的具体属性
   SELECT table_name, owner, type_name, default_directory_owner, default_directory_name
   FROM user_external_tables;
   
   -- 查看外部表的数据来源
   SELECT table_name, file_name
   FROM user_external_locations;
   
   -- 查看当前用户创建的所有表的信息
   SELECT table_name, owner, table_type
   FROM user_tables;
   
   -- 查看当前用户创建的所有表的列信息
   SELECT table_name, column_name, data_type, data_length
   FROM user_tab_columns;
   
   -- 查看当前用户创建的所有目录对象的信息
   SELECT directory_name, directory_path
   FROM user_directories;
   
   复制

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。

提供丰富的学习资源和实践经验，让你快速掌握AI技能；提供最新的行业动态和应用案例，帮助你在AI领域脱颖而出。