Oracle exp/imp command quick import and export data

Export the table structure with the EXP command and do not export the table data. Simply add a parameter rows=n to the command line. Indicates that table data is not exported.

Exp username/pwd@sid file=d:databak.dmp owner= (user) Rows=n

*************************************************************************************************************** *****

Oracle EXP/IMP Command detailed

E:>exp help=y

By entering the EXP command and user name/password, you can
Commands after user/password:

Example: EXP Scott/tiger

Alternatively, you can control how the export runs by entering the EXP command with various parameters.
To specify a parameter, you can use the keyword:

Format: EXP keyword=value or keyword= (value1,value2,..., Valuen)
Example: EXP scott/tiger grants=y tables= (emp,dept,mgr)
or tables= (T1:P1,T1:P2), if T1 is a partitioned table

USERID must be the first parameter in the command line.

Keyword description (default)
---------------------------------------------------
USERID User name/password
Full export entire file (N)
Size of buffer data buffers
Owner User Name list
File output files (expdat. DMP)
Table List of tables
COMPRESS Import a range (Y)
Length of RecordLength IO record
Grants Export Permission (Y)
Inctype Incremental Export Type
INDEXES Export Index (Y)
Record tracking incremental export (Y)
Rows export rows of data (Y)
Parfile parameter filename
CONSTRAINTS Export Limit (Y)
Consistent Cross Table consistency
Log screen output logs file
STATISTICS Analysis Object (estimate)
Direct directly path (N)
Triggers export triggers (Y)
FEEDBACK shows the progress of each x row (0)
FILESIZE the maximum size of each dump file
QUERY Select a clause that exports a subset of the table

The following keywords are only available for use in a table space that can be transferred

Transport_tablespace export of removable tablespace metadata (N)
tablespaces List of table spaces to be transferred

E:>imp help=y

You can enter the IMP command and your username/password
Command with your username/password:

Example: IMP Scott/tiger

Alternatively, you can control "import" according to different parameters by entering IMP commands and various arguments.
To specify a parameter, you can use the keyword:

Format: IMP keyword=value or keyword= (value1,value2,..., Vlauen)
Example: IMP scott/tiger ignore=y tables= (emp,dept) full=n
or tables= (T1:P1,T1:P2), if T1 is a partitioned table

USERID must be the first parameter in the command line.

Keyword description (default)
----------------------------------------------
USERID User name/password
Full import entire file (N)
Buffer Data buffers Size
Fromuser List of all user names
File input files (expdat. DMP)
Touser List of user names
Show lists only the contents of the file (N)
Table List of tables
IGNORE Ignore creation error (N)
Length of RecordLength IO record
Grants Import permission (Y)
Inctype Incremental Import Type
INDEXES Import Index (Y)
Commit commit array Insert (N)
Rows Import data row (Y)
Parfile parameter filename
Log screen output logs file
CONSTRAINTS Import Limit (Y)
DESTROY Coverage Table space data file (N)
Indexfile writes table/index information to the specified file
Skip_unusable_indexes Skip maintenance for index not available (N)
ANALYZE execute the ANALYZE statement in the dump file (Y)
FEEDBACK shows the progress of each x row (0)
Toid_novalidate skips validation of the specified type ID
FILESIZE the maximum size of each dump file
Recalculate_statistics Recalculate Statistics (N)

The following keywords are only available for use in a table space that can be transferred
Transport_tablespace import of removable tablespace metadata (N)
Tablespaces The table space to be transferred to the database
Datafiles data files to be transferred to the database
Tts_owners a user with data in a table-space-centralized transport

***************************************************************
Use of the Oracle exp/imp Export Import Tool

A. Export tool exp

1. It is the next executable file storage directory for the operating system/oracle_home/bin

The EXP export tool compresses data backups in a database into a binary system file. Can migrate between different OS

It has three different modes:
A. User mode: Export all user objects and data in the object;
B. Table mode: Export all the user tables or specified tables;
C. Entire database: Export all objects in the database.

2. Export tool exp Interactive command line usage examples
$exp oa1/123@oracle
Connect to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-production
With the partitioning, OLAP and Data Mining options
Input array extract buffer size: 4096 >

Export file: Expdat. DMP > D:/a.dmp

(2) U (user), or (3) T (table): (2) u > 3

Export table Data (yes/no): Yes > Yes

Compressed area (yes/no): yes >

Exported ZHS16GBK character set and Al16utf16 NCHAR character set

About to export the specified table through the general path ...
Table (T) or partition (T:P) to export: (press RETURN to exit) > Pfdept

. . Exporting table Pfdept 83 rows Exported
Table (T) or partition (T:P) to export: (press RETURN to exit) >return
The export terminated successfully, but a warning appears.

3. Export tool exp Non-interactive command line example
$exp oa1/123@oracle file=d:/all.dmp grants=y

Description: Export all tables of the OA1 user to a file d:/all.dmp

$exp oa1/123@oracle tables= (pfdept,pfuser) file=d:/all.dmp grants=y

Description: Export the OA1 User two table emp,dept to the file d:/all.dmp

$exp oa1/123@oracle tables=pfdept query= "where predeptid=0745" file=d:/all.dmp

Description: Add the query condition of export pfdept to exp predeptid=0745

$exp parfile=username.par file=/directory1/username_1.dmp,/directory1/username_2.dmp Filesize=2000M log=/ Directory2/username_exp.log

Parameter file Username.par content
Userid=username/userpassword
buffer=8192000
Compress=n
Grants=y

Description: Username.par for export tool exp used in the parameter file, the specific parameters can be modified according to the need

FILESIZE Specifies the maximum number of bytes generated for binary prepared files
Two. Import Tools Imp

1. It is the next executable file storage directory for the operating system/oracle_home/bin

The Imp Import tool imports the binary system files that are formed by exp into the database.

It has three different modes:
A. User mode: Export all user objects and data in the object;
B. Table mode: Export all the user tables or specified tables;
C. Entire database: Export all objects in the database.

Only users with Imp_full_database and DBA authority can do the entire database import

Imp Step:
(1) Create TABLE (2) Insert data (3) create INDEX (4) Create triggers,constraints

2. Import Tools Imp Interactive command-line example
$ imp
import:release 10.1.0.2.0-production on Monday September 3 08:21:32 2007
Copyright (c) 1982, Oracle. All rights reserved.
Username: System
Password: * * *
Connect to: Oracle Database 10g Enterprise Edition release 10.1.0.2.0-production
with the Partitioning, OLAP and Data Mining options
Import file: expdat.dmp> d:/all.dmp
Enter the insertion buffer size (min. 8192) 30720>
by General The path exports the file created by export:v08.01.06
Warning: This object is exported by TEST instead of the current user
has finished importing in the ZHS16GBK character set and ZHS16GBK NCHAR character set
List only the contents of the import file (yes/ NO): No>
Because the object already exists, ignore the Create error (yes/no):no> Yes
Import permission (yes/no): yes>
Import table Data (yes/no): Yes>
to import the entire export file ( yes/no):no> Yes
. Importing the object of test into SCOTT
. Import Table "Cmamenu" 4336 rows were imported
successfully terminated the import, but a warning appears.
3. Import Tools Imp non-interactive command-line example

$ imp system/oracle fromuser=oa1 tables= (pfdept)
$ imp system/oracle fromuser=oa1 tables= (pfdept,pfuser)
$ imp system/oracle fromuser=oa1 Touser=system
$ imp oa1/123 file = All.dmp full=y
$ imp oa1/123 file = all.dmp show=n buffer=2048000 ignore=n commit=y grants=y full=y log=d:/.log
$ imp oa1/123 parfile=params.dat
Params.dat Content
File=dba.dmp show=n ignore=n grants=y fromuser=oa1 tables= (pfdept,pfuser)

4. Possible problems with importing tools imp

(1) The database object already exists
In general, you should delete the table, sequence, function/process, triggers, etc. before the data is imported.
The database object already exists, and the default IMP parameter will import the failed
If the parameter ignore=y is used, the data content in the exp file is imported
If the table has a constraint on a unique keyword, the condition will not be imported
If the table does not have a unique keyword constraint, it will cause the record to repeat

