— 步骤2：创建外部表目录
CREATE OR REPLACE DIRECTORY ext_data AS ‘/yashandb/data/external’;

— 步骤3：授予权限
GRANT READ, WRITE ON DIRECTORY ext_data TO fgedu;

— 步骤4：创建外部表
CREATE TABLE fgedu_sales_ext (
id NUMBER(10),
sale_date DATE,
customer_id NUMBER(10),
amount NUMBER(12,2),
product_id NUMBER(10)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_data
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”‘
MISSING FIELD VALUES ARE NULL
(id, sale_date DATE ‘YYYY-MM-DD’, customer_id, amount, product_id)
)
LOCATION (‘sales_2025.csv’)
);

— 步骤5：验证外部表数据
SELECT COUNT(*) FROM fgedu_sales_ext;

— 输出结果
COUNT(*)
———-
10000

— 步骤6：将外部表数据加载到内部表
INSERT /*+ PARALLEL(8) */ INTO fgedu_sales
SELECT * FROM fgedu_sales_ext;

— 输出结果
10000 rows created.

— 步骤7：验证内部表数据
SELECT COUNT(*) FROM fgedu_sales;

— 输出结果
COUNT(*)
———-
10000

— 步骤2：授予权限
GRANT READ, WRITE ON DIRECTORY ext_data TO fgedu;

— 步骤3：创建外部表（用于导出）
CREATE TABLE fgedu_customers_ext (
json_data VARCHAR2(4000)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ext_data
LOCATION (‘customers.dmp’)
);

— 步骤4：将客户数据导出到外部表
INSERT INTO fgedu_customers_ext
SELECT
‘{‘ ||
‘”customer_id”:’ || customer_id || ‘,’ ||
‘”name”:”‘ || name || ‘”,’ ||
‘”email”:”‘ || email || ‘”,’ ||
‘”address”:”‘ || address || ‘”‘ ||
‘}’ AS json_data
FROM fgedu_customers;

— 输出结果
1000 rows created.

— 步骤5：验证导出文件
— 检查外部目录中的文件
SELECT * FROM TABLE(
DBMS_BACKUP_RESTORE.fileList(
‘EXT_DATA’,
‘customers.dmp’
)
);

— 步骤6：从外部表读取数据（验证导出）
SELECT * FROM fgedu_customers_ext WHERE ROWNUM <= 5; -- 输出结果 JSON_DATA -------------------------------------------------- {"customer_id":1,"name":"张三","email":"zhangsan@example.com","address":"北京市朝阳区"} {"customer_id":2,"name":"李四","email":"lisi@example.com","address":"上海市浦东新区"} {"customer_id":3,"name":"王五","email":"wangwu@example.com","address":"广州市天河区"} {"customer_id":4,"name":"赵六","email":"zhaoliu@example.com","address":"深圳市南山区"} {"customer_id":5,"name":"钱七","email":"qianqi@example.com","address":"杭州市西湖区"}

— 步骤2：授予权限
GRANT READ, WRITE ON DIRECTORY ext_data TO fgedu;

— 步骤3：创建外部表（访问外部系统数据）
CREATE TABLE fgedu_inventory_ext (
product_id NUMBER(10),
product_name VARCHAR2(100),
quantity NUMBER(10),
last_update DATE
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_data
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘|’
OPTIONALLY ENCLOSED BY ‘”‘
MISSING FIELD VALUES ARE NULL
(product_id, product_name, quantity, last_update DATE ‘YYYY-MM-DD HH24:MI:SS’)
)
LOCATION (‘inventory_*.csv’)
);

— 步骤4：创建定期同步作业
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => ‘FGEDU.SYNC_INVENTORY’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN
— 清空本地表
TRUNCATE TABLE fgedu_inventory;
— 从外部表同步数据
INSERT /*+ PARALLEL(4) */ INTO fgedu_inventory
SELECT * FROM fgedu_inventory_ext;
COMMIT;
END;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=HOURLY; BYMINUTE=0;’,
enabled => TRUE,
comments => ‘每小时同步库存数据’
);
END;
/

— 步骤5：查询集成数据
SELECT * FROM fgedu_inventory WHERE quantity < 10; -- 输出结果 PRODUCT_ID PRODUCT_NAME QUANTITY LAST_UPDATE ---------- --------------- -------- ----------- 1 产品A 5 2025-01-20 10:00:00 5 产品E 8 2025-01-20 10:00:00 -- 步骤6：监控同步作业 SELECT job_name, status, last_start_date, next_run_date FROM user_scheduler_jobs WHERE job_name = 'SYNC_INVENTORY';