Oracle_Operations

📂目录对象
  1. 目录对象简介

Oracle 目录对象 (Directory Object) 是数据库中的一个逻辑概念,它将数据库内的一个名称映射到操作系统上的一个物理文件路径。这主要用于方便、安全地在 Oracle 数据库中进行文件读写操作,例如使用数据泵 (Data Pump) 进行导入导出,或者通过 UTL_FILE 包访问服务器上的文件。

  1. 核心操作指南

📋 查询目录对象

可以通过数据字典视图查看已创建的目录对象。

查询示例​:

SELECT directory_name, directory_path FROM dba_directories; --系统层面
SELECT directory_name, directory_path FROM all_directories; --用户层面

✨ 创建目录对象

使用 CREATE DIRECTORY 语句创建目录对象。Oracle 不会自动在操作系统上创建该物理路径,必须确保路径真实存在,否则后续操作会报错。

CREATE OR REPLACE DIRECTORY directory_name AS 'operating_system_path';

示例​:

CREATE OR REPLACE DIRECTORY EXP_DIR AS '/u01/app/oracle/export';
CREATE OR REPLACE DIRECTORY LOG_DIR AS '/u01/app/oracle/logs';

🔧 修改目录对象

修改目录对象(实际上是修改其映射的操作系统路径),同样使用 CREATE OR REPLACE DIRECTORY 语句。

CREATE OR REPLACE DIRECTORY directory_name AS 'new_operating_system_path';

示例​:

CREATE OR REPLACE DIRECTORY EXP_DIR AS '/new/path/to/export';

❌ 删除目录对象

使用 DROP DIRECTORY 语句删除目录对象。这仅删除数据库中的对象,不会删除操作系统中的物理目录或文件。

DROP DIRECTORY directory_name;

示例​:

DROP DIRECTORY EXP_DIR;

🔐 权限管理

创建目录对象需要 CREATE ANY DIRECTORY 权限。使用目录对象进行读写需要被授予相应的 READWRITE 权限。

授权示例​:

GRANT READ, WRITE ON DIRECTORY EXP_DIR TO scott;
GRANT ALL ON DIRECTORY EXP_DIR TO scott; -- 授予 READ, WRITE, EXECUTE 权限

权限查询​:
可以查询 USER_TAB_PRIVSALL_DIRECTORIES 视图来了解用户对目录的权限。

SELECT directory_name, grantee, privilege
FROM user_tab_privs t, all_directories d
WHERE t.table_name(+)=d.directory_name
ORDER BY 1,2,3;
  1. 目录对象的应用

📤 #数据泵 (Data Pump) 操作

数据泵 (expdp, impdp) 是目录对象最常见的应用场景之一,导出/导入的文件必须存放在目录对象指定的位置。

导出示例​:

expdp scott/tiger DIRECTORY=EXP_DIR DUMPFILE=scott_export.dmp LOGFILE=LOG_DIR:export.log

📄 文件操作 (UTL_FILE 包)

UTL_FILE 包允许 PL/SQL 程序读写操作系统文本文件,这些文件必须位于目录对象指定的路径下。

写文件示例​:

DECLARE
  file_handle UTL_FILE.FILE_TYPE;
BEGIN
  file_handle := UTL_FILE.FOPEN('EXP_DIR', 'example.txt', 'W');
  UTL_FILE.PUT_LINE(file_handle, 'Hello, World!');
  UTL_FILE.FCLOSE(file_handle);
END;
/

读文件示例​:

DECLARE
  file_handle UTL_FILE.FILE_TYPE;
  buffer VARCHAR2(4000);
BEGIN
  file_handle := UTL_FILE.FOPEN('EXP_DIR', 'example.txt', 'R');
  UTL_FILE.GET_LINE(file_handle, buffer);
  DBMS_OUTPUT.PUT_LINE(buffer);
  UTL_FILE.FCLOSE(file_handle);
END;
/
  1. 重要注意事项

路径存在性​:创建 DIRECTORY 对象时,Oracle ​不会验证操作系统路径是否存在。你必须在操作系统层面确保该路径已创建,并且 Oracle 软件所有者(如 oracle 用户)对其有适当的读写权限,否则在使用时会报错 (如 ORA-29280)。
权限要求​:
- 创建目录需要 CREATE ANY DIRECTORY 权限。
- 删除目录需要 DROP ANY DIRECTORY 权限。
- 使用目录需要被显式授予 READWRITE 权限。
大小写敏感​:目录对象名称在授权和引用时通常是大小写敏感的。建议使用大写,并在引用时使用大写或加双引号。
操作系统权限​:即使在数据库中被授予了目录的 READ/WRITE 权限,对应的操作系统用户(如运行 Oracle 数据库的用户)也必须在操作系统层面拥有对该物理路径的读写权限。
分布式环境​:在 OceanBase 等分布式数据库环境中,目录对象通常需要指向一个共享文件系统​(如 NFS),以确保所有节点都能访问同一路径。

常见错误与排查

下面用一个表格快速回顾目录对象的核心操作:

操作类型 核心 SQL 命令 所需系统权限 备注
创建目录 CREATE OR REPLACE DIRECTORY ... AS 'os_path'; CREATE ANY DIRECTORY Oracle 不验证 OS 路径是否存在
修改目录 CREATE OR REPLACE DIRECTORY ... AS 'new_os_path'; CREATE ANY DIRECTORY 实质是替换
删除目录 DROP DIRECTORY ...; DROP ANY DIRECTORY 不删除 OS 上的物理目录和文件
授予权限 GRANT READ[,WRITE] ON DIRECTORY ... TO ...; 授权给用户操作目录对象的权限
查询目录 SELECT * FROM DBA_DIRECTORIES; 查看所有目录对象
🔍查询结果格式化

📊 环境设置命令

命令 说明 示例
SET LINESIZE n 设置每行最大字符数(默认80) SET LINESIZE 120
SET PAGESIZE n 设置每页行数(0=不分页) SET PAGESIZE 0(不分页)
SET FEEDBACK OFF 不显示查询行数反馈 SET FEEDBACK OFF
SET HEADING ON 控制是否显示列标题 SET HEADING ON
SET NULL text 定义NULL值的显示文本 SET NULL 'NULL'
SET TAB OFF 用空格替代Tab字符(推荐) SET TAB OFF
SET COLSEP "text" 设置列分隔符 `SET COLSEP "
SET TRIMSPOOL ON 去除输出文件行尾空格 SET TRIMSPOOL ON
SET UNDERLINE char 设置标题下划线字符 SET UNDERLINE =
📝 COLUMN 列格式化命令
命令格式 说明 示例
COLUMN colname HEADING text 设置列标题 COLUMN sal HEADING 'Salary'
COLUMN colname FORMAT format 设置显示格式 见下方格式说明
COLUMN colname JUSTIFY position 设置对齐方式 COLUMN ename JUSTIFY CENTER
COLUMN colname NULL text 设置NULL值显示 COLUMN comm NULL 'N/A'
COLUMN colname WRAPPED 超宽时自动换行 COLUMN notes WRAPPED
COLUMN colname LIKE othercol 复制其他列格式 COLUMN comm LIKE sal

常用格式说明:

📋 标题与页脚设置

-- 顶部标题
TTITLE CENTER '员工信息表' SKIP 2
TTITLE LEFT '部门: ' DEPTNO RIGHT '页码: ' SQL.PNO

-- 底部页脚  
BTITLE CENTER '机密文件' RIGHT '打印日期: ' _DATE

-- 清除标题
TTITLE OFF
BTITLE OFF

💾 输出控制命令

命令 说明
SPOOL filename 开始输出到文件
SPOOL OFF 停止输出到文件
SET TERMOUT OFF 不显示到屏幕(仅输出到文件)

🔧 分组与小计计算

-- 按部门分组
BREAK ON deptno SKIP 1

-- 计算小计和总计
COMPUTE SUM OF sal ON deptno
COMPUTE SUM OF sal ON REPORT

-- 清除分组设置
CLEAR BREAKS
CLEAR COMPUTES

🗑️ 清除与重置命令

命令 说明
CLEAR COLUMNS 清除所有列格式
CLEAR BREAKS 清除分组设置
CLEAR COMPUTES 清除计算设置
SHOW ALL 显示当前所有设置
查看当前设置​:使用 SHOW LINESIZESHOW PAGESIZE
⏫数据泵
  1. 创建与授权目录对象(DIRECTORY)
    数据泵操作的文件必须存放在Oracle定义的目录对象(Directory Object)对应的操作系统目录中。
    -- 1. 使用管理员身份登录SQL*Plus,创建逻辑目录(指向服务器上的物理路径)
CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/u01/backup'; 

-- 2. 将目录的读写权限授予给执行操作的用户

GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO YOUR_USERNAME; 

-- 3. 查询所有目录对象确认创建成功

SELECT * FROM DBA_DIRECTORIES; 

注意:务必确保服务器上对应的物理路径(如/u01/backup)真实存在,且Oracle软件用户(如oracle)有该路径的读写权限。

  1. 导出数据 (expdp)

常用导出模式有按方案(用户)、表、表空间和全库导出。

模式 命令示例(在操作系统命令行执行) 说明
方案级 expdp scott/tiger@orcl DIRECTORY=DUMP_DIR DUMPFILE=scott.dmp LOGFILE=exp_scott.log SCHEMAS=scott 导出指定用户的所有对象和数据。
表级 expdp scott/tiger@orcl DIRECTORY=DUMP_DIR DUMPFILE=tables.dmp LOGFILE=exp_tables.log TABLES=emp,dept 导出指定的表。可配合QUERY参数条件导出。
全库级 expdp system/password@orcl DIRECTORY=DUMP_DIR DUMPFILE=full.dmp LOGFILE=exp_full.log FULL=Y 导出整个数据库(除SYS方案下的部分对象)。需要DBA权限。
expdp SYSTEM/password@LANGCHAOPDB1 DIRECTORY=DUMP_DIR DUMPFILE=lc20250928.dmp LOGFILE=lc20250928.log SCHEMAS="(GSCLOUD,LC00029999)"

之后将dmp文件传输至目标机。

  1. 导入数据 (impdp)

提前建立目标表空间:

create tablespace GSCLOUD_TBS datafile '+DATAC1/LCCDB1/3E44172D_tbs01.dbf' size 32700M autoextend on;

导入:

impdp SYSTEM/infodba@LANGCHAOPDB1 DIRECTORY= EXP_DIR DUMPFILE=lc20250928.dmp SCHEMAS="(GSCLOUD,LC00029999)" cluster=n

CLUSTER=N 是 Oracle Data Pump(数据泵)工具中一个实用的参数,特别在 ​Oracle RAC(实时应用集群)​​ 环境中。它的核心作用是告诉数据泵不要利用整个RAC集群的资源,而是将所有工作进程都限制在启动导入或导出作业的那个单个数据库实例上运行

场景 命令示例 关键参数说明
导入到同名用户 impdp scott/tiger@newdb DIRECTORY=DUMP_DIR DUMPFILE=scott.dmp LOGFILE=imp_scott.log 目标数据库需已存在相应用户并有所需权限。
更改目标用户 impdp system/password@newdb DIRECTORY=DUMP_DIR DUMPFILE=scott.dmp REMAP_SCHEMA=scott:new_user REMAP_SCHEMA将对象从源用户(scott)映射到目标用户(new_user)。
仅导入表结构 impdp scott/tiger@newdb DIRECTORY=DUMP_DIR DUMPFILE=meta.dmp CONTENT=METADATA_ONLY CONTENT=METADATA_ONLY只导入元数据(对象定义),不导入数据。
表已存在时追加数据 impdp scott/tiger@newdb DIRECTORY=DUMP_DIR DUMPFILE=data.dmp TABLE_EXISTS_ACTION=APPEND TABLE_EXISTS_ACTION处理表存在的情况:SKIP(默认,跳过)、APPEND(追加数据)、REPLACE(替换表)或TRUNCATE(清空后插入)。

⚡ 性能优化与高级技巧

  1. 并行处理 (PARALLEL)
    通过启用多个工作进程来大幅提升导入导出速度,通常设置为CPU核心数的2倍左右。
    expdp ... PARALLEL=4 DUMPFILE=expdp_%U.dmp
    %U是通配符,会自动生成多个文件(如expdp_01.dmp, expdp_02.dmp),每个并行进程处理一个文件。

  2. 压缩与加密
    ◦ 压缩 (COMPRESSION):减少导出文件大小,节省磁盘空间和传输时间。可选ALL, DATA_ONLY, METADATA_ONLY等。
    expdp ... COMPRESSION=ALL
    ◦ 加密 (ENCRYPTION):保护敏感数据,如ENCRYPTION=ALL ENCRYPTION_PASSWORD=mypwd

  3. 使用参数文件 (PARFILE)
    当命令参数复杂或包含特殊字符(如QUERY中的引号)时,推荐使用参数文件,可避免转义符的麻烦。
    创建文件 exp.par:

USERID=scott/tiger
DIRECTORY=DUMP_DIR
DUMPFILE=sample.dmp
LOGFILE=exp.log
TABLES=emp
QUERY="WHERE deptno=20"

执行命令:expdp PARFILE=exp.par

⚠️ 注意事项

• 权限要求:执行全库导出导入或操作其他用户对象的用户通常需要具有DBA角色或DATAPUMP_EXP_FULL_DATABASE/DATAPUMP_IMP_FULL_DATABASE权限。

• 空间预估:操作前可使用ESTIMATE_ONLY=Y参数仅估算作业所需的磁盘空间,而不实际执行操作。

• 作业监控与管理:数据泵作业是可在服务器端运行的持久化作业。如果操作被中断,可以使用ATTACH参数附加到现有作业上,进行监控(STATUS)、停止(STOP_JOB)或重启(START_JOB)。

•SCAN IP相关问题 ORA-29283
ORACLE RAC中expdp出现ORA-29283错误解决方法_ora-29283: invalid file operation: nonexistent fil-CSDN博客