Oracle 数据的导入和导出

数据导出：
 1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
   exp system/manager@TEST file=d:\daochu.dmp full=y
 2 将数据库中system用户与sys用户的表导出
   exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
 3 将数据库中的表inner_notify、notify_staff_relat导出
    exp aichannel/aichannel@TESTDB2 file= d:\datanewsmgnt.dmp tables=(inner_notify,notify_staff_relat) 
 4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
   exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=" where filed1 like '00%'"
 
  上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。
  也可以在上面命令后面 加上 compress=y 来实现。

数据的导入
 1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
   imp system/manager@TEST  file=d:\daochu.dmp
   imp aichannel/aichannel@TEST  full=y  file=d:\datanewsmgnt.dmp ignore=y
   上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
   在后面加上 ignore=y 就可以了。
 2 将d:daochu.dmp中的表table1 导入
 imp system/manager@TEST  file=d:\daochu.dmp  tables=(table1)

 

IMP:

silent          silent: display banner information, default is N
trace           trace option: enable sql_trace and timed_stat, default is N
resumable               resumable option: enable resumable session, default is N
resumable_timeout               resumable_timeout option: wait time for resumable
resumable_name          resumable_name option: SQL statements to be resumable
userid          user/password to connect to oracle: no default
recordlength            record length of file: optional, default is system dependent
file            import file names: format is (file1, file2...) default is EXPDAT.DMP
show            list contents of import file only: default is N
ignore          ignore create error due to object existence: default is N
grants          import grants option: default is T
rows            import rows option: default is Y
full            import entire database: default is N
fromuser                users to import: format is '(user1,user2,..,userN)'
touser          import into: format is '(user1,user2,..,userN)'
tables          tables to import: format is '(table1, table2,...,tableN)'
buffer          buffer size: default is system dependent (IMPDBFS)
parfile         Parameter file: name of file that contains parameter specifications
indexes         import indexes option: default is Y
constraints             import constraints option: enable constraints, default is Y
inctype         incremental import option: system or restore
commit          commit option: whether to commit after each array insert, default is N
help            help: display descriptions on import parameters, default is N
indexfile               write table/index information to specified filename
log             log import messages to specified file
destroy         destroy (reuse) tablespace datafiles, default is N
charset         character set of export file (if not exact)
feedback                feedback in rows default is IMPFDB
point_in_time_recover           Point-in-time-Recover option: default is N
analyze         execute ANALYZE statements in dump file: default is Y
skip_unusable_indexes           skip maintenance of unusable indexes: default is N
filesize                file size: the size of export dump files
toid_novalidate         types not to validate: format is '(type1, type2,...typeN)'
recalculate_statistics          recalculate statistics: (yes, no)
statistics              statistics: (always, safe, none, recalculate)
transport_tablespace            Transport Tablespace Mode:  default in N
tts_owners              transport validation: format is (owner1, owner2...)
datafiles               transport datafile destination names: format is (file1, file2...)
tablespaces             transport validation: format is (tsn1, tsn2...)
compile         compile packages, procedures, and functions: enable compile, default is Y
icache          icache import modes: users or dependencies
streams_configuration           Enable STREAMS_CONFIGURATION, default is Y
streams_instantiation           Enable STREAMS_INSTANTIATION, default is N
metrics         metrics: display performance metrics, default is N
data_only               import data only: default is N

Import: Release 11.2.0.1.0 - Production on 星期一 9月 17 19:15:10 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 EXP:

template                IAS Template to be used
silent          silent: display banner information, default is N
metrics         metrics: display performance information, default is N
userid          user/password to connect to oracle: no default
recordlength            record length of file: optional, default is system dependent
buffer          array fetch buffer size: default is EXUAFCH (4096)
file            export file names: format is (file1, file2...) default is EXPDAT.DMP
full            export entire database: default is N
grants          export grants option: default is Y
rows            export rows option: default is Y
compress                compact extents option: default is Y
trace           trace option: enable sql_trace and timed_stat, default is N
resumable               enable resumable session : default is N
resumable_timeout               resumable_timeout: wait time for resumable
resumable_name          resumable string: SQL statements to be resumable
owner           users to export: format is '(user1, user2, .., userN)'
tables          tables to export: format is '(table1, table2, ..., tableN)'
parfile         parameter file: name of file that contains parameter specifications
indexes         export indexes option: default is Y
inctype         incremental export option: (incremental, cumulative or complete)
record          option to record incremental/cumulative export: default is Y
constraints             export table constraints option: default is Y
consistent              provide read-consistency for the entire export: default is N
help            help: display descriptions on export parameters, default is N
log             log export messages to specified file
statistics              analyze option: (estimate, cumulative, none)
feedback                feedback in rows default is EXUFDB
direct          direct path option: default is N
point_in_time_recover           point-in-time recover option: default is N
tts_full_check          TTS perform strict test for objects in recovery set: default is N
tablespaces             tablespaces to transport or recover: format is '(ts1, ts2, ..., tsN)'
query           query used to select a subset of rows for a table
filesize                file size: the size of export dump files
transport_tablespace            transportable tablespace option: default is N
triggers                export triggers option: default is Y
impparfile              file to create as paramfile for IMP for transportable tablespaces
file_format             format of export file names
flashback_time          database time to be used for flashback export: no default
flashback_scn           system change number to be used for flashback export: no default
object_consistent               Provides consistency for registered objects during execution of procedureal callback: default is N

Export: Release 11.2.0.1.0 - Production on 星期一 9月 17 19:17:01 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.