How do you export table data to a dmp file in PL/SQL?

2 years ago
Benjamin Taylor
2 minutes

In PL/SQL, you can use the Data Pump tool to export table data into a DMP file. Here are the steps to export table data using Data Pump.

  1. Create a directory object to specify the export path of the DMP file (skip this step if the directory object already exists).
CREATE DIRECTORY export_dir AS '/path/to/dmp/files';
  1. Write code in PL/SQL to export table data.
BEGIN
  DBMS_DATAPUMP.OPEN(
    operation => 'EXPORT',
    job_mode => 'SCHEMA',
    job_name => 'my_export_job'
  );

  DBMS_DATAPUMP.ADD_FILE(
    handle => 'my_export_job',
    filename => 'my_export.dmp',
    directory => 'EXPORT_DIR',
    filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
  );

  DBMS_DATAPUMP.METADATA_FILTER(
    handle => 'my_export_job',
    name => 'SCHEMA_EXPR',
    value => 'IN(''SCHEMA_NAME'')'
  );

  DBMS_DATAPUMP.START_JOB('my_export_job');
END;
/

In the code above, replace “/path/to/dmp/files” with the actual export path, replace “my_export.dmp” with the actual DMP file name, and replace “SCHEMA_NAME” with the schema where the table data to be exported is located (e.g. HR).

  1. Running the above PL/SQL code will create a DMP file at the specified export path, which includes the data of tables in the specified schema.

注意：上述代码只导出了表数据，不包含表结构。如果需要同时导出表结构和数据，可以修改job_mode为’FULL’，并添加相应的元数据过滤器。

In addition, you can also use database management tools such as Oracle SQL Developer to export data. These tools offer a more user-friendly interface, making it easy to export table data as a DMP file.

bannerAds