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 🙂
11 Comments
Trackbacks
- Data Pump PL/SQL error when called via procedure « Beyond Oracle
- ORA-01017 running EXPDP from Enterprise Manager | Beyond Oracle
data_filter needs the schema_name parameter. Without it I get an ORA-39001. With it works fine.
Hi “Nobody”:
In my 10g release it works without schema_name in data_filter!!
Regards,
Joao Oliveira
how about Data Pump PL/SQL Schema export
Code generates these errors…..
ERROR at line 1:
ORA-39001: invalid argument value
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 2926
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3162
ORA-06512: at “HR.PARTIAL_EXPORT”, line 22
ORA-06512: at line 1
Same error as of IMRAN KHAN
hi,
did you used the name_expr metadata filter i used? or the table_name?
Have you added schema_name?
i used:
DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle,
NAME => ‘NAME_EXPR’,
VALUE => ‘IN (”CLIENTES”, ”FORNECEDORES”)’
);
you can also do it like this:
DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle, name => ‘SCHEMA_EXPR’, value => ‘IN (“MYSCHEMA”)’);
Say something… waiting your answer if this hint helped.
Regards
Joao
MY error was.
Ensure that you have the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles. To see a list of all roles assigned to you within your security domain, do the following:
SQL> SELECT * FROM SESSION_ROLES;
I also got one basic question, I want to export schema with table also. Is it possible?
Getting following error while executing the procedure:
ERROR at line 1:
ORA-31634: job already exists
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 911
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4354
ORA-06512: at “SHUBHO.PARTIAL_EXPORT”, line 14
ORA-06512: at line 1
Elapsed: 00:00:00.03
SHUBHO@XE >
SHUBHO@XE >
SHUBHO@XE > SELECT * FROM SESSION_ROLES;
ROLE
——————————
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
PLUSTRACE
XDBADMIN
XDBWEBSERVICES
14 rows selected.
tried with different job name and then following error mesage:
SHUBHO@XE > exec partial_export;
BEGIN partial_export; END;
*
ERROR at line 1:
ORA-39001: invalid argument value
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 2926
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3162
ORA-06512: at “SHUBHO.PARTIAL_EXPORT”, line 23
ORA-06512: at line 1
Elapsed: 00:00:00.48
SHUBHO@XE >
Please assist…
I recently got to this unique site a few weeks ago.
I was really captured with the little bit of resources you have got here.
Huge browses upwards in making such fantastic wewebsite
page!