数据导出:
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.