Data Pump PL/SQL partial export
Since the release 10g, Oracle have redesigned the export / import method. Oracle still ships EXP and IMP but DATA PUMP is now the standard tool for fast data movement between databases. DATA PUMP syntax is very similar to EXP/IMP, although its technology is entirely different.
Data Pump runs independent database jobs (different from traditionally client EXP/IMP) in a distributed environment. For instance, the DATA PUMP scheduler user can be different from the one that is monitoring the status of that export / import job. The exported data is saved /loaded in / from a database DIRECTORY object and the user that exports must have write /read access to it.
What I’ll write today is not about DATA PUMP itself and all it features (google it it’s easy), but is to describe a simple way use the DBMS_DATAPUMP PL/SQL API to to export partial data from some tables on my database. I guess the code explains itself…
First, create a database directory:
CREATE OR REPLACE DIRECTORY MY_DIR AS '/opt/my_oracle_dir'; GRANT READ, WRITE ON DIRECTORY MY_DIR TO joni;
Next, let’s create the procedure:
CREATE OR REPLACE PROCEDURE partial_export AS l_dp_handle NUMBER; l_last_job_state VARCHAR2 (30) := 'UNDEFINED'; l_job_state VARCHAR2 (30) := 'UNDEFINED'; l_sts ku$_status; l_job_name VARCHAR2 (100); l_dirname VARCHAR2 (100); l_filename VARCHAR2 (100); BEGIN l_filename := 'myexpfile.dmp'; -- sets the job name l_job_name := 'BZ' || SYSDATE; l_dp_handle := DBMS_DATAPUMP.OPEN (operation => 'EXPORT', job_mode => 'TABLE', remote_link => NULL, job_name => l_job_name, VERSION => 'LATEST' ); --specify the database directory and the filename for the export file DBMS_DATAPUMP.add_file (handle => l_dp_handle, filename => l_filename, DIRECTORY => 'MY_DYR' ); DBMS_DATAPUMP.add_file (handle => l_dp_handle, filename => l_filename || '.LOG', DIRECTORY => 'MY_DYR', filetype => DBMS_DATAPUMP.ku$_file_type_log_file ); --specify the tables that I want to export. (CLIENTES,FORNECEDORES) DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle, NAME => 'NAME_EXPR', VALUE => 'IN (''CLIENTES'', ''FORNECEDORES'')' ); -- set subset data export. exports only rows that id_empresa equals 2050 DBMS_DATAPUMP.data_filter (handle => l_dp_handle, NAME => 'SUBQUERY', VALUE => 'WHERE ID_EMPRESA=2050', table_name => 'CLIENTES' ); -- set subset data export. exports only rows that id_empresa equals 2050 DBMS_DATAPUMP.data_filter (handle => l_dp_handle, NAME => 'SUBQUERY', VALUE => 'WHERE ID_EMPRESA=2050', table_name => 'FORNECEDORES' ); DBMS_DATAPUMP.start_job (l_dp_handle); DBMS_DATAPUMP.detach (l_dp_handle); END;
It’s ready to be invoked! Try it!!! Now you can export DATA directly from your application without any special coding. Just call this procedure! Next, you just have to code the method to get all that data out of the server directory 🙂
- Data Pump PL/SQL error when called via procedure « Beyond Oracle
- ORA-01017 running EXPDP from Enterprise Manager | Beyond Oracle