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.
Step 1: Setting up the export
Before starting the export, make sure to run this code on the source database, where you want to perform the export:
DECLARE
Bkp NUMBER;
s VARCHAR2(30000);
BEGIN
-- Abrindo um job de exportação no Data Pump
Bkp := DBMS_DATAPUMP.OPEN(
operation => 'EXPORT',
job_mode => 'SCHEMA',
job_name => NULL
);
-- Adicionando arquivo de exportação
DBMS_DATAPUMP.ADD_FILE(
handle => Bkp,
filename => 'datapump_source_DATA_F.dmp',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file,
reusefile => 1
);
-- Adicionando arquivo de log
DBMS_DATAPUMP.ADD_FILE(
handle => Bkp,
filename => 'datapump_source_DATA_F.log',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file
);
-- Selecionando owners para exportação
SELECT LISTAGG('''' || owner || '''', ', ') WITHIN GROUP (ORDER BY owner)
INTO s
FROM (
SELECT username AS owner
FROM dba_users
WHERE username NOT IN (
'ANONYMOUS', 'SYS', 'SYSTEM', 'SYSAUX',
'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DBSNMP',
'DIP', 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER',
'ORACLE_OCM', 'SYSBACKUP', 'SYSDG', 'SYSKM',
'XDB', 'XS$NULL', 'RDSADMIN', 'SYSRAC',
'SYS$UMF', 'REMOTE_SCHEDULER_AGENT', 'GGSYS',
'DBSFWUSER'
)
);
-- Aplicando filtro para exportação dos schemas selecionados
DBMS_DATAPUMP.METADATA_FILTER(Bkp, 'SCHEMA_LIST', s);
-- Iniciando o job de exportação
DBMS_DATAPUMP.START_JOB(Bkp);
END;
/
Step 2: Tracking the export progress
To check the export progress, run the following command on the source database:
SELECT 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
To check the file generated in the directory, use the following command on the source database:
SET 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.
Created by: Carlos Furushima