Tuesday, January 27, 2009

How to Import and Export Database dump file in Oracle.

After installing oracle, we can find imp.exe and exp.exe files in bin folder.
These exe files could help for importing and exporting database dump.

This database dump file consists of tables (with or without data), database objects (Stored procedures, Views, Triggers, Constraints, Indexes, Sequences, etc…).

While exporting or importing, we have an option whether to consider table data or not.
So situations, like where we are just in need of only schema it helps to export or import without data. We also have an option for importing or exporting selected tables, i.e; we can mention a list of tables which we require to export or import.

So these exp and imp exe files have input parameters which are set depending upon need.
To know the parameters of imp, say in command prompt as
Imp help=y




Similarly for exp say,
Exp help=y



We can find imp and exp files almost expect the same kind of parameters.

Example:-
If I have a database whose connection string is
Demo/abcd@MyDB
(User name: Demo
Password: abcd
Schema: MyDB)
If I need to export I can go to command prompt and say “exp”, then it asks me for username where I have to give Demo/abcd@MyDB.
After that it asks for the file path where dump has to be exported, Export table data or not.




or I can say
exp Demo/abcd@MyDB file=[dumpfile export path] rows=(Y or N) log=[log file path]
This directly connects to the database and exports the database objects to the specific dump file path mentioned. We can find a log file at given Log file path, to just verify if there are any errors while exporting.
If we want only selected tables to be exported we can say
exp Demo/abcd@MyDB file=[dumpfile export path] rows=(Y or N) log=[log file path] tables=(table1, table2, tables3, …tablen)

Also for importing a database dump file, I should say imp in command prompt.
Say, if I am willing to import a database dump file to Demo\abcd@MyDemo,
Then I should give username as Demo\abcd@MyDemo, wheimp.exe prompts for.
Then it asks for the database dump file where it is to export.
We can either give the path or drag and drop the dump file.




or
I can say
Imp Demo\abcd@MyDemo file=[import databse dump file path] log=[log file path] rows=Y tables=(table1, table2.. tablen).
So import and export process seems to be almost same.

No comments: