1.

from one user to another user

2.from one database to another database
3.from one o/s to another o/s as export files are platform independent
4.from one version to another version.
Using exp:

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 <user>/<password> 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 <user>/<password> FILE=scott.dmp TABLES=(emp,dept)
the above is only for one user

Using imp:

To import the full database exported in the example above.

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 exported in the example above

imp <user>/<password> FIlE=scott.dmp

Example Export Sessions
This section provides examples of the following types of Export
sessions:

 Example Export Session in Full Database Mode

 Example Export Session in User Mode

 Example Export Sessions in Table Mode

 Example Export Session Using Partition-Level Export

In each example, you are shown how to use both the command-line
method and the parameter file method. Some examples use vertical
ellipses to indicate sections of example output that were too long to
include.

Example Export Session in Full Database Mode

Only users with the DBA role or the EXP_FULL_DATABASE role can
export in full database mode. In this example, an entire database is
exported to the file dba.dmp with all GRANTS and all data.

Parameter File Method

> exp PARFILE=params.dat

The params.dat file contains the following information:

FILE=dba.dmp
GRANTS=y
FULL=y
ROWS=y

Command-Line Method
> exp FULL=y FILE=dba.dmp GRANTS=y ROWS=y

Export Messages
Information is displayed about the release of Export you are using and
the release of Oracle Database that you are connected to. Status
messages are written out as the entire database is exported. A final
completion message is returned when the export completes
successfully, without warnings.

Example Export Session in User Mode

User-mode exports can be used to back up one or more database

users. For example, a DBA may want to back up the tables of deleted
users for a period of time. User mode is also appropriate for users who
want to back up their own data or who want to move objects from one
owner to another. In this example, user scott is exporting his own
tables.

Parameter File Method

> exp scott/tiger PARFILE=params.dat

The params.dat file contains the following information:

FILE=scott.dmp
OWNER=scott
GRANTS=y
ROWS=y
COMPRESS=y

Command-Line Method
> exp scott/tiger FILE=scott.dmp OWNER=scott GRANTS=y
ROWS=y COMPRESS=y

Export Messages
Information is displayed about the release of Export you are using and
the release of Oracle Database that you are connected to. Then, status
messages similar to the following are shown:

.
.
. about to export SCOTT's tables via Conventional
Path ...
. . exporting table BONUS
0 rows exported
. . exporting table DEPT
4 rows exported
. . exporting table EMP
14 rows exported
. . exporting table SALGRADE
5 rows exported
.
.
.
Export terminated successfully without warnings.

Example Export Sessions in Table Mode

In table mode, you can export table data or the table definitions. (If no
rows are exported, the CREATE TABLE statement is placed in the
export file, with grants and indexes, if they are specified.)

A user with the EXP_FULL_DATABASE role can use table mode to

export tables from any user's schema by specifying
TABLES=schemaname.tablename.

If schemaname is not specified, Export defaults to the exporter's

schema name. In the following example, Export defaults to the SYSTEM
schema for table a and table c:

> exp TABLES=(a, scott.b, c, mary.d)

A user with the EXP_FULL_DATABASE role can also export dependent

objects that are owned by other users. A nonprivileged user can export
only dependent objects for the specified tables that the user owns.

Exports in table mode do not include cluster definitions. As a result, the

data is exported as unclustered tables. Thus, you can use table mode
to uncluster tables.

Example 1: DBA Exporting Tables for Two Users

In this example, a DBA exports specified tables for two users.

Parameter File Method

> exp PARFILE=params.dat

The params.dat file contains the following information:

FILE=expdat.dmp
TABLES=(scott.emp,blake.dept)
GRANTS=y
INDEXES=y

Command-Line Method
> exp FILE=expdat.dmp TABLES=(scott.emp,blake.dept)
GRANTS=y INDEXES=y

Export Messages
Information is displayed about the release of Export you are using and
the release of Oracle Database that you are connected to. Then, status
messages similar to the following are shown:

.
.
.
About to export specified tables via Conventional
Path ...
Current user changed to SCOTT
. . exporting table EMP
14 rows exported
Current user changed to BLAKE
. . exporting table DEPT
8 rows exported
Export terminated successfully without warnings.

Example 2: User Exports Tables That He Owns

In this example, user blake exports selected tables that he owns.

Parameter File Method

> exp blake/paper PARFILE=params.dat

