How to import and export Oracle Data in Linux

When Oracle 11g is exported using export, the empty table cannot export 11g R2 with a new feature. When the table does not have data, no segment is allocated to save space.

Solution:

1. Insert a row and then roll back to generate a segment. This method inserts data into an empty table and then deletes it, resulting in a segment. An empty table can be exported.

2. Set the deferred_segment_creation parameter. The default value of this parameter is true. When it is set to false, segment is allocated to both empty tables and non-empty tables.

Modify the SQL statement: Alter system set deferred_segment_creation = false scope = both;

It should be noted that the value setting does not affect the empty tables previously imported and cannot be exported. It can only be used for the newly added tables. To export an empty table, you can only use the first method.

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.

3. Example of export tool exp in non-interactive Command Line Mode

$ Exp Scott/tiger tables = (EMP, Dept) file =/directory/Scott. dmp grants = y

Note: The EMP and dept tables in the Scott user are exported to the file/directory/Scott. dmp.

$ Exp Scott/tiger tables = EMP query = \ "where job = \ 'salesman \ 'and Sal \ <1600 \" file =/directory/scott2.dmp

Note: add the query condition job = 'salesman' and Sal for exporting EMP to exp <1600

(But I am rarely using this method. It is more convenient to generate a temporary table for the records meeting the conditions, and then use exp)

$ Exp parfile = username. Par file =/directory1/username_1.dmp,/directory1/username_2.dmp filesize = 2000 m log =/directory2/username_exp.log

Parameter file username. Par content
Userid = username/userpassword
Buffer= 8192000
Compress = N
Grants = y

Description: username. Par is the parameter file used for exporting tool exp. The specific parameters can be modified as needed.

Filesize specifies the maximum number of bytes of the generated binary backup file

(It can be used to solve the limitation of 2 GB physical files in some operating systems, accelerate the compression speed, and facilitate the engraving of historical data CDs)

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

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.

3. Import tool imp non-interactive command line method example

$ Imp system/manager fromuser = Jones tables = (accts)
$ Imp system/manager fromuser = Scott tables = (EMP, Dept)
$ Imp system/manager fromuser = Scott touser = Joe tables = EMP
$ Imp Scott/tiger file = expdat. dmp full = y
$ Imp Scott/tiger file =/mnt1/t1.dmp show = n buffer = 2048000 ignore = n commit = y grants = y full = y log =/oracle_backup/log/imp_scott.log
$ Imp system/manager parfile = Params. dat
Params. dat content
File = DBA. dmp show = n ignore = n grants = y fromuser = Scott tables = (Dept, EMP)

4. Problems with the import tool imp

(1) The database object already exists.
Generally, tables, sequences, functions/processes, and triggers under the target data should be completely deleted before data is imported;
The database object already exists. If you use the default imp parameter, the Import fails.
If the ignore = y parameter is used, the data content in the exp file will be imported.
If the table has a constraint that contains a unique keyword, the table will not be imported if the condition is not met.
If the table does not have a constraint for a unique keyword, record duplication occurs.

(2) database objects are subject to primary and foreign key constraints
Data Import fails if it does not comply with the primary and foreign key constraints.
Solution: import the dependency table first.
The primary and foreign key constraints of the disable object to be imported. After the data is imported, enable them
(3) Insufficient Permissions
If you want to import user a's data to user B, user a must have the imp_full_database permission.

(4) failed to allocate storage when importing large tables (greater than 80 m)
Compress = Y for the default exp, that is, compress all data into one data block.
If there is no continuous big data block during import, the import will fail.
When exporting a large table larger than 80 Mb, remember to compress = n, and this will not cause this error.

(5) imp and exp use different character sets.
If the character set is different, the import will fail. You can change the Unix environment variable or the information about nls_lang in the NT Registry.
After the import is complete, change it back.

(6) imp and exp versions cannot be compatible
IMP can successfully import files generated by earlier exp versions. Files generated by later exp versions cannot be imported.
We can use
$ Imp username/password @ connect_string
Connect_string is in/ORACLE_HOME/Network/admin/tnsnames. ora
Name of the defined local or remote database
Note:
UNIX:/etc/hosts to define the Host Name of the local or remote database server
Win98: Relationship between Windows \ hosts and IP address

Win2000: winnt \ system32 \ drivers \ etc \ hosts

(Import backup) imp WQ/summit file =/home/Oracle/0730.dmp fromuser = WQ (this is the username of Oracle at the time of data export) Ignore = y

(Export backup) exp WQ/summit file =/home/Oracle/0730.dmp owner = WQ

1. Export command:

Exp username/password @ database Sid

Then, follow the prompts to answer questions such as the target file, data table, and data.

2. Import command:

IMP username/password @ database Sid

You can also answer some questions as prompted.