How to bulk export tables in PL/SQL?

2 years ago
Noah Thompson
2 minutes

In PL/SQL, you can use the Data Pump utility program to batch export tables. Data Pump is a tool provided by Oracle Database for efficient data import and export.

Here is an example code for exporting multiple tables in bulk.

DECLARE
  l_directory_name   VARCHAR2(30) := 'DATA_PUMP_DIR'; -- 数据库目录名称
  l_export_directory VARCHAR2(100) := 'D:\export_dir'; -- 导出文件保存目录
  l_export_job_name  VARCHAR2(30) := 'EXPORT_JOB'; -- 导出作业名称
BEGIN
  -- 创建数据库目录
  EXECUTE IMMEDIATE 'CREATE DIRECTORY ' || l_directory_name || ' AS ''' || l_export_directory || '''';

  -- 创建导出作业
  DBMS_DATAPUMP.CREATE_JOB(job_name       => l_export_job_name,
                           job_type       => 'EXPORT',
                           job_action     => 'SCHEMA_EXPORT',
                           directory_name => l_directory_name);

  -- 添加导出作业参数
  DBMS_DATAPUMP.ADD_PARAMETER(handle   => DBMS_DATAPUMP.JOB_HANDLE,
                              name     => 'SCHEMAS',
                              value    => 'SCHEMA_NAME1, SCHEMA_NAME2, ...', -- 表所在的模式名称，可以是多个模式，以逗号分隔
                              no_echo  => 1);
  DBMS_DATAPUMP.ADD_PARAMETER(handle   => DBMS_DATAPUMP.JOB_HANDLE,
                              name     => 'INCLUDE',
                              value    => 'TABLE', -- 导出的对象类型，这里选择导出表
                              no_echo  => 1);
  DBMS_DATAPUMP.ADD_PARAMETER(handle   => DBMS_DATAPUMP.JOB_HANDLE,
                              name     => 'CONTENT',
                              value    => 'DATA_ONLY', -- 导出的内容，这里选择只导出数据
                              no_echo  => 1);
  -- 启动导出作业
  DBMS_DATAPUMP.START_JOB(l_export_job_name);
END;
/

The variables that need to be replaced in the code above are:

  1. l_directory_name: The name of the database directory is used to specify the directory where the export file will be saved.
  2. Export directory: the path where exported files are saved.
  3. Export job name: the name of the job for exporting.
  4. The SCHEMA_NAME1, SCHEMA_NAME2, etc. in the value parameter: the names of the schemas where the tables to be exported are located, multiple schemas separated by commas.

After running the above code, PL/SQL will create an export job and save the exported table data to the specified directory.

bannerAds