Scenario is that we would like to export a schema called SWX residing on a Oracle 11.2.0.2 database running on Windows 32- Bit to a Oracle 10.2.0.4 database running on SPARC 64-bit. The source database has transparent database encryption running. The tablespace where the schema resides is fully encrypted using TDE. The target database is not running TDE. To do this complex export import scenario. We have to use expdp and impdp. Since exp and imp has limitations and is not as effecient as impdp expdp (Data Pump) . Exp and IMP should be used by database administrators only if they are running a 9i database and want to export it to a higher version database. First thing on the Source database, where “SWX” schema resides. We will do the following steps : 1. Create a directory for the export backups and grant read write permissions for it to the public (all users) SQL> create or replace directory export_dir as ‘E:\ORA_BACKUPS’; SQL> grant read, write on directory export_dir to public; 2. expdp system/sys directory=export_dir dumpfile=exp_win_11th_sep_11.dmp logfile=export.txt schemas=swx encryption_password=ora11g version=10.2 or alternately you can create a parameter file called export.par with below parameters and run expdp directory=export_dir dumpfile=exp_win_11th_sep_11.dmp logfile=export.txt schemas=swx encryption_password=ora11g version=10.2 expdp system/sys parfile=export.par 3. FTP the file to the target database using bin mode 4. If the user already exists on target server, you can drop the users. If it doesnt exist proceed like below. 5. create a import directory like we created export directory on source server. The location where you FTP’ed the backupfile should be defined as import_dir SQL> create or replace directory import_dir as ‘/u2’; SQL> grant read, write on directory import_dir to public; 6. Create a parameter file called import.par with below parameters directory=import_dir dumpfile=EXP_WIN_11TH_SEP_11.DMP remap_tablespace=swx_enc:users logfile=import.txt # Do not use encryption_password since this database does not have a wallet defined. If there was already a wallet for this database. You should first open the wallet and then use encryption_password parameter # the original tablespace in source database where the schema SWX orginated was called “swx_enc”. The tablespace where it will be imported on target database is called “users” remap_tablespace=sourcetbs:targettbs #If you want to import to a different schema you can use the option remap_schema=sourcename:targetname 7. impdp system/system parfile=import.par Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE ORA-39082: Object type ALTER_PROCEDURE:”SWX”.”SPUPDATECYCLE” created with compilation warnings Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 1 with process name “DW01” prematurely terminated ORA-31672: Worker process DW01 died unexpectedly. Job “SYSTEM”.”SYS_IMPORT_FULL_02″ stopped due to fatal error at 12:18:44 Do not care for this error. The schema has been imported succesffuly into the 10g database. So as you can see how efficient datapump is as a tool for doign cross platform cross version import and export of data. If you want to do full database restores then RMAN is the best way to do it. But in crossplatform scenario, Transportable tablespace becomes overtly complicated and also it has the limitations of little-endian and big-endian. But datapump has no such restrictions. You can practically export and import from any version to any version , to any platform to any platform.