How to import and export Oracle Data in LINUX

How to import and export Oracle Data in LINUX

This article describes how to import and export Oracle Data in LINUX. We will share this with you for your reference. The details are as follows:

1. Export tool exp

1. It is the directory for storing the next executable file in the operating system/ORACLE_HOME/bin

The exp export tool compresses data backup in the database into a binary system file, which can be migrated between different operating systems.

It has three modes:

A. User Mode: export data of all user objects and objects;
B. Table mode: export all or specified tables of the user;
C. entire database: export all objects in the database.

2. Example of using the export tool exp interactive command line:

$ Exp test/test123 @ appdb
Enter array fetch buffer size: 4096> press Enter
Export file: expdat. dmp> m. dmp generate exported file name
(1) E (ntire database), (2) U (sers), or (3) T (ables): (2) U> 3
Export table data (yes/no): yes> press ENTER
Compress extents (yes/no): yes> press ENTER
Export done in ZHS16GBK character set and ZHS16GBK NCHAR character set
About to export specified tables via Conventional Path...
Table (T) or Partition (T: P) to be exported: (RETURN to quit)> name of the Table to be exported by cmamenu
.. Exporting table CMAMENU 4336 rows exported
Table (T) or Partition (T: P) to be exported: (RETURN to quit)> name of the Table to be exported n
Table (T) or Partition (T: P) to be exported: (RETURN to quit)> press ENTER
Export terminated successfully without warnings.

Export command:Copy codeThe Code is as follows: exp test/test123 @ orcl file =/opt/daochu. dmp full = y

Test indicates the user name, test123 indicates the user password, orcl indicates the database name, and file indicates the url of the file to be exported.

Ii. Import tool imp

1. It is the directory for storing the next executable file in the operating system/ORACLE_HOME/bin

The imp import tool imports binary system files generated by EXP into the database.

It has three modes:

A. User Mode: import all user objects and data in objects;
B. Table mode: import all or specified tables of the user;
C. entire database: import all objects in the database.

Only users with IMP_FULL_DATABASE and DBA permissions can import the entire database.

Imp steps:

(1) create table (2) insert data (3) create index (4) create triggers, constraints

2. Import tool imp interactive command line method example:

$ Imp
Import: Release 8.1.6.0.0-Production on Friday December 7 17:01:08 2001
(C) Copyright 1999 Oracle Corporation. All rights reserved.
User name: test
Password :****
Connect to: Oracle8i Enterprise Edition Release 8.1.6.0.0-64bit Production
With the Partitioning option
JServer Release 8.1.6.0.0-Production
Import file: expdat. dmp>/tmp/m. dmp
Input buffer size (minimum 8192) 30720>
EXPORT files created by EXPORT: V08.01.06 in the normal path
Warning: This object is exported by TEST instead of the current user.
The ZHS16GBK Character Set and ZHS16GBK NCHAR character set have been imported.
Only list the content of the imported file (yes/no): no>
The creation error is ignored because the object already exists (yes/no): no> yes
Import Permission (yes/no): yes>
Import table data (yes/no): yes>
Import the entire exported file (yes/no): no> yes
. Importing the TEST object to SCOTT.
.. Importing table "CMAMENU" 4336 rows being imported
Import is terminated successfully, but a warning is displayed.

Import command:Copy codeThe Code is as follows: $ imp test/test123 @ orcl file =/opt/whufe. dmp full = y

Here, test is the user name, the last test123 is the user password, orcl is the database name, and file is the url that stores the file to be imported.

I hope this article will help you with Oracle database programming.

Articles you may be interested in:

• Oracle Database Password Reset, Import and Export command example application
• Two Methods for oracle Database Export and oracle Data Import (oracle Data Import and Export)
• How to use oracle Database Import and Export commands
• Considerations and experiences for oracle Import and Export
• Introduction to oracle Data Pump Import and Export
• Oracle Database Export (exp) Import (imp) Description