Archive for the ‘oracle 9i’ Category

Hi friends,


Last one year i have work a lot on export and import for small to huge database, from specific objects to schema to and query based backup.


here I am listed some of my syntax which you can note down for your reference.

  1. export objects from specific schema which match the specific criteria.

expdp system/oracle directory=DUMPDIR1 dumpfile=BG_objects_%u.dmp logfile=BG_objects.log

2. Import objects to the schema which were own by other schema but exclude some specific objects.

impdp system/oracle directory=DUMPDIR1 dumpfile=BG_objects_01.dmp logfile=BG_objects_imp.log EXCLUDE=TABLE:\”LIKE \’%POSTED%\’\” table_exists_action=REPLACE

3. Import objects from one schema to other schema where tablespace for both the schema were different and from different version(like from 10g to 11g).

impdp system/oracle directory=DUMP_BACKUP dumpfile=dumpfile_17032015.dmp logfile=logfile_imp.log remap_schema=<from_schema>:<to_schema>
remap_tablespace=<from_tablespace>:<to_tablespace> table_exists_action=replace EXCLUDE=STATISTICS;

4. Export specific objects from the schema.

expdp username directory=BCKUP_DUMP dumpfile=schema_pk_objects_%U.dmp logfile=schema_pk_objects.log include=TABLE:\”LIKE \’%PK_%\’\”;

5. Export only one table.

expdp oracle directory=BKUP_DUMP dumpfile=schema_ac_objects_%U.dmp logfile=schema_ac_objects.log tables=<Table_name>;

6.Export a big database and break the dumpfile to matching criteria to fit to the space available in the disk.

expdp SYSTEM/oracle dumpfile=DUMP1:SCHEMA_%u.dmp dumpfile=DUMP2:SCHEMA_%u.dmp filesize=96G logfile=DUMP1:schema.log full=y exclude=statistics;

7. Import a full dump which contains multiple user in different tablespace to a single common user in a single common tablespace to same database or in a different database.

impdp system/oracle directory=dump dumpfile=EPIXDB_01.dmp,EPIXDB_02.dmp full=y logfile=epixdb_imp.log remap_schema=<Schema1>:<schema_common>
remap_schema=<Schema2>:<schema_common> remap_schema=<Schema3>:<schema_common> remap_schema=<Schema4>:<schema_common> remap_schema=<Schema5>:<schema_common>
remap_schema=<Schema6>:<schema_common> remap_schema=<Schema7>:<schema_common> remap_schema=<Schema8>:<schema_common> remap_schema=<Schema9>:<schema_common> remap_schema=<Schema10>:<schema_common>
remap_schema=<Schema11>:<schema_common> remap_schema=<Schema12>:<schema_common> remap_tablespace=<tablespace1>:<common_tablespace> remap_tablespace=<tablespace2>:<common_tablespace>
remap_tablespace=<tablespace3>:<common_tablespace> table_exists_action=replace;

8.export of a object to a specific time or from a specific time this is also known as incremental backup (note:flashback log must be present)

expdp username directory=BKUP_DUMP dumpfile=schema_ac_objects_%U.dmp logfile=schema_ac_objects.log tables=TABLE_NAME
FLASHBACK_TIME=\”to_timestamp\(to_char\(sysdate,\’yyyy-mm-dd hh24:mi:ss\’\),\’yyyy-mm-dd hh24:mi:ss\’\)\”;

9. Traditional export or exp utility to take backup of specific data.

exp user/pass file=exp.dmp log=exp.log TABLES=test query=”””where rownum< 101″””;

exp uwclass/uwclass owner=SCOTT tables=emp query=\” WHERE job=\’MANAGER\’ AND sal \>50000\” STATISTICS=NONE;

10.Export of multiple table from multiple schemas.

expdp system/password directory=BACKUP dumpfile=schema_old_table%u.dmp logfile=schema_old_tables.log schemas=’SCHEMA1′,’SCHEMA2′,’SCHEMA3′,’SCHEMA4′,’SCHEMA5′,’SCHEMA6′

11.Export from multiple schema but table name can be like matching criteria.

expdp system/U6dba#15@projdb directory=backup dumpfile=projdbdmp_%u.dmp logfile=projdbdmp_full.log schemas=’SCHEMA1′,’SCHEMA2′,’SCHEMA3′,’SCHEMA4′,’SCHEMA5′,’SCHEMA6′ INCLUDE=TABLE:\”IN\(\SELECT table_name FROM dba_tables where table_name like \’CC_%\’ \)\”;


Thats it..


Above are a snap-chat 🙂 of my huge collection of syntax which are most common in worst scenario.Still if you have any other critical situation which you are facing please let me know in the comment, It would be a honor to help.


Hope it helps.


With Regards

Nimai Karmakar




Read Full Post »

Bitbach's Blog

Just another Oracle weblog @ WordPress

Daniel Westermann's Blog

simplicity...that's it

Oracle Application DBA

sharing the information about realtime experience

My Oracle Notes

Emiliano Fusaglia RAC DBA/Data Architect

Johribazaar's Blog

Just another WordPress.com weblog

Ayurveda and Yoga

Site for Ayurveda and Yoga articles


Few adventures in my slow running life...


4 out of 5 dentists recommend this WordPress.com site


Technical Articles by Kanchana Selvakumar

SAP Basis Cafe

SAP Basis Cafe - Exploring SAP R/3 Basis World