《EXP命令：参数_举例_注意事项_问题与解决(整理汇)》由会员分享，可在线阅读，更多相关《EXP命令：参数_举例_注意事项_问题与解决(整理汇)（7页珍藏版）》请在金锄头文库上搜索。
1、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.Keyword Description
2、 (Default) -USERID username/password FULL export entire file (N)BUFFER size of data buffer OWNER list of owner usernamesFILE output files (EXPDAT.DMP) TABLES list of table namesCOMPRESS import into one extent (Y)RECORDLENGTH length of IO recordGRANTS export grants (Y) INCTYPE incremental export type
3、INDEXES export indexes (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 filenameCONSISTENT cross-table consistency(N)CONSTRAINTS export constraints (Y)OBJ
4、ECT_CONSISTENT transaction set to read only during object export (N)FEEDBACK display progress every x rows (0)FILESIZE maximum size of each dump fileFLASHBACK_SCN SCN used to set session snapshot back toFLASHBACK_TIME time used to get the SCN closest to the specified timeQUERY select clause used to
5、export a subset of a tableRESUMABLE suspend when a space related error is encountered(N)RESUMABLE_NAME text string used to identify resumable statementRESUMABLE_TIMEOUT wait time for RESUMABLETTS_FULL_CHECK perform. full or partial dependency check for TTSVOLSIZE number of bytes to write to each tap
6、e volumeTABLESPACES list of tablespaces to exportTRANSPORT_TABLESPACE export transportable tablespace metadata (N)TEMPLATE template name which invokes iAS mode exportExample: 1. 将数据库中 system 用户与 sys 用户的表导出expsystem/managerTESTfile=d:daochu.dmp wner=(system,sys)2 导出一个完整数据库exp system/manager file=bibl
7、e_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)注意：在导出用户时，尽管已经得到了这个用户的所有对象，但是还是不 能得到这些对象引用的
8、任何同义词。解决方法是用以下的 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 =
9、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 用户所属表
10、的总字节数：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_ty
11、pe=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/man
12、ager 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.dmp“增量型”增量导出(incremental)，即备份上一次备份后改变的数据exp system/manager inctype=incremental file=990702.dmp“累计型”增量导出(cumulative)，即备份上一次“完全”导出之后改变 的数据exp system/manager inctype=cumulative file=990702.dmp
