How to export and import Oracle Database using command prompt?

First thing you should do is change the tnsnames.ora files with the code below. This .ora file is under directory \client_1\NETWORK\ADMIN\


conn1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP_Address)(PORT = 1521))
)
(CONNECT_DATA =
(SID = XE)
or (SERVICE_NAME = conn1)
)
)


then save the file and open the command prompt (windows key + R, type cmd).

Export:

To export, type this command in cmd:
>> exp user/password@instant
It will show many questions, leave it with default answer using hit enter. But, there are the important question, such as:
- (2)U(sers), or (3)T(ables): (2)U >
2 : to export all database under user that mentioned above
3 : to export selected tables only. If 3 choosed, it will appear question what tables that will be exported
- Export grants (yes/no): yes > (type Y or N)
- Export table data (yes/no): yes > (type Y or N)
- Compress extents (yes/no): yes > (type Y or N)

In the rest, make sure that appear message Export terminated successfully without warnings.

Import:

To import full dmp file type this command in cmd:
>> imp user/password@instant file=emp.dmp log=imp.log full=yes

To import dept table alone type this command in cmd:
>> imp user/password@instant file=emp.dmp log=imp.log fromuser=user touser=user tables=dept

FROMUSER and TOUSER clause are optional. But in case if used, then create a user in the destination database that is mentioned in TOUSER clause then try importing the dmp file.

In the rest, make sure that appear message Export terminated successfully without warnings.
If there are still warnings, please check the dblink connection. If it has a problem connection with dblink, drop the current dblink (result of importing) and create new dblink with simple new name.

Advertisement

Filed under: Oracle, Export and Import Oracle Database