Oracle Export, Import using sqlplus

I will write here about oracle export,import using sqlplus. I assumed that you have basic idea about oracle, sql.

There may situation like follows:-

• You want to copy some tables from one schema to another schema
• You want to copy a schema into another database
• You want to copy table rows from one schema to another schema

You can do this using oracle export, import facilities. These two facilities are really powerful to smooth data transfer. Usually I do this using sqlplus command facilities.

ExportProcess :
At first you need to create dump file. Enter command prompt then type exp in the prompt. It will ask for userid and password of the schema. Suppose your schema is ab and listener name is biis and password is ab. Then type mak@iis and then password makiis.


Then choose the dump file location. You may choose default directory by just entering file name or choose any other location say c:\\ab.dmp. The dump file will be stored in C drive. There are some dump options that is

1. dump full database
2. dump a user
3. dump tables

Here we dump all object(tables,grants,constraints,function,procedures,view,materialized view etc) under user ab. sqlplus will ask for U for user, T for tables and E for entire database. Then enter other options such as export grants, export table data, compress extend etc. After entering all the options export will start and if you see a message at the end stating that "Export terminated successfully without warnings" then dump is 100% successful.

Import Process:
Now you import all objects of ab to another user say xyz. For this purpose

1. First create the user xyz.
2. Then enter in cammand prompt and type imp.
3. Then type username and password of the user on which you want to import objects.
4. Then type location of dump file (c:\\ab.dump)
5. Enter dump options

All objects from ab to xyz will be imported successfully.


I accepted the default dump options for import and export.

For any queries plz contact me at khaer_mohammad@yahoo.com