Generate DDL from a Data Pump Export File

-- Get DDL from impdp -- The below command will not import the dump, but will just create a .sql file with the DDL statements impdp system/**** directory=import_dir dumpfile=IMPORT.DMP sqlfile=getddl.sql

Export Oracle 11g (x86 Windows) Schema into Oracle 10g (SPARC Solaris) using EXPDP ( Cross Platform, Cross Versin Migration)

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 … Continue reading Export Oracle 11g (x86 Windows) Schema into Oracle 10g (SPARC Solaris) using EXPDP ( Cross Platform, Cross Versin Migration)

How to Add Date and Time to a Filename with Windows Command Line.

Many times it becomes necessary to append date stamp on your export dumps. In Unix systems, shell scripting is very liberal with variables you can define yourself eg: expdate=`date '+%d%m%Y'` dat=`date '+%m%d%y %H:%M:%S'` And then go onto define in your script as ./expdp system/system directory=export_dir dumpfile=exp_swx_$expdate.dmp logfile=exp_swx_$expdate.log schemas=swx But on Windows it can be done … Continue reading How to Add Date and Time to a Filename with Windows Command Line.

Export tablespace Oracle 10g: Transportable Tablespace

Transportable Tablespace across Different Platforms ___________________________________________________ In our example we will transport a tablespace called TEST. The tablespace should be self-contained without any objects referring to other tablespaces so for that check if the tablespace os self contained using below query: EXECUTE dbms_tts.transport_set_check('TEST', TRUE, TRUE); SELECT * FROM transport_set_violations; If you have cross referenced objects. … Continue reading Export tablespace Oracle 10g: Transportable Tablespace

How to import a schema to a new server in a different tablespace : Oracle 10g

1.  Copy the backup of the dumpfile to the target server. 2.  Create a tablespace in our eg: called TEST. Make sure this tablespace is atleast 20% larger than the size of your dumpfile(its not a hard n fast rule but its better IMO). 3. Create a user TEST and grant unlimited quota on the … Continue reading How to import a schema to a new server in a different tablespace : Oracle 10g

Use datapump(impdp) to import a table : Oracle 10g

----- Procedure to import table from production system to test system ------ 1. Copy the backup(dump) of  schema onto /backup_restor_only $ gzip -d <name of the file> 2. After this gzping finished logon to SQL*PLUS and type below commands SQL> create or replace directory import_dir as '/backup_restor_only'; SQL> grant read,write on DIRECTORY import_dir to public; … Continue reading Use datapump(impdp) to import a table : Oracle 10g