命令列方式呼叫EXP：

exp的引數眾多，可以透過該以下方式查件exp下的引數：

[oracle@localhost ~]$ exp help=y

Export: Release 10.2.0.1.0 - Production on Wed Mar 19 16:30:55 2014

You can let Export prompt you for parameters by entering the EXP

command followed by your username/password:

Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed

by various arguments. To specify parameters, you use keywords:

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 partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default) Keyword Description (Default)

--------------------------------------------------------------------------

USERID username/password FULL export entire file (N)

BUFFER size of data buffer OWNER list of owner usernames

FILE output files (EXPDAT.DMP) TABLES list of table names

COMPRESS import into one extent (Y) RECORDLENGTH length of IO record

GRANTS export grants (Y) INCTYPE incremental export type

INDEXES export indexes (Y) RECORD track incr. export (Y)

DIRECT direct path (N) TRIGGERS export triggers (Y)

LOG log file of screen output STATISTICS analyze objects (ESTIMATE)

ROWS export data rows (Y) PARFILE parameter filename

CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)

OBJECT_CONSISTENT transaction set to read only during object export (N)

FEEDBACK display progress every x rows (0)

FILESIZE maximum size of each dump file

FLASHBACK_SCN SCN used to set session snapshot back to

FLASHBACK_TIME time used to get the SCN closest to the specified time

QUERY select clause used to export a subset of a table

RESUMABLE suspend when a space related error is encountered(N)

RESUMABLE_NAME text string used to identify resumable statement

RESUMABLE_TIMEOUT wait time for RESUMABLE

TTS_FULL_CHECK perform full or partial dependency check for TTS

VOLSIZE number of bytes to write to each tape volume

TABLESPACES list of tablespaces to export

TRANSPORT_TABLESPACE export transportable tablespace metadata (N)

TEMPLATE template name which invokes iAS mode export

Export terminated successfully without warnings.

比如匯出scott模式下的所有物件及資料：

[oracle@localhost ~]$ exp scott/oracle file=/u01/exp_dir/scott-03-18.dmp log=/u01

/exp_dir/scott_log-03-18.log;

.

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

[oracle@localhost ~]$ ls /u01/exp_dir/

scott-03-18.dmp scott_log-03-18.log

引數檔案呼叫：

在exp後跟的引數較多時，建議使用PARFILE引數指定一個引數檔案，該引數會指定一個文字格式的引數檔案來設定exp的詳細設定:(如果呼叫的parfile引數檔案中的引數設定與命理行呼叫的引數用衝突，會以最後出現的引數為準)

比如手工寫一個parfile

[oracle@localhost ~]$ vi /u01/exp_dir/parfile.par

file=/u01/exp_dir/parfile_test.dmp

log=/u01/exp_dir/parfile_test.log

filesize=102400000

buffer=1024000

indexes=y

按照剛寫的parfile引數設定匯出scott使用者下的所有物件和資料：

[oracle@localhost ~]$ exp scott/oracle parfile=/u01/exp_dir/parfile.par

.

.. about to export SCOTT's tables via Conventional Path ...

. . exporting table BONUS 0 rows exported

. . exporting table DEPT 4 rows exported

. . exporting table DEPT_TEST 14 rows exported

. . exporting table EMP 14 rows exported

. . exporting table EMP_TEST 14 rows exported

. . exporting table SALGRADE 5 rows exported

.

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

互動模式呼叫：

直接執行exp命令，根據提示設定匯出資料：

如：匯出scott使用者下的emp和dept表：

[oracle@localhost ~]$ exp

Export: Release 10.2.0.1.0 - Production on Wed Mar 19 16:49:48 2014

Username: scott

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 > 102400

Export file: expdat.dmp > /u01/exp_dir/exp_mtl.dmp

(2)U(sers), or (3)T(ables): (2)U > T

Export table data (yes/no): yes > yes

Compress extents (yes/no): yes > yes

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

Table(T) or Partition(T:P) to be exported: (RETURN to quit) > dept

. . exporting table DEPT 4 rows exported

Table(T) or Partition(T:P) to be exported: (RETURN to quit) > scott:emp

. . exporting table DEPT 4 rows exported

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

Export terminated successfully with warnings.

四種處理模式：

整庫模式：需要擁有EXP_FULL_DATABASE和IMP_FULL_DATABASE的角色，對應的引數為FULL

表空間模式：對應TABLESPACE

使用者模式：對應OWNER

表模式：對應TABLES

比如匯出EXAMPLE表空間（由於需要用as sysdba，所以’前要加上\進行轉譯）

[oracle@localhost exp_dir]$ exp \'system/oracle as sysdba\' tablespaces=users transport_tablespace=y file=/u01/exp_dir/users_tbs.dmp log=/u01/exp_dir/users_tbs.log

匯出scott使用者下的emp和dept表的前10行資料：

[oracle@localhost ~]$ exp scott/oracle tables=dept,emp query = "'where rownum

.

About to export specified tables via Conventional Path ...

. . exporting table DEPT 4 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table EMP 10 rows exported

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

同時匯出scott和xtt 模式：

[oracle@localhost ~]$ exp \'system/oracle as sysdba\' owner=scott,xtt file=/u01/exp_di

r/shame.dmp log=/u01/exp_dir/shame.log

.

.. 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 with warnings

匯出全庫：

[oracle@localhost ~]$ exp \' sys/oracle as sysdba\' full=y file=/u01/exp_dir/full_database.dmp log=/u01/exp_dir/full_database.log

.

. exporting posttables actions

. exporting triggers

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting user history table

. exporting default and system auditing options

. exporting statistics

Export terminated successfully with warnings.

匯出scott.emp表但不匯出授權，索引，約束和觸發器

[oracle@localhost ~]$ exp scott/oracle tables=emp grants=n indexes=n constraints=n triggers=n file=/u01/exp_dir/scott_emp.dmp log=/u01/exp_dir/scott_emp.log

.

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

Note: grants on tables/views/sequences/roles will not be exported

Note: indexes on tables will not be exported

Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...

. . exporting table EMP 14 rows exported

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

估計要匯出資料大小，並分多個檔案匯出：

如：估測XTT和SCOTT使用者SHEAM的大小，並分配每個檔案合適大小將其匯出

SQL> select owner,max(bytes)/1024/1024 MAX_BYTES

2 from dba_segments

3 where owner in('SCOTT','XTT')

4 group by owner

5 order by 2 desc

6 /

OWNER MAX_BYTES

------------------------------ ----------

XTT 104

SCOTT .0625

透過上面的查詢可以看出這兩個sheam大小在105MB左右，先要分三個檔案匯出，每個40MB足夠了

[oracle@localhost ~]$ exp \'system/oracle as sysdba\' owner=scott,xtt filesize=40M file=/u01/exp_dir/scott_xtt_sheam01.dmp,/u01/exp_dir/scott_xtt_sheam02.dmp,/u01/exp_dir/scott_xtt_sheam03.dmp log=/u01/exp_dir/scott_xtt_sheam.log

.

. 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 with warnings.

EXP邏輯匯出資料的呼叫方式

相關文章