Oracle Data Pump

Oracle Data Pump enables high-speed movement of data and metadata from one database to another.

Directory

Data pump works with files in oracle directory, so it must be created before running the examples:
CREATE OR REPLACE DIRECTORY dpout AS 'C:\out';
GRANT READ, WRITE ON DIRECTORY dpout TO hr;

expdp and impdp

Command-line clients

Example of how to copy a table using expdp and impdp utilities:

-- Copy only data from hr.regions to hr.regions2
CREATE TABLE hr.regions2 AS SELECT * FROM hr.regions WHERE 1=2;

expdp hr/hr@mspubsrv DIRECTORY=dpout DUMPFILE=regions.dmp LOGFILE=export.log TABLES=regions CONTENT=DATA_ONLY

impdp hr/hr@mspubsrv DIRECTORY=dpout DUMPFILE=regions.dmp TABLES=regions REMAP_TABLE=regions:regions2

DBMS_DATAPUMP

PL/SQL package, also known as the Data Pump API

Example of how to export a schema using DBMS_DATAPUMP package:

-- Export schema using DBMS_DATAPUMP package

DECLARE
	nHandle NUMBER;
	sJobName 		VARCHAR2(30) := 'MY_JOB_NAME'; -- 30 characters limit; it will be truncated if more than 30 characters are used
	sJobStatus	VARCHAR2(64);
BEGIN
	-- Open datapump job
	nHandle := DBMS_DATAPUMP.OPEN
	(
		operation   => 'EXPORT',
		job_mode    => 'SCHEMA',
		job_name    => sJobName
	);
  	
	-- Dump file
	DBMS_DATAPUMP.ADD_FILE
	(
		handle    => nHandle,
		filename  => 'file.dmp', -- file name can contain %U - it will be replaced by 01, 02 etc, depending on how many files are needed to perform the export
		directory => 'dpout',
		filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
	);

	-- Log file
	DBMS_DATAPUMP.ADD_FILE
	(
		handle    => nHandle,
		filename  => 'file.log',
		directory => 'dpout',
		filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
	);
	
	-- Set schema name to export
	DBMS_DATAPUMP.METADATA_FILTER
	(
		handle => nHandle,
		name   => 'SCHEMA_EXPR',
		value  => '= ''USER1'''
	);

	DBMS_DATAPUMP.METADATA_REMAP
	(
		handle	=> nHandle,
		name		=>'REMAP_SCHEMA',
		old_value => 'USER1',
		value => 'USER2',
	);
		
	-- Parallelism setting should be less than or equal to the number of dump files in the dump file set.
	DBMS_DATAPUMP.SET_PARALLEL
	(
		handle => nHandle,
		degree => 1
	);
  	
	-- Write to log file
	DBMS_DATAPUMP.LOG_ENTRY
	(
		handle => nHandle,
		message => '---> Schema Export via PL/SQL'
	);
  	
	-- Start job
	DBMS_DATAPUMP.START_JOB(nHandle);
  	
	DBMS_DATAPUMP.WAIT_FOR_JOB
	(
		handle => nHandle,
		job_state => sJobStatus
	);
END;
/

Last edited Jul 12, 2012 at 1:27 PM by alekseiv, version 2

Comments

No comments yet.