Oracle导出SQL文件命令有哪些？

在Oracle数据库管理中,将数据或结构导出为SQL文件是一项常见操作，主要用于数据迁移、备份或开发环境搭建，Oracle提供了多种工具和方法来实现这一需求，其中最常用的是expdp（数据泵导出）和传统的exp（导出工具），以及通过SQL*Plus直接生成SQL脚本，以下将详细介绍这些命令的使用方法、参数配置及注意事项。

使用数据泵导出工具（expdp）

数据泵是Oracle推荐的高效导出工具,比传统exp功能更强大，支持并行处理和更灵活的导出选项。expdp是命令行工具，需通过expdp命令调用，语法结构如下：

expdp username/password@database_name DIRECTORY=directory_name DUMPFILE=dumpfile_name.dmp FILESIZE=filesize SQLFILE=sqlfile_name.sql [其他参数]

核心参数说明：

1. username/password@database_name：数据库连接信息，如scott/tiger@orcl。
2. DIRECTORY：指定数据库服务器上的目录对象，该目录需预先创建并授予读写权限。

   CREATE DIRECTORY exp_dir AS '/u01/oracle/expdp';
   GRANT READ, WRITE ON DIRECTORY exp_dir TO scott;

3. DUMPFILE：导出文件的名称，默认扩展名为.dmp。
4. SQLFILE：关键参数，用于将导出的DDL（数据定义语言）语句输出到SQL文件，而非.dmp文件，例如SQLFILE=tables.sql。
5. TABLES：指定导出的表名，多个表用逗号分隔，如TABLES=emp,dept。
6. SCHEMAS：导出整个用户模式下的所有对象，如SCHEMAS=scott。
7. FULL：导出整个数据库，需DBA权限，如FULL=Y。
8. QUERY：使用条件导出部分数据，如QUERY="WHERE deptno=10"。
9. PARALLEL：并行导出线程数，提高导出速度，如PARALLEL=4。

示例场景：

• 导出指定表的SQL脚本：

  expdp scott/tiger@orcl DIRECTORY=exp_dir TABLES=emp,dept SQLFILE=emp_dept_ddl.sql

  执行后,emp_dept_ddl.sql文件将包含emp和dept表的建表语句、索引、约束等DDL。

• 导出整个模式的SQL脚本：

  
  （图片来源网络，侵删）

  expdp scott/tiger@orcl DIRECTORY=exp_dir SCHEMAS=scott SQLFILE=scott_ddl.sql

注意事项：

• SQLFILE参数仅导出DDL语句，不包含数据，若需导出数据，需使用DUMPFILE生成.dmp文件，再用impdp导入。
• 数据泵导出需在数据库服务器端执行,且用户需有EXP_FULL_DATABASE角色权限。

使用传统导出工具（exp）

传统exp工具逐步被expdp取代，但在某些旧版本Oracle中仍可能使用，其基本语法如下：

exp username/password@database_name FILE=filename.dmp LOG=logfile.sql FULL=Y [其他参数]

• ROWS=Y：仅导出表结构（DDL），不导出数据，但exp本身不直接支持生成SQL文件，需通过第三方工具或手动转换。
• INDEXES=Y：导出索引定义。
• GRANTS=Y：导出权限和角色。

由于exp无法直接生成SQL文件，通常建议优先使用expdp。

通过SQL*Plus生成SQL脚本

对于简单的表结构导出,可直接通过SQL*Plus查询数据字典视图生成SQL语句。

-- 查询表结构并生成建表语句
SET LONG 1000000 PAGESIZE 0 LINESIZE 1000
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') FROM DUAL;

将上述查询结果保存为.sql文件即可，若需导出多个表，可通过循环或脚本批量处理。

常用数据字典视图：

导出数据为SQL文件的其他方法

若需同时导出表结构和数据（即DML语句），可通过以下方式：

1. 使用PL/SQL Developer等工具：图形化界面中右键表选择“Export to SQL”。
2. 编写自定义脚本：通过查询数据字典拼接INSERT语句，

   SET ECHO OFF
   SPOOL data.sql
   SELECT 'INSERT INTO ' || table_name || ' VALUES (' || 
          LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_id) || ');'
   FROM user_tab_columns
   WHERE table_name = 'EMP';
   SPOOL OFF

FAQs

问题1：expdp导出时提示“未授权目录对象”如何解决？
解答：需确保已创建目录对象并授予用户权限，执行以下SQL：

CREATE DIRECTORY directory_name AS 'path';
GRANT READ, WRITE ON DIRECTORY directory_name TO username;

同时检查操作系统目录是否存在且有读写权限。

问题2：如何导出表数据并包含INSERT语句到SQL文件？
解答：expdp的SQLFILE参数仅导出DDL，若需导出数据，可使用以下方法：

1. 通过SQL*Plus生成INSERT语句：

   SET HEADING OFF PAGESIZE 0 FEEDBACK OFF
   SPOOL data.sql
   SELECT 'INSERT INTO emp (empno, ename) VALUES (' || empno || ', ''' || ename || ''');' FROM emp;
   SPOOL OFF

2. 使用第三方工具如ora2sql或编写PL/SQL动态生成脚本。