The params.dat file contains the following information:

FILE=blake.dmp
TABLES=(dept,manager)
ROWS=y
COMPRESS=y
Command-Line Method
> exp blake/paper FILE=blake.dmp TABLES=(dept, manager)
ROWS=y COMPRESS=y

Export Messages
Information is displayed about the release of Export you are using and
the release of Oracle Database that you are connected to. Then, status
messages similar to the following are shown:

.
.
.

About to export specified tables via Conventional

Path ...
. . exporting table DEPT
8 rows exported
. . exporting table MANAGER
4 rows exported
Export terminated successfully without warnings.

Example 3: Using Pattern Matching to Export Various Tables

In this example, pattern matching is used to export various tables for

users scott and blake.

Parameter File Method

> exp PARFILE=params.dat

The params.dat file contains the following information:

FILE=misc.dmp
TABLES=(scott.%P%,blake.%,scott.%S%)

Command-Line Method
> exp FILE=misc.dmp TABLES=(scott.%P%,blake.%,scott.%S
%)

Export Messages
Information is displayed about the release of Export you are using and
the release of Oracle Database that you are connected to. Then, status
messages similar to the following are shown:

.
.
.
About to export specified tables via Conventional
Path ...
Current user changed to SCOTT
. . exporting table DEPT
4 rows exported
. . exporting table EMP
14 rows exported
Current user changed to BLAKE
. . exporting table DEPT
8 rows exported
. . exporting table MANAGER
4 rows exported
Current user changed to SCOTT
. . exporting table BONUS
0 rows exported
. . exporting table SALGRADE
5 rows exported
Export terminated successfully without warnings.

Example Export Session Using Partition-Level Export

In partition-level Export, you can specify the partitions and

subpartitions of a table that you want to export.

Example 1: Exporting a Table Without Specifying a Partition

Assume emp is a table that is partitioned on employee name. There are

two partitions, m and z. As this example shows, if you export the table
without specifying a partition, all of the partitions are exported.

Parameter File Method

> exp scott/tiger PARFILE=params.dat

The params.dat file contains the following:

TABLES=(emp)
ROWS=y

Command-Line Method
> exp scott/tiger TABLES=emp rows=y

Export Messages
Information is displayed about the release of Export you are using and
the release of Oracle Database that you are connected to. Then, status
messages similar to the following are shown:

.
.
.
About to export specified tables via Conventional
Path ...
. . exporting table EMP
. . exporting partition M
8 rows exported
. . exporting partition Z
6 rows exported
Export terminated successfully without warnings.

Example 2: Exporting a Table with a Specified Partition

Assume emp is a table that is partitioned on employee name. There are

two partitions, m and z. As this example shows, if you export the table
and specify a partition, only the specified partition is exported.

Parameter File Method

> exp scott/tiger PARFILE=params.dat

The params.dat file contains the following:

TABLES=(emp:m)
ROWS=y

Command-Line Method
> exp scott/tiger TABLES=emp:m rows=y
Export Messages
Information is displayed about the release of Export you are using and
the release of Oracle Database that you are connected to. Then, status
messages similar to the following are shown:

.
.
.
About to export specified tables via Conventional
Path ...
. . exporting table EMP
. . exporting partition M
8 rows exported
Export terminated successfully without warnings.

Example 3: Exporting a Composite Partition

Assume emp is a partitioned table with two partitions, m and z. Table

emp is partitioned using the composite method. Partition m has
subpartitions sp1 and sp2, and partition z has subpartitions sp3 and
sp4. As the example shows, if you export the composite partition m,
all its subpartitions (sp1 and sp2) will be exported. If you export the
table and specify a subpartition (sp4), only the specified subpartition is
exported.

Parameter File Method

> exp scott/tiger PARFILE=params.dat

The params.dat file contains the following:

TABLES=(emp:m,emp:sp4)
ROWS=y

Command-Line Method
> exp scott/tiger TABLES=(emp:m, emp:sp4) ROWS=y

Export Messages
Information is displayed about the release of Export you are using and
the release of Oracle Database that you are connected to. Then, status
messages similar to the following are shown:

.
.
.
About to export specified tables via Conventional
Path ...
. . exporting table EMP
. . exporting composite partition M
. . exporting subpartition SP1
1 rows exported
. . exporting subpartition SP2
3 rows exported
. . exporting composite partition Z
. . exporting subpartition SP4
1 rows exported
Export terminated successfully without warnings.

