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

头像
逼格高的汤圆
     北京
    阅读 10 分钟

    使用外部表进行数据移动

    外部表的架构

    外部表是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领域脱颖而出。

    本文由mdnice多平台发布

    数据库
    逼格高的汤圆
    7 声望2 粉丝
    « 上一篇
    Oracle数据库跨平台迁移：RMAN和Data Pump的最佳实践
    下一篇 »
    Oracle Enterprise Manager Cloud Control框架及其最佳实践

    引用和评论

    推荐阅读
    头像
    从零开始掌握 Kubernetes Namespace：高效管理集群资源

    逼格高的汤圆阅读 381

    头像
    Mybatis-基础使用

    半夏之沫赞 1阅读 2.5k

    头像
    直击青藏高原数据匮乏难题！浙江大学团队提出GeoAI新模型，解释青藏高原地表热流分布

    超神经HyperAI赞 1阅读 671

    头像
    Mybatis源码-加载映射文件与动态代理

    半夏之沫赞 1阅读 2k

    头像
    Mybatis源码-配置加载

    半夏之沫赞 1阅读 1.6k

    头像
    白鲸开源WhaleStudio荣获2024星空奖！

    海豚调度赞 1阅读 450

    头像
    DeepMind与Google Research齐发力，多技术路线打造AI天气预报的「六边形战士」

    超神经HyperAI赞 1阅读 421

    0 条评论
    得票最新