In this tutorial, we will explore how to perform a data export using Datapump Export via the DBMS_DATAPUMP API in Oracle Database. This method is highly flexible and lets you export data in an efficient, controlled way.
\n\n\n\nStep 1: Setting up the export
\n\n\n\nBefore starting the export, make sure to run this code on the source database, where you want to perform the export:
\n\n\n\nDECLARE\n Bkp NUMBER;\n s VARCHAR2(30000);\nBEGIN\n -- Abrindo um job de exportação no Data Pump\n Bkp := DBMS_DATAPUMP.OPEN(\n operation => 'EXPORT',\n job_mode => 'SCHEMA',\n job_name => NULL\n );\n\n -- Adicionando arquivo de exportação\n DBMS_DATAPUMP.ADD_FILE(\n handle => Bkp,\n filename => 'datapump_source_DATA_F.dmp',\n directory => 'DATA_PUMP_DIR',\n filetype => dbms_datapump.ku$_file_type_dump_file,\n reusefile => 1\n );\n\n -- Adicionando arquivo de log\n DBMS_DATAPUMP.ADD_FILE(\n handle => Bkp,\n filename => 'datapump_source_DATA_F.log',\n directory => 'DATA_PUMP_DIR',\n filetype => dbms_datapump.ku$_file_type_log_file\n );\n\n -- Selecionando owners para exportação\n SELECT LISTAGG('''' || owner || '''', ', ') WITHIN GROUP (ORDER BY owner) \n INTO s\n FROM (\n SELECT username AS owner\n FROM dba_users\n WHERE username NOT IN (\n 'ANONYMOUS', 'SYS', 'SYSTEM', 'SYSAUX',\n 'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DBSNMP',\n 'DIP', 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER',\n 'ORACLE_OCM', 'SYSBACKUP', 'SYSDG', 'SYSKM',\n 'XDB', 'XS$NULL', 'RDSADMIN', 'SYSRAC',\n 'SYS$UMF', 'REMOTE_SCHEDULER_AGENT', 'GGSYS',\n 'DBSFWUSER'\n )\n );\n\n -- Aplicando filtro para exportação dos schemas selecionados\n DBMS_DATAPUMP.METADATA_FILTER(Bkp, 'SCHEMA_LIST', s);\n\n -- Iniciando o job de exportação\n DBMS_DATAPUMP.START_JOB(Bkp);\nEND;\n/\nStep 2: Tracking the export progress
\n\n\n\nTo check the export progress, run the following command on the source database:
\n\n\n\nSELECT text
FROM TABLE(rdsadmin.rds_file_util.Read_text_file (
p_directory => 'DATA_PUMP_DIR',
p_filename => 'datapump_source_DATA_F.log'
));
Step 3: Checking the generated file
\n\n\n\nTo check the file generated in the directory, use the following command on the source database:
\n\n\n\nSET LINES 200 PAGES 200
COL filename FORMAT A100
SELECT filename,
ROUND(filesize / 1024 / 1024 / 1024) AS SIZE_GB
FROM TABLE(rdsadmin.rds_file_util.Listdir('DATA_PUMP_DIR'))
WHERE filename LIKE 'datapump_source_DATA_F%'
ORDER BY mtime;
For the next steps, transferring to the target environment and creating the DBLINK and DIRECTORY, I suggest checking them directly in the official Oracle documentation, since they can vary depending on the environment’s configuration and security policies.
\n\n\n\n\n\n\n\nCreated by: Carlos Furushima
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n