exp/imp属于oracle内置的工具包,安装oracle后即可使用,(如果不能成功执行,catexp.sql/catalog.sql重新生成数据字典)
权限上,owner可以导出自己的schema 如果要导出整库或别的schema则需要赋予用户exp_full_database角色,导入需要imp_full_database角色。
导出导入模式分为4种:
整库导出导入(full=y),schema导出导入(owner=?),表导出导入(tables=owner.table),表空间导出导入(tablespaces=tablespace_name)
建议使用parfile导出导入:
exp parfile=xxx.par
parfile参数文件:
#Export the sample tables used for the sid.
userid=xxx/xxx
file=bible_tables.dmp
log=bible_tables.log
tables=(
amy.artist
amy.books
seapark.checkup
seapark.items
)
常用参数:
参数 描述(默认值)
------------- -------------------------------------------------------------
USERID 用户名/密码
FULL 全库导出模式
OWNER schema导出模式
TABLES table导出模式
TABLESPACES tablespaces导出模式
FILE 导出数据库文件 (EXPDAT.DMP)
LOG 导出日志文件
BUFFER size of data buffer
COMPRESS import into one extent (Y)
GRANTS export grants (Y)
INDEXES export indexes (Y)
RECORD track incr. export (Y)
RECORDLENGTH length of IO record(在direct 模式下有效)
DIRECT direct path (N)
TRIGGERS export triggers (Y)
STATISTICS analyze objects (ESTIMATE)
ROWS export data rows (Y)
CONSTRAINTS export constraints (Y)
CONSISTENT cross-table consistency(N)
OBJECT_CONSISTENT transaction set to read only during object export (N)
PARFILE parameter filename
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 设置session挂起(N)(当设置了Y,则需要设置RESUMABLE_NAME和RESUMABLE_TIMEOUT)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT 设置session挂起时间
TTS_FULL_CHECK perform full or partial dependency check for TTS
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
说明:
用户模式下以下不能导出:
Default roles
Directory aliases
Profiles
Public synonyms
Public dblink
Role grants
Roles
System privilege grants
Tablespace quotas