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

1获取exp 帮助exp help=yEXP username/password:Example: EXP SCOTT/TIGEREXP 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 tableUSERID must be the first parameter on the command line.KeywordDescription (Default)-USERIDusername/passwordFULLexport entire file (N)BUFFERsize of data bufferOWNERlist of owner usernamesFILEoutput files (EXPDAT.DMP) TABLESlist of table namesCOMPRESSimport into one extent (Y)RECORDLENGTH length of IO recordGRANTSexport grants (Y)INCTYPEincremental export typeINDEXESexport indexes (Y)RECORDtrack incr. export (Y)DIRECTdirect path (N)TRIGGERSexport triggers (Y)LOGlog file of screen output STATISTICSanalyze objects (ESTIMATE)ROWSexport data rows (Y)PARFILEparameter filenameCONSISTENT cross-table consistency(N)CONSTRAINTSexport constraints (Y)OBJECT_CONSISTENTtransaction set to read only during object export (N)FEEDBACKdisplay progress every x rows (0)FILESIZEmaximum size of each dump fileFLASHBACK_SCNSCN used to set session snapshot back toFLASHBACK_TIMEtime used to get the SCN closest to the specified timeQUERYselect clause used to export a subset of a tableRESUMABLEsuspend when a space related error is encountered(N)RESUMABLE_NAMEtext string used to identify resumable statementRESUMABLE_TIMEOUTwait time for RESUMABLETTS_FULL_CHECKperform. full or partial dependency check for TTSVOLSIZEnumber of bytes to write to each tape volumeTABLESPACESlist of tablespaces to exportTRANSPORT_TABLESPACE export transportable tablespace metadata (N)TEMPLATEtemplate name which invokes iAS mode exportExample: 1. 将数据库中system用户与sys用户的表导出expsystem/managerTESTfile=d:daochu.dmp wner=(system,sys)2 导出一个完整数据库exp system/manager file=bible_db log=dible_db full=y3. 导出数据库定义而不导出数据exp system/manager file=bible_db log=dible_db full=y rows=n4. 导出一个或一组指定用户所属的全部表、索引和其他对象exp system/manager file=seapark log=seapark wner=seaparkexp system/manager file=seapark log=seapark wner=(seapark,amy,amyc,harold)注意：在导出用户时，尽管已经得到了这个用户的所有对象，但是还是不能得到这些对象引用的任何同义词。解决方法是用以下的SQL*Plus命令创建一个脚本文件，运行这个脚本文件可以获得一个重建seapark所属对象的全部公共同义词的可执行脚本，然后在目标数据库上运行该脚本就可重建同义词了。SET LINESIZE 132SET PAGESIZE 0SET TRIMSPOOL ONSPOOL c:seapark.synSELECT Create public synonym |synonym_name| for |table_owner|.|table_name|;FROM dba_synonymsWHERE table_owner = SEAPARK AND wner = PUBLIC;SPOOL OFF5. 导出一个或多个指定表exp seapark/seapark file=tank log=tank tables=tankexp system/manager file=tank log=tank tables=seapark.tankexp system/manager file=tank log=tank tables=(seapark.tank,amy.artist)6. 估计导出文件的大小全部表总字节数：SELECT sum(bytes)FROM dba_segmentsWHERE segment_type = TABLE;seapark用户所属表的总字节数：SELECT sum(bytes)FROM dba_segmentsWHERE wner = SEAPARKAND segment_type = TABLE;seapark用户下的aquatic_animal表的字节数：SELECT sum(bytes)FROM dba_segmentsWHERE wner = SEAPARKAND segment_type = TABLEAND segment_name = AQUATIC_ANIMAL;7. 导出表数据的子集(oracle8i以上)NT系统：exp system/manager query=Where salad_type=FRUIT tables=amy.salad_typefile=fruit log=fruitUNIX系统：exp system/manager query=Where salad_type=FRUIT tables=amy.salad_typefile=fruit log=fruit8. 用多个文件分割一个导出文件exp system/managerfile=(paycheck_1,paycheck_2,paycheck_3,paycheck_4)log=paycheck, filesize=1G tables=hr.paycheck9. 使用参数文件exp system/manager parfile=bible_tables.parbible_tables.par参数文件：#Export the sample tables used for the Oracle8i Database Administrators Bible.file=bible_tableslog=bible_tablestables=(amy.artistamy.booksseapark.checkupseapark.items)10. 增量导出“完全”增量导出(complete)，即备份整个数据库exp system/manager inctype=complete file=990702.dm