Exporting Tables in Oracle

Exporting Single Table :-

•  First create Directory at OS and DB level on Source and Target Machine and then start export and import.

[oracle@bab fiexp]$ expdp system/manager dumpfile=f.dmp directory=fiexp tables=sam.emp

• Copy .dmp to target  Location by using SCP or OS Team will copy this .dmp file to your target location.

Now Start Importing Single Table:-

•  We are using here Remap parameter if we use remap option oracle will change source directory name to new name like on source our emp table is in  SAM schema and we are importing into BAB schema and also it will rename your table.

[oracle@bab ~]$ impdp system/manager dumpfile=f.dmp directory=fiimp remap_schema=bab:emphr


                                                                  *****************

SCHEMA LEVEL EXPORT:- 

• It exports objects of particular schema and on the fly  it creates new schema at target machine.

[oracle@bab fiexp]$ expdp system/manager dumpfile=schema.dmp directory=fiexp schemas=ots

[oracle@bab fiimp]$ impdp system/manager directory=fiimp dumpfile=schema.dmp  remap_schema=ots:jld

                                                                 *****************

TABLESPACE LEVEL:-

• Create Directories at OS level and Database level with read, write permissions.
• Create tablespace with same name at target machine as source.
• Create same users at target machine with minimum connect, resource privileges.

[oracle@bab tsexp]$ expdp system/manager dumpfile=pts.dmp directory=tsexp tablespaces=prodtsl

• Scp .dmp file to target.

[oracle@akram tsimp]$ impdp system/manager dumpfile=pts.dmp directory=tsimp remap_tablespace=prodts:prodts

Note:-

To import at target side Target tablespace name can be diff but users should be same.

Happy Learning ....