Friday, October 24, 2014

how to export database in oracle?

Export schemas(table,view,trigger,cursor etc.) from Oracle:

There are two ways to export shcemas.
 1. Using Directory
 2. Without using Directory

1. Using Directory:-
Step i.
  Create a folder at first in any drive.

Step ii. 
  Open SQL prompt and create a directory using given syntax:
   SQL> create directory directory-name as folder-name
 where directory-name is any name and folder-name must be what you've created in step 1.

Step iii. 
 Exit from sql prompt and write export command at command prompt using given synatx:
 c:\> expdp User/password dumpfile=file-name.dmp directory=directory-name schemas=User
 where User is user-name with export priviledge, password is the password for that user, dump file name is any file name with extension .dmp, directory-name is the same name what you created in step ii. and schemas user is for which you want to export schemas(tables, views, function etc.)  


Here is the exported dump file:

2. Without using Directory:

 Remove (directory=directory-name) attribute from step iii above. So, syntax would be like this:
  C:\>expdp user/password dumpfile=dump-file-name schemas=User
This time the dump file location would be default location of oracle. I'm using 10g express edition so my default file location is 'c:\oraclexe\app\oracle\admin\xe\dpdump\dump-file.dmp'. Default location may vary according to version. On command prompt see the 2nd line from last. This is your default dump-file location.