|
Category :
Database
Resources -> Oracle Utilities
DB Version
:
Oracle 10G
OS Details :
Sun Solaris9 / 10
Reference
: Oracle Metalink & other web sites
The
10G version of oracle has introduced the feature of data pump. One of
the biggest advantages of using this tool is the ability to perform
data pump and data import from within PLSQL through the use of the
DBMS_DATAPUMP API. Listed below is an example of using a schema
metadata only export.
(Step 1) The owner
of this procedure would need the following privileges granted directly
(not through a role):
SQL> grant
create session, create table, create procedure, exp_full_database to
test_user;
(Step 2) Create a directory object and make sure the
user who will be performing the API based data pump operation has read
and write privileges on the directory
SQL> create or replace directory data_pump_dir as '/app/datapump';
SQL> grant read,write on directory data_pump_dir to test_user;
(Step3) Create the Stored procedure
create
or replace procedure datapump_proc(sch_name in varchar2) is
dp_handle NUMBER;
job_status VARCHAR2(30);
var_filter_string varchar2(1000);
begin
--
Datapump operation is an EXPORT operation done at the schema level
dp_handle := dbms_datapump.open(operation => 'EXPORT',job_mode =>
'SCHEMA');
--
Define the dump file location
dbms_datapump.add_file(handle => dp_handle, filename =>
'exp_test.dmp',directory => 'DATA_PUMP_DIR', filetype
=>DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
--
Define the log file location
dbms_datapump.add_file(handle => dp_handle,filename => 'expt.log',
directory => 'DATA_PUMP_DIR', filetype
=>DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
--
Parse the procedure's input string which identifies the schema for
which the export will be done
var_filter_string := 'IN ('''||upper(sch_name)||''')';
--
Define the schema for which the data pump export will be done
dbms_datapump.metadata_filter(handle => dp_handle, name =>
'SCHEMA_EXPR', value => var_filter_string);
--
Defines the data pump export to be a meta data only export (rows=N)
dbms_datapump.data_filter(handle=> dp_handle, name=> 'INCLUDE_ROWS' ,
value=>0);
--
Start the Data pump Export
dbms_datapump.start_job(dp_handle);
dbms_datapump.wait_for_job(handle => dp_handle, job_state =>
job_status) ;
dbms_output.put_line('DataPump Export - '||to_char(sysdate,'DD/MM/YYYY
HH24:MI:SS')||' Status '||job_status);
dbms_datapump.detach(handle => dp_handle);
end;
/
|
