-
Junior Member
Import export
How to export and import database for a particular user or schema ?
-
Expert Member
Re: Import export
The Oracle export (EXP) and import (IMP) utilities are used to perform logical database backup and recovery. They are also used to move Oracle data from one machine, database or schema to another.
First goto command prompt.
Check whether bin path is set correctly or not. Otherwise set the bin path.
Ex:- path = d:\ora_home\bin
Then to export data use the following syntax.
exp scott/tiger file=emp.dmp log=emp.log tables=emp indexes=no
Here scott/tiger are username and password from which data is to be exported.
File is the dump file name. If you want to export entire database tables option is not required. If you want to export a particular table then give tables= tablename.
To import the data use the following syntax
imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept full =n
To import entire dump set full = y. To import a particular table, specify the table name then set full =n.
To get help regarding IMP and EXP go to BIN path of orahome then type
EXP HELP = YES
-
Junior Member
Re: Import export
To import the full database
-----------------------------
imp SYSTEM/password FULL=y FIlE=dba.dmp
To import just the dept and emp tables from the scott schema
-----------------------------
imp SYSTEM/password FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp)
To import tables and change the owner
-----------------------------
imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager)
To import just the scott schema
-----------------------------
imp / FIlE=scott.dmp
To export the entire database to a single file dba.dmp in the current directory.
-----------------------------
- Login to server
exp SYSTEM/password FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y
or
exp SYSTEM/password PARFILE=params.dat
where params.dat contains the following information
FILE=dba.dmp
GRANTS=y
FULL=y
ROWS=y
LOG=dba.log
To dump a single schema to disk (we use the scott example schema here)
----------------------------
- Login to server which has an Oracle client
exp / FIlE=scott.dmp OWNER=scott
To export specific tables to disk.
----------------------------
- Login to server which has an Oracle client
exp SYSTEM/password FIlE=expdat.dmp TABLES=(scott.emp,hr.countries)
-the above command uses two users : scott and hr
exp / FILE=scott.dmp TABLES=(emp,dept)
the above is only for one user
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules