12c Database : Datapump Enhancements (EXPDP)

Here is the list of enhancements for export datapump.

expdp Specifying the Encryption Password

	You can now specify silently a password during runtime. When you import a encrypted Dumpfile 
	you can pass the Password for that file from the STDIN. 
	The password will not visible by commands like ps or will not be stored in scripts.

	Example:-
	
	expdp scott/tiger DIRECTORY=dpump1 DUMPFILE=export.dmp ENCRYPTION_PWD_PROMPT=Y

Exporting Views as Tables You can now export a View as a table.

	Example:-
	expdp scott/tiger DIRECTORY=dpump1 DUMPFILE=scott1.dmp VIEWS_AS_TABLES=empview tables=emp

Transportable feature You can now use trasportable feature for FULL/Tablespace/Table:Paritions with export pump

Full Transportable Export/Import (Full Database)

	Example:-	
	1) Make the tablespaces readonly
		
	2) Export the database
		expdp user_name full=y dumpfile=expdat.dmp directory=data_pump_dir transportable=always logfile=export.log
	
	3) Check the export log, which files should be copied to target system.
		Example:- 
			******************************************************************************
			Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
			  /u01/app/oracle/admin/mydb/dpdump/expdat.dmp
			******************************************************************************
			Datafiles required for transportable tablespace SALES:
			  /u01/app/oracle/oradata/mydb/sales01.dbf
			Datafiles required for transportable tablespace CUSTOMERS:
			  /u01/app/oracle/oradata/mydb/cust01.dbf
			Datafiles required for transportable tablespace EMPLOYEES:
			  /u01/app/oracle/oradata/mydb/emp01.dbf

	4) Check the endian conversion if required
		SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
		     FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
		     WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

	5) Import the database
		impdp scott/tiger FULL=y DUMPFILE=fullexp.dmp DIRECTORY=dpump1 
		TRANSPORT_DATAFILES='/u01/app/oracle/oradata/db1211/users01.dbf' LOGFILE=import.log

Transportable Export/Import (Tablespaces)

	1) Check the tablespace self contained or not
		EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);
		SELECT * FROM TRANSPORT_SET_VIOLATIONS;

	2) Make the tabelspaces read only
		ALTER TABLESPACE sales_1 READ ONLY;
		ALTER TABLESPACE sales_2 READ ONLY;
	
	3) expdp user_name dumpfile=expdat.dmp directory=data_pump_dir transport_tablespaces=sales_1,sales_2 logfile=tts_export.log
	
	4) Check the logs for the files that are need to copy to target system
		Example:-
		*****************************************************************************
		Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
		  /u01/app/oracle/admin/salesdb/dpdump/expdat.dmp
		*****************************************************************************
		Datafiles required for transportable tablespace SALES_1:
		  /u01/app/oracle/oradata/salesdb/sales_101.dbf
		Datafiles required for transportable tablespace SALES_2:
		  /u01/app/oracle/oradata/salesdb/sales_201.dbf

	5) Convert the endian of files in source system if required, mark the tablespace read/write in source
	
	6) Copy those file to target system 
	
	7) impdp user_name dumpfile=expdat.dmp directory=data_pump_dir
		   transport_datafiles=
		   'c:\app\orauser\oradata\orawin\sales_101.dbf',
		   'c:\app\orauser\oradata\orawin\sales_201.dbf'
		   remap_schema=sales1:crm1  remap_schema=sales2:crm2
		   logfile=tts_import.log

Transportable Export/Import (Tables/Partitions)

	1) Mark the datafiles that are associated with table as read only
		SQL> ALTER TABLESPACE sales_prt_tbs READ ONLY;
	
	2) Export using expdp, tables
		Example:-
		expdp user_name dumpfile=sales_prt.dmp  directory=data_pump_dir 
	      	tables=sh.sales_prt:sales_q1_2000,sh.sales_prt:sales_q2_2000
	      	transportable=always logfile=exp.log
	      
	3) Check the log for the datafiles to be copied.
		Datafiles required for transportable tablespace SALES_PRT_TBS:
		  /u01/app/oracle/oradata/sourcedb/sales_prt.dbf
		Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 11:32:13

	4) Copy the datafile to target
	
	5) Import the dump
	Example:- impdp user_name dumpfile=sales_prt.dmp directory=data_pump_dir
		   transport_datafiles='/u01/app/oracle/oradata/targetdb/sales_prt.dbf'
		   tables=sh.sales_prt:sales_q1_2000,sh.sales_prt:sales_q2_2000
		   logfile=imp.log

Next Post: IMPDP Enhancements

-Thanks

Geek DBA