Export Tips

Export a schema or user with all data

$exp username/password file=exp_schema.dmp owner=schema buffer=1024000 log=exp_schema.log

Note: Replace "schema" with actual schema name or username

---------------------------------------------------------------------------------------------------------------------------------

Export two tables of a particular user with data

$exp username/password file=exp_tables.dmp tables=T1,T2 buffer=1024000 log=exp_tables.log

Note: T1, T2 are table names

---------------------------------------------------------------------------------------------------------------------------------

Export a full database with data

$exp system/manager file=full_exp_db.dmp full=y buffer=1024000 log=full_exp_db.log

Export full database without data

$exp system/manager file=full_exp_db.dmp full=y rows=N log=full_exp_db.log

---------------------------------------------------------------------------------------------------------------------------------

Exporting database, when ordinary database export file exceeds 2GB in size

  1. $mknod /tmp/exp_pipe_dbname p

    2. (make a unix pipe)

  2. $ compress exp_dbname.dmp.Z &

    3. (Compress the expot file from unix pipe file )

  3. $ exp username/password file=/emp/exp_pipe_dbname

(Start exporting database)

---------------------------------------------------------------------------------------------------------------------------------

Export a partition table "PT" with all partitions and data

$exp username/password file=exp_PT.dmp fromuser=username tables=PT

---------------------------------------------------------------------------------------------------------------------------------

Export partitions PART1, PART2 of a partition table PT

$exp username/password file=exp_p12_table.dmp tables=(PT:PART1,PT:PART2) rows=y

 

---------------------------------------------------------------------------------------------------------------------------------

Exporting to Tape device

$exp username/password file=/dev/rmt/0 full=y

---------------------------------------------------------------------------------------------------------------------------------

 

Export to Tape device by specifying tape size

IF tape size is 1B and your export file is 2B, then specify a size less than 2GB as VOLSIZE, once the VOLSIZE is reached, system will prompt for next tape.

$exp username/password full=y file=/dev/rmt/0 volsize=900M

---------------------------------------------------------------------------------------------------------------------------------

Export to Tape deive using unix pipes

$mknod exp_db_pipe p

$dd if=exp_db_pipe of=/dev/rmt/0

$ exp username/password file=exp_db_pipe full=y

---------------------------------------------------------------------------------------------------------------------------------

 Export table from V8.x database to v7.x database

  1. Run catexp7.sql against V8.x database.

    2. [ catexp7.sql is available at V8.x $ORACLE_HOME/rdbms/admin directory ]

  2. Using V7.x export utility do export of table from V8.x database
  3. Using V7.x import utility import the table into V7.x database

[ imp Yusername/password@database_name file=Imp_table_X.dmp fromuser=Xuser touser=Yusername  tables=table_X ignore=y ]

 

---------------------------------------------------------------------------------------------------------------------------------

  Export Metadata

    $ exp FILE=exp_tablespace.dmp TRANSPORT_TABLESPACE=Y TABLESPACES=tablespace_name TRIGGERS=N CONSTRAINTS=N

    ---------------------------------------------------------------------------------------------------------------------------------