Oracle 创建并使用外部表

一. 什么是外部表

• 在 Oracle 数据库中，外部表（External Table）是一种特殊类型的表，它允许数据库直接访问外部文件（如 CSV 文件、文本文件等），并将这些文件的内容视为表数据进行查询。
• 外部表本质上是一个逻辑表，它不会在数据库中存储实际的数据，而是通过指定的文件位置直接访问数据文件。

⏹外部表的特点：

• 无需存储数据：外部表的数据存储在外部文件系统中，而不是数据库表空间内。这意味着外部表只是一个映射，使得数据库可以通过 SQL 查询来访问存储在文件中的数据。
• 直接访问外部数据：外部表允许您通过 SQL 查询直接访问外部文件中的数据，而无需将数据导入数据库。这对于处理大量的文件数据特别有用。
• 支持大数据量处理：外部表非常适合处理大量外部数据文件，您可以像查询数据库表一样查询这些文件内容。
• 支持多种文件格式：外部表支持多种不同的文件格式，如 CSV、固定宽度文本文件、分隔符分隔的文件等。

二. 创建外部表所在的文件夹对象

⏹在Oracle安装的Win10上准备好外部表所用到csv数据库文件。

"1","Name_1","41","user1@example.com","2024/05/20 13:05:39"
"2","Name_2","57","user2@example.com","2024/07/29 13:05:39"
"3","Name_3","44","user3@example.com","2024/10/11 13:05:39"
"4","Name_4","24","user4@example.com","2024/04/24 13:05:39"
"5","Name_5","42","user5@example.com","2024/02/21 13:05:39"
"6","Name_6","56","user6@example.com","2024/02/18 13:05:39"

⏹创建外部表所在的文件夹对象

• 需要使用DBA用户
• DBA用户所在的容器应该是PDB，而不是CDB
• 因为我们的Oracle数据库安装在Win10上，所以文件夹路径需要Win10上的路径

# DBA用户切换容器为PDB
SQL>  ALTER SESSION SET CONTAINER = XEPDB1;

Session altered.

# 确认当前容器
SQL> SHOW CON_NAME;

CON_NAME
------------------------------
XEPDB1
SQL>
SQL> CREATE OR REPLACE DIRECTORY PERSON_TABLE_EXT AS 'C:/Users/FengYeHong/Desktop';

Directory created.

# 确认文件夹对象已经创建完毕
SQL> SELECT directory_name, directory_path FROM all_directories WHERE directory_name = 'PERSON_TABLE_EXT';

DIRECTORY_NAME             DIRECTORY_PATH
----------------           ---------------------------
PERSON_TABLE_EXT           C:/Users/FengYeHong/Desktop

三. 授予访问外部表文件夹的权限

3.1 DBA用户授予普通用户访问外部表文件夹的权限

SQL> GRANT READ, WRITE ON DIRECTORY PERSON_TABLE_EXT TO "DB_USER";

Grant succeeded.

3.2 授予Win10上的Oracle用户访问桌面文件夹的权限

⏹通过services.msc打开服务，然后找到OracleServiceXE服务

⏹打开OracleServiceXE服务的属性，然后在登录选项卡中找到当前oracle服务的用户名

四. 普通用户创建外部表

• 创建一个外部表，名称叫做PERSON_TABLE_TEST
• CREATED_DATE DATE "yyyy/mm/dd hh24:mi:ss"：指定日期的格式
• LOCATION ('person_data.csv')，指定加载外部表所在文件夹中的person_data.csv文件。

CREATE TABLE PERSON_TABLE_TEST (
   "ID" NUMBER, 
	"NAME" VARCHAR2(50), 
	"AGE" NUMBER, 
	"EMAIL" VARCHAR2(100), 
	"CREATED_DATE" DATE
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY PERSON_TABLE_EXT
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    (
      ID, 
      NAME, 
      AGE, 
      EMAIL, 
      CREATED_DATE DATE "yyyy/mm/dd hh24:mi:ss"
    )
  )
  LOCATION ('person_data.csv')
)
REJECT LIMIT UNLIMITED;

五. 查询

⏹查询外部表是否真的创建成功

SQL> SELECT TABLE_NAME FROM USER_EXTERNAL_TABLES WHERE TABLE_NAME = 'PERSON_TABLE_TEST';

TABLE_NAME
--------------------------------------------------------------------------------
PERSON_TABLE_TEST

⏹查询外部表中的数据

• 就像查询普通表一样
• 外部表还可以和数据库中的表进行关联查询

六. 删除

⏹普通用户删除外部表，和删除普通表相同

DROP TABLE PERSON_TABLE_TEST;

⏹DBA用户删除外部表所在的目录

-- PERSON_TABLE_EXT 是目录的别名
DROP DIRECTORY PERSON_TABLE_EXT;