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.)  

 Example:-

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:
 Syntax:
  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.

Example: