Oracle Foundation EXP/IMP Data Pump import/Export command

First, the Export method:

There are four ways to export data using the Exp/imp method:

1. Table mode export: One or more specified tables, including the table definition, table data, table owner authorization, table index, table constraints, and triggers created on the table. You can also export only structures and not export data. You can also export all the tables owned by the user, and you can also specify the partitions of the exported table.

2. User mode export: All objects in the user pattern and the data in the object.

3. Tablespace Export: All objects contained in the specified table space, and the index definition on the object.

4. All database export: Refers to all objects in the database, including tablespaces, users, and all objects in the schema (tables, views, sequences, synonyms, constraints, indexes, stored procedures and triggers, etc.), data, and permissions.

Second, export the data with the EXP command.

Grammar:

Exp Userid=username/password direct=y full=y rows=y file=d:/backup/back.dmp log=d:/backup/back.log

Description

Userid=username/password: Represents the user name and password.

Direct=y: Whether to export by direct path

Full=y: Indicates all database exports

Rows=y: Represents the export along with the data in the base table

File: Export files

LOG: Exported Journal file

The parameter can be one or more, and if no required parameters are given at the command line, the Export utility prompts the user to enter it individually.

　　

Because of too many exp parameters, you can obtain the EXP syntax information by entering exp help=y.

By entering the EXP command and username/password, you can then command the username/password:

Routine: EXP Scott/tiger

Alternatively, you can control "export" by different parameters by entering an EXP command with various parameters. To specify parameters, you can use keywords:

Format: Exp keyword=value or keyword= (value1,value2,..., valuen) Routines: Exp Scott/tiger grants=y tables= (emp,dept,mgr) or TABLES= (T 1:P1,T1:P2), if T1 is a partitioned table

The USERID must be the first parameter in the command line.

  Keywords   description (default)                                         keywords                         description (default)  -------------------------------------- ------------------------------------  userid        username/password                               full                         Export Entire file (N)   buffer         Data buffer size                          owner                    owner user List   file              output file (expdat. DMP)            tables                    table Name list   compress   Import into a zone ( Y)                        recordlength io  record length   grants       export Permission (Y)                               inctype                  incremental export Types   INDEXES&NBsp;     Export Index (Y)                              record                  Tracking Incremental Export (Y)   DIRECT         Direct Path (N)                             triggers                Export Trigger (Y)   log             log file for screen output                     statistics              Analytical Objects (ESTIMATE)   rows          Export Data rows (Y)                          parfile                   parameter file name   consistent crosstab consistency                           constraints         export Constraints (Y)

  feedback                               Show progress per x line (0)   filesize                                  maximum size of each dump file   flashback_scn                     SCN   flashback_time              for callback session snapshots       time to get the SCN closest to the specified time   query                                     SELECT clauses to export a subset of tables   RESUMABLE     &NBSp;                      hang when encountering a space-related error (N)   resumable_name                   text string used to identify a recoverable statement   resumable_timeout              resumable waiting time   tts_full_check                    perform a full or partial dependency check on TTS   tablespaces                         List of table spaces to export   transport_tablespace        Export Transportable tablespace metadata (N)   template                               calling IAS Template name for schema export

Example:

　　1) fully export the database

Exp System\[email protected] file= ' d:\backup\full.dmp ' full=y

　　2) Export user

　　Exp System/[email protected] file= ' D:\backup\scott.dmp ' owner= (Scott)

　　3) Export Table

　　Exp Scott/[email protected] file= ' D:\backup\table.dmp ' tables= (emp,dept)

　　4) Export Table space

　　Exp Scott/[email protected] file= ' D:\backup\table.dmp ' tablespaces= (users)

If you want to compress the DMP file, you can do so by adding compress=y to the above command.

Iii. importing data using the IMP command　　

The syntax information for IMP can be obtained by entering IMP help=y at the command line: ============================================================================= C:/ Documents and Settings/auduser>imp help=y

Commands that can be followed by a username/password by entering the IMP command and your user name/password:

Routine: IMP Scott/tiger

Alternatively, you can control "import" according to different parameters by entering the IMP command and various parameters. To specify parameters, you can use keywords:

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

The USERID must be the first parameter in the command line.

  Keywords   description (default)         keywords       description (default)  -- ------------------------------------------------------------------------  userid   username/password               full          Import entire file (N)   buffer   data buffer size          Fromuser      Everyone user Name list   file     input file (expdat. DMP)     touser       user Name list   show     List only file contents (n) tables      table Name list   ignore   Ignore creation error (N)     RecordLength   Length of 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 file name   log file for log      screen output     constraints  Import Restrictions (Y)   destroy                            Overlay tablespace data file (N)   indexfile                          writes table/index information to the specified file   skip_unusable_indexes  Skips maintenance of indexes that are not available (N)   feedback                          Show progress per x line (0)   toid_novalidate             Skip validation of the specified type ID   FILESIZE      &NBsp;                      maximum size of each dump file   statistics                        always import precomputed statistics   resumable                        hang when encountering a space-related error (N)   resumable_name              text string used to identify a recoverable statement   resumable_timeout         resumable The waiting time   compile                              compilation process, packages and functions (Y)

The following keywords are used only for transportable tablespace transport_tablespace import Transportable tablespace metadata (N) tablespaces the tablespace that will be transferred to the database datafiles The data file that will be transferred to the database tts_owners has a user who can transmit data in a table space set

Example:

　　1) Import All

Imp scott/[email protected] file=full.dmp full=y

　　2) Import Table

Imp scott/[email protected] file=table.dmp tables= (emp,dept)

　　3) Import Users

　　Imp scott/[email protected] file=d:\backup\scott.dmp Fromuser=scott Touser=scott

Oracle Foundation EXP/IMP Data Pump import/Export command