(2) Database objects have primary foreign key constraints
Data will fail to import if the primary foreign key constraint is not met
Workaround: Import primary table First, then import dependency table
Disable the primary foreign KEY constraint on the target import object, and then enable them after importing the data
(3) Insufficient authority
If you want to import a user's data into a B user, a user needs to have Imp_full_database permissions

(4) When importing large tables (greater than 80M), storage allocation fails
The default exp, compress = Y, is to compress all the data on a block of data.
When imported, failure is imported if there is no contiguous large block of data.
When you export a large table above 80M, remember compress= N, this error is not caused.

(5) IMP and exp use different character sets
If the character set is different, the import fails, and you can change the UNIX environment variable or the NT registry Nls_lang related information.
When the import is complete, change it back.

(6) IMP and EXP versions are not compatible
IMP can successfully import the files generated by the low version exp and cannot import the files generated by the high version exp
According to the situation, we can use

1 completely export database test, user Name System Password Manager exported to D:daochu.dmp
Expsystem/manager@testfile=d:daochu.dmp full=y
2 Exporting a table from the system user in the database to the SYS user
Expsystem/manager@testfile=d:daochu.dmp wner= (System,sys)
3 Export the table table1, table2 in the database
Expsystem/manager@testfile=d:daochu.dmp tables= (Table1,table2)
4 Export the fields in the table table1 in the database filed1 with "00″ data"
Expsystem/manager@testfile=d:daochu.dmp tables= (table1) query= "where filed1 like '00%'"
The above is the common export, for compression I do not care, with WinZip DMP file can be very good compression. But it's OK to add compress=y after the above command.
"Import with IMP data":
1 Import the data from D:DAOCHU.DMP into the test database.
Same User:
Impsystem/manager@test file=d:daochu.dmp
Different users:
There may be something wrong with it, because some tables already exist, and then it complains, and the table is not imported.
Just add ignore=y to the back.
2 Import the table table1 in D:daochu.dmp
Impsystem/manager@test file=d:daochu.dmp tables= (table1)
Basically, the import export above is sufficient. There are a lot of situations where I delete the table completely and import it.
Features: Oracle data Import Export IMP/EXP is equivalent to Oracle data restore and backup.
In most cases, you can use an Oracle data import export to complete the backup and restore of your data (without causing data loss).
Oracle has a benefit, although your computer is not a server, but you installed the Oracle client, and established a connection
(Add the correct service name by naming the local –> service in Net8 Assistant
In fact, you can think of the client and server side of the path, and then the data can be pulled over the
This allows you to export the data locally, although the server may be far away from you.
You can also import DMP files from a local to a remote database server.
With this feature you can build two identical databases, one for testing and one for formal use.
Execution environment: can be executed in SQLPLUS.EXE or DOS (command line),
DOS can be performed because the install directory Ora81bin in Oracle 8i is set to the global path,
There are EXP.EXE and IMP.EXE files under this directory that are used to perform import export.
Oracle is written in Java, I would like to SQLPLUS.EXE, EXP. EXE, IMP. EXE these two files are wrapped in the class file.
Sqlplus. EXE call EXP.EXE, IMP. EXE they are wrapped in the class, completing the import export function.
Attention:
You need to have enough permissions, you don't have enough permissions, it will prompt you.
Database can be connected to. You can use tnsping test to get the database test to connect.
--------------------------------------------------------------------------------------------------------------- ---------------
Detailed reference is as follows:
A. Export tool exp
1. It is the next executable file storage directory for the operating system/oracle_home/bin
The EXP export tool compresses data backups in a database into a binary system file. Can migrate between different OS
It has three different modes:
A. User mode: Export all user objects and data in the object;
B. Table mode: Export all the user tables or specified tables;
C. Entire database: Export all objects in the database.
2. Export tool exp Interactive command line usage examples
$exp test/test123@appdb
Enter array Fetch buffer size:4096 > 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 > enter
Compress extents (yes/no): yes > 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 IS exported: (Return to quit) > Cmamenu the name of the tables to export
. . Exporting table Cmamenu 4336 rows Exported
Table (T) or Partition (T:P) to IS exported: (Return to quit) > list name to export
Table (T) or Partition (T:P) to IS exported: (Return to quit) > enter
Export terminated successfully without warnings.
3. Export tool exp Non-interactive command line example
$exp Scott/tiger tables= (emp,dept) file=/directory/scott.dmp grants=y
Description: Export two table emp,dept from Scott user to file/directory/scott.dmp
$exp Scott/tiger tables=emp query= "where job= ' salesman ' and sal<1600" file=/directory/scott2.dmp
Description: Add the query condition of the EMP in exp inside job= ' salesman ' and sal<1600
(but I personally rarely use this, or after the conditions of the record to generate temporary tables, then exp will be more convenient)
$exp parfile=username.par file=/directory1/username_1.dmp,/directory1/username_2.dmp Filesize=2000M log=/ Directory2/username_exp.log
Parameter file Username.par content
Userid=username/userpassword
buffer=8192000
Compress=n
Grants=y
Description: Username.par for export tool exp used in the parameter file, the specific parameters can be modified according to the need
FILESIZE Specifies the maximum number of bytes generated for binary prepared files
(can be used to solve the limitations of 2G physical files under some OS and to speed up compression and facilitate engraving of historical data CDs, etc.)
Two. Import Tools Imp
1. It is the next executable file storage directory for the operating system/oracle_home/bin
The Imp Import tool imports the binary system files that are formed by exp into the database.
It has three different modes:
A. User mode: Export all user objects and data in the object;
B. Table mode: Export all the user tables or specified tables;
C. Entire database: Export all objects in the database.
Only users with Imp_full_database and DBA authority can do the entire database import
Imp Step:
(1) Create TABLE (2) Insert data (3) create INDEX (4) Create triggers,constraints
2. Import Tools Imp Interactive command line 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
Enter the insertion buffer size (min. 8192) 30720>
To export a file created by export:v08.01.06 through a regular path
Warning: This object is exported by TEST instead of the current user
Import in the ZHS16GBK character set and ZHS16GBK NCHAR character set has been completed
List only the contents of the import file (yes/no):no>
Ignore creation error (YES/NO) because the object already exists: No>yes
Import permissions (yes/no):yes>
Import table Data (yes/no):yes>
Import the entire export file (yes/no): No>yes
. Importing the object of test into SCOTT
. . Importing table "Cmamenu" 4336 rows are imported
The import was successfully terminated, but a warning appears.
3. Import Tools imp non-interactive command line 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_s Cott.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. Possible problems with importing tools imp
(1) The database object already exists
In general, you should delete the table, sequence, function/process, triggers, etc. before the data is imported.
The database object already exists, and the default IMP parameter will import the failed
If the parameter ignore=y is used, the data content in the exp file is imported
If the table has a constraint on a unique keyword, the condition will not be imported
If the table does not have a unique keyword constraint, it will cause the record to repeat
(2) Database objects have primary foreign key constraints
Data will fail to import if the primary foreign key constraint is not met
Workaround: Import primary table First, then import dependency table
Disable the primary foreign KEY constraint on the target import object, and then enable them after importing the data
(3) Insufficient authority
If you want to import a user's data into a B user, a user needs to have Imp_full_database permissions
(4) When importing large tables (greater than 80M), storage allocation fails
The default exp, compress = Y, is to compress all the data on a block of data.
When imported, failure is imported if there is no contiguous large block of data.
When you export a large table above 80M, remember compress= N, this error is not caused.
(5) IMP and exp use different character sets
If the character set is different, the import fails, and you can change the UNIX environment variable or the NT registry Nls_lang related information.
When the import is complete, change it back.
(6) IMP and EXP versions are not compatible
IMP can successfully import the files generated by the low version exp and cannot import the files generated by the high version exp
According to the situation, we can use
$ imp username/password@connect_string
Description: Connect_string is in/oracle_home/network/admin/tnsnames.ora
Name of the local or remote database defined
Precautions:
UNIX:/etc/hosts to define the host name of a local or remote database server
The correspondence between Win98:windowshosts and IP address

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch