Common Oracle exp tools

The following is an introduction to Oracle exp, a commonly used data export tool EXP. We all know that Oracle EXP is a tool for the client, which can be used on the Oracle client and the Oracle server. When using the EXP tool on the Oracle client, it must contain a connection string;

When using the EXP tool on the Oracle server, strings are not allowed. Export includes three modes: export table, export scheme, and export database.

1) Export the table

To export a table, you can use the EXP tool to store the structure and data of one or more TABLES in the OS file. to export a table, you can use the TABLES option.

Normal users can export all tables of their own solutions. However, to export tables of other schemes, this user must have the EXP_FULL_DATABASE role or DBA role. In addition, when you export a table, all indexes, triggers, and constraints of the corresponding table are exported by default. The following uses the SYSTEM user to export the SCOTT. DEPT table as an example:

 
 

  
  
exp system/Oracle@charge TABLE=scott.dept,scott.emp FILE=tab1.dmp 
 
 

2) Export Solution

The export scheme is to use Oracle EXP tool to store all the object records in one or more schemes to the OS file, and the export table is completed using the OWNER option.

Normal users can export their own solutions. However, to export other solutions, the user must have the DBA role or the EXP_FULL_DATABASE role. If you want to export all objects in your own scheme, you can leave the OWNER option Unspecified. The following uses the SYSTEM user to export all objects in the SCOTT scheme as an example. The example is as follows:

 
 

  
  
exp system/Oracle@charge OWNER=scott FILE=schemal.dmp 
 
 

The above is the command line method for data export. The EXP tool can also export data in interactive mode.

1) Export table Interaction Mode)

 
 

  
  
[Oracle@ora-asm3 dbs]$ exp 
 
 

Export: Release 10.2.0.1.0-Production on Monday June 16 16:24:26 2008

 
 

  
  
Copyright (c) 1982, 2005, Oracle. All rights reserved. 
 
 

Username: charge enter user name

Password: enter the Password

 
 

  
  
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production  
  
  
With the Partitioning, OLAP and Data Mining options  
 
 

Enter array fetch buffer size: 4096> input buffer size, which can be customized by default. If the data file is large, we recommend that you set a larger value.

Export file: expdat. dmp> charge01.dmp: Enter the exported file name, which must use the ". dmp" file as the extension.

(2) U (sers), or (3) T (ables): (2) U> t input and export type. The default value is the user, that is, the scheme. Enter t here, export table

Export table data (yes/no): yes> whether to Export data in the table. If NO is selected, the table structure is exported.

Compress extents (yes/no): yes> whether to Compress data

 
 

  
  
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set  
  
  
About to export specified tables via Conventional Path ...  
 
 

Table (T) or Partition (T: P) to be exported: (RETURN to quit)> clients input Table name

Start exporting clients table data

 
 

  
  
. . exporting table CLIENTS 10 rows exported 
 
 

Table (T) or Partition (T: P) to be exported: (RETURN to quit)> if no data is to be exported, press enter to exit.

 
 

  
  
Export terminated successfully without warnings. 
 
 

A message is displayed, indicating that the export is successful. No alarm is triggered.
2) Export the scheme interaction method)

 
 

  
  
[Oracle@ora-asm3 dbs]$ exp 
 
 

Export: Release 10.2.0.1.0-Production on Monday June 16 16:23:47 2008

 
 

  
  
Copyright (c) 1982, 2005, Oracle. All rights reserved. 
 
 

Username: charge enter user name

Password: enter the Password

 
 

  
  
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production  
  
  
With the Partitioning, OLAP and Data Mining options  
 
 

Enter array fetch buffer size: 4096> input buffer size, which can be customized by default. If the data file is large, we recommend that you set a larger value.

Export file: expdat. dmp> charge. dmp: Enter the exported file name, which must use ". dmp" as the extension.

(2) U (sers), or (3) T (ables): (2) U> u input and export type, the default is the user solution), you can press enter directly, you can also enter u.

Export grants (yes/no): yes> Import Permission

Export table data (yes/no): yes> whether to Export data in the table. If NO is selected, the table structure is exported.

Compress extents (yes/no): yes> whether to Compress data

 
 

  
  
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set  
  
  
. exporting pre-schema procedural objects and actions  
  
  
. exporting foreign function library names for user CHARGE  
  
  
. exporting PUBLIC type synonyms  
  
  
. exporting private type synonyms  
  
  
. exporting object type definitions for user CHARGE  
  
  
About to export CHARGE's objects ...  
  
  
. exporting database links  
  
  
. exporting sequence numbers  
  
  
. exporting cluster definitions  
  
  
. about to export CHARGE's tables via Conventional Path ...  
  
  
. . exporting table BALANCE 0 rows exported  
  
  
. . exporting table BALANCEFINISHED 0 rows exported  
  
  
………………  
  
  
. exporting synonyms  
  
  
. exporting views  
  
  
. exporting stored procedures  
  
  
. exporting operators  
  
  
. exporting referential integrity constraints  
  
  
. exporting triggers  
  
  
. exporting indextypes  
  
  
. exporting bitmap, functional and extensible indexes  
  
  
. exporting posttables actions  
  
  
. exporting materialized views  
  
  
. exporting snapshot logs  
  
  
. exporting job queues  
  
  
. exporting refresh groups and children  
  
  
. exporting dimensions  
  
  
. exporting post-schema procedural objects and actions  
  
  
. exporting statistics  
  
  
Export terminated successfully without warnings.  
 
 

A message is displayed, indicating that the export is successful. No alarm is triggered.