Example Import Sessions

This section gives some examples of import sessions that show you
how to use the parameter file and command-line methods. The
examples illustrate the following scenarios:

 Example Import of Selected Tables for a Specific User

 Example Import of Tables Exported by Another User

 Example Import of Tables from One User to Another

 Example Import Session Using Partition-Level Import

 Example Import Using Pattern Matching to Import Various Tables

Example Import of Selected Tables for a Specific User

In this example, using a full database export file, an administrator

imports the dept and emp tables into the scott schema.

Parameter File Method

> imp PARFILE=params.dat
The params.dat file contains the following information:

FILE=dba.dmp
SHOW=n
IGNORE=n
GRANTS=y
FROMUSER=scott
TABLES=(dept,emp)

Command-Line Method
> imp FILE=dba.dmp FROMUSER=scott TABLES=(dept,emp)

Import Messages
Information is displayed about the release of Import you are using and
the release of Oracle Database that you are connected to. Status
messages are also displayed.

Example Import of Tables Exported by Another User

This example illustrates importing the unit and manager tables from
a file exported by blake into the scott schema.

Parameter File Method

> imp PARFILE=params.dat

The params.dat file contains the following information:

FILE=blake.dmp
SHOW=n
IGNORE=n
GRANTS=y
ROWS=y
FROMUSER=blake
TOUSER=scott
TABLES=(unit,manager)

Command-Line Method
> imp FROMUSER=blake TOUSER=scott FILE=blake.dmp
TABLES=(unit,manager)
Import Messages
Information is displayed about the release of Import you are using and
the release of Oracle Database that you are connected to. Status
messages are also displayed.

Example Import of Tables from One User to Another

In this example, a database administrator (DBA) imports all tables

belonging to scott into user blake's account.

Parameter File Method

> imp PARFILE=params.dat

The params.dat file contains the following information:

FILE=scott.dmp
FROMUSER=scott
TOUSER=blake
TABLES=(*)

Command-Line Method
> imp FILE=scott.dmp FROMUSER=scott TOUSER=blake
TABLES=(*)

Import Messages
Information is displayed about the release of Import you are using and
the release of Oracle Database that you are connected to. Then, status
messages similar to the following are shown:

.
.
.
Warning: the objects were exported by SCOTT, not by you

import done in WE8DEC character set and AL16UTF16 NCHAR

character set
. importing SCOTT's objects into BLAKE
. . importing table "BONUS"
0 rows imported
. . importing table "DEPT"
4 rows imported
. . importing table "EMP"
14 rows imported
. . importing table "SALGRADE"
5 rows imported
Import terminated successfully without warnings.

Example Import Session Using Partition-Level Import

This section describes an import of a table with multiple partitions, a

table with partitions and subpartitions, and repartitioning a table on
different columns.

Example 1: A Partition-Level Import

In this example, emp is a partitioned table with three partitions: P1, P2,
and P3.

A table-level export file was created using the following command:

> exp scott/tiger TABLES=emp FILE=exmpexp.dat ROWS=y

Export Messages
Information is displayed about the release of Export you are using and
the release of Oracle Database that you are connected to. Then, status
messages similar to the following are shown:

.
.
.
About to export specified tables via Conventional
Path ...
. . exporting table EMP
. . exporting partition P1
7 rows exported
. . exporting partition P2
12 rows exported
. . exporting partition P3
3 rows exported
Export terminated successfully without warnings.
In a partition-level Import you can specify the specific partitions of an
exported table that you want to import. In this example, these are P1
and P3 of table emp:

> imp scott/tiger TABLES=(emp:p1,emp:p3)

FILE=exmpexp.dat ROWS=y

Import Messages
Information is displayed about the release of Import you are using and
the release of Oracle Database that you are connected to. Status
messages are also displayed.

Example 2: A Partition-Level Import of a Composite Partitioned

Table

This example demonstrates that the partitions and subpartitions of a

composite partitioned table are imported. emp is a partitioned table
with two composite partitions: P1 and P2. Partition P1 has three
subpartitions: P1_SP1, P1_SP2, and P1_SP3. Partition P2 has two
subpartitions: P2_SP1 and P2_SP2.

A table-level export file was created using the following command:

> exp scott/tiger TABLES=emp FILE=exmpexp.dat ROWS=y

Export Messages
Information is displayed about the release of Export you are using and
the release of Oracle Database that you are connected to. Then, status
messages similar to the following are shown:

When the command executes, the following Export messages are

displayed:

.
.
.
About to export specified tables via Conventional
Path ...
. . exporting table EMP
. . exporting composite partition P1
. . exporting subpartition P1_SP1
2 rows exported
. . exporting subpartition P1_SP2
10 rows exported
. . exporting subpartition P1_SP3
7 rows exported
. . exporting composite partition P2
. . exporting subpartition P2_SP1
4 rows exported
. . exporting subpartition P2_SP2
2 rows exported
Export terminated successfully without warnings.

The following Import command results in the importing of subpartition

P1_SP2 and P1_SP3 of composite partition P1 in table emp and all
subpartitions of composite partition P2 in table emp.

> imp scott/tiger TABLES=(emp:p1_sp2,emp:p1_sp3,emp:p2)

FILE=exmpexp.dat ROWS=y

Import Messages
Information is displayed about the release of Import you are using and
the release of Oracle Database that you are connected to. Then, status
messages similar to the following are shown:

.
.
.
. importing SCOTT's objects into SCOTT
. . importing subpartition "EMP":"P1_SP2"
10 rows imported
. . importing subpartition "EMP":"P1_SP3"
7 rows imported
. . importing subpartition "EMP":"P2_SP1"
4 rows imported
. . importing subpartition "EMP":"P2_SP2"
2 rows imported
Import terminated successfully without warnings.

Example 3: Repartitioning a Table on a Different Column

This example assumes the emp table has two partitions based on the
empno column. This example repartitions the emp table on the deptno
column.
Perform the following steps to repartition a table on a different column:

1. Export the table to save the data.

2. Drop the table from the database.

3. Create the table again with the new partitions.

4. Import the table data.

The following example illustrates these steps.

> exp scott/tiger table=emp file=empexp.dat

.
.
.

About to export specified tables via Conventional

Path ...
. . exporting table EMP
. . exporting partition EMP_LOW
4 rows exported
. . exporting partition EMP_HIGH
10 rows exported
Export terminated successfully without warnings.

SQL> connect scott/tiger

Connected.
SQL> drop table emp cascade constraints;
Statement processed.
SQL> create table emp
2 (
3 empno number(4) not null,
4 ename varchar2(10),
5 job varchar2(9),
6 mgr number(4),
7 hiredate date,
8 sal number(7,2),
9 comm number(7,2),
10 deptno number(2)
11 )
12 partition by range (deptno)
13 (
14 partition dept_low values less than (15)
15 tablespace tbs_1,
 16 partition dept_mid values less than (25)
17 tablespace tbs_2,
18 partition dept_high values less than (35)
19 tablespace tbs_3
20 );
Statement processed.
SQL> exit

> imp scott/tiger tables=emp file=empexp.dat ignore=y

.
.
.
import done in WE8DEC character set and AL16UTF16 NCHAR
character set
. importing SCOTT's objects into SCOTT
. . importing partition "EMP":"EMP_LOW"
4 rows imported
. . importing partition "EMP":"EMP_HIGH"
10 rows imported
Import terminated successfully without warnings.

The following SQL SELECT statements show that the data is partitioned
on the deptno column:

SQL> connect scott/tiger

Connected.
SQL> select empno, deptno from emp partition
(dept_low);
EMPNO DEPTNO
---------- ----------
7782 10
7839 10
7934 10
3 rows selected.
SQL> select empno, deptno from emp partition
(dept_mid);
EMPNO DEPTNO
---------- ----------
7369 20
7566 20
7788 20
7876 20
7902 20
5 rows selected.
SQL> select empno, deptno from emp partition
(dept_high);
EMPNO DEPTNO
---------- ----------
7499 30
7521 30
7654 30
7698 30
7844 30
7900 30
6 rows selected.
SQL> exit;

Example Import Using Pattern Matching to Import Various

Tables

In this example, pattern matching is used to import various tables for

user scott.

Parameter File Method

imp PARFILE=params.dat

The params.dat file contains the following information:

FILE=scott.dmp
IGNORE=n
GRANTS=y
ROWS=y
FROMUSER=scott
TABLES=(%d%,b%s)

Command-Line Method
imp FROMUSER=scott FILE=scott.dmp TABLES=(%d%,b%s)