I had a task to create a way to save the database from clients with only Oracle Instant Client installed. As you may or may not know exporting a database is simple with exp like this:

1
exp [email protected] FILE=D:\exp.dmp LOG=exp.log

Well i have some bad news. Imp and exp only come with a full install of Oracle and aren’t included in the Instant Client. I could’ve done it dirty by copying the imp, exp binaries and the necessary dll-s to place, but hey this is not a nice solution. I did a little research on the topic and found Datapump. Datapump is a great  and modern tool in the Oracle DB and the  icing on the cake is: it has a nice PL/SQL API .

So i came up with the following stored procedure ( It is not exactly the same , you know, i’m not allowed to share company information and stuff) but the code i post here will work for the typical Scott tiger Foo example:

Note:

Make sure you have the rights needed to export full schema before you run the procedure. If you haven’t got enough rights you can always run:[code]grant exp_full_database, imp_full_database to foo with admin option[/code] as sysdba.

Note 2:

Make sure you have created the directory called DUMP_DIR or you will not be able to run the export.

1
2
create or replace directory DUMP_DIR as \\NETWORK-SHARED\LOCATION;
    grant read, write on directory DUMP_DIR to SCOTT;

So here comes the actual stored procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
create or replace procedure CreateDataPumpExport ( dumpname in varchar2,EXPORTSECONDARYSCHEMA in varchar2)
is
l_dp_handle NUMBER;
l_last_job_state VARCHAR2(30) := 'UNDEFINED';
l_job_state VARCHAR2(30) := 'UNDEFINED';
l_sts KU$_STATUS;
task_in_progress number:=1;
task_id number:=Expseq.nextval;
BEGIN
l_dp_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
job_name => 'Datapump_DB_export'||task_id,
version => 'LATEST');

DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => dumpname||to_char(sysdate,'yyyy-mm-dd')||task_id||'.datapumpexport',
directory => 'DUMP_DIR');

DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => dumpname||task_id||'.log',
directory => 'DUMP_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
if EXPORTSECONDARYSCHEMA='T' then
DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'SCHEMA_EXPR',
value => 'IN (''FOO'',''BAR'')');
end if;
if EXPORTSECONDARYSCHEMA <> 'T' then
DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'SCHEMA_EXPR',
value => '= ''FOO''');
end if;

DBMS_DATAPUMP.start_job(l_dp_handle);

DBMS_DATAPUMP.detach(l_dp_handle);
END;
/

If everything went correctly the datapump export just started. Wait until it finishes and you can import the result into any Oracle 10 or 11 Database using impdp.

OpenSo(u)rcery

Long time, no see. Don't worry, I'm not dead, not even remotely dead. I'm just pretty busy. I'm actually working on great stuff at work and …… Continue reading

Openduty @ Cloud Budapest

Published on April 12, 2015

Leaving Ustream

Published on February 07, 2015