EXP命令参数举例注意事项问题与解决(整理汇)

EXP命令参数举例注意事项问题与解决(整理汇)本文简介：1获取exp帮助exphelp=yEXPusername/password:Example:EXPSCOTT/TIGEREXPKEYWORD=valueorKEYWORD=(value1,value2,.,valueN)Example:EXPSCOTT/TIGERGRANTS=YTABLES=(EM

EXP命令参数举例注意事项问题与解决(整理汇)本文内容：

1

获取exp

帮助

exp

help=y

EXP

username/password:

Example:

EXP

SCOTT/TIGER

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)

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

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

INDS

export

inds

(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

per.

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

Example:

1.

将数据库中system用户与sys用户的表导出

expsystem/[emailprotected]=d:\daochu.dmp

wner=(system,sys)

2

导出一个完整数据库

exp

system/manager

file=bible_db

log=dible_db

full=y

3.

导出数据库定义而不导出数据

exp

system/manager

file=bible_db

log=dible_db

full=y

rows=n

4.

导出一个或一组指定用户所属的全部表、索引和其他对象

exp

system/manager

file=seapark

log=seapark

wner=seapark

exp

system/manager

file=seapark

log=seapark

wner=(seapark,amy,amyc,harold)

注意：在导出用户时，尽管已经得到了这个用户的所有对象，但是还是不能得到这些对象引用的任何同义词。解决方法是用以下的SQL*Plus命令创建一个脚本文件，运行这个脚本文件可以获得一个重建seapark所属对象的全部公共同义词的可执行脚本，然后在目标数据库上运行该脚本就可重建同义词了。

SET

LINESIZE

132

SET

PAGESIZE

0

SET

TRIMSPOOL

ON

SPOOL

c:\seapark.syn

SELECT

Create

public

synonym

||synonym_name

||

for

||table_owner||

.

||table_name||

;

FROM

dba_synonyms

WHERE

table_owner

=

SEAPARK

AND

wner

=

PUBLIC

;

SPOOL

OFF

5.

导出一个或多个指定表

exp

seapark/seapark

file=tank

log=tank

tables=tank

exp

system/manager

file=tank

log=tank

tables=seapark.tank

exp

system/manager

file=tank

log=tank

tables=(seapark.tank,amy.artist)

6.

估计导出文件的大小

全部表总字节数：

SELECT

sum(bytes)

FROM

dba_segments

WHERE

segment_type

=

TABLE

;

seapark用户所属表的总字节数：

SELECT

sum(bytes)

FROM

dba_segments

WHERE

wner

=

SEAPARK

AND

segment_type

=

TABLE

;

seapark用户下的aquatic_animal表的字节数：

SELECT

sum(bytes)

FROM

dba_segments

WHERE

wner

=

SEAPARK

AND

segment_type

=

TABLE

AND

segment_name

=

AQUATIC_ANIMAL

;

7.

导出表数据的子集(oracle8i以上)

NT系统：

exp

system/manager

query=

Where

salad_type=

FRUIT

tables=amy.salad_type

file=fruit

log=fruit

UNIX系统：

exp

system/manager

query=\“Where

salad_type=\

FRUIT\

\“tables=amy.salad_type

file=fruit

log=fruit

8.

用多个文件分割一个导出文件

exp

system/manager

file=(paycheck_1,paycheck_2,paycheck_3,paycheck_4)

log=paycheck,filesize=1G

tables=hr.paycheck

9.

使用参数文件

exp

system/manager

parfile=bible_tables.par

bible_tables.par参数文件：

#Export

the

sample

tables

used

for

the

Oracle8i

Database

Administrator

s

Bible.

file=bible_tables

log=bible_tables

tables=(

amy.artist

amy.books

seapark.checkup

seapark.items

)

10.

增量导出

“完全”增量导出(complete)，即备份整个数据库

exp

system/manager

inctype=complete

file=990702.dmp

“增量型”增量导出(incremental)，即备份上一次备份后改变的数据

exp

system/manager

inctype=incremental

file=990702.dmp

“累计型”增量导出(cumulative)，即备份上一次“完全”导出之后改变的数据

exp

system/manager

inctype=cumulative

file=990702.dmp