EXP的RESUMABLE，RESUMABLE_NAME，RESUMABLE_TIMEOUT

RESUMABLE
默认值：n
是否启用resumable space allocation特性。oracle 9i引入特性resumable space allocation。如果一个事务由于空间分配的原因导致挂起，resumable space allocation特性可以使事务继续进行。
如果空间分配出现文件，那么export会等待空间分配问题解决，然后继续进行导出任务。等待时间取决于RESUMABLE_TIMEOUT参数的大小，如果超过这个值，导出仍然会失败。
此参数应与RESUMABLE_NAME和RESUMABLE_TIMEOUT配合使用。


RESUMABLE_NAME
默认值：'User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID’
RESUMABLE_NAME会被插入USER_RESUMABLE或者DBA_RESUMABLE视图中
此参数只有在RESUMABLE=y的情况下才有效。


RESUMABLE_TIMEOUT
默认值：7200 （秒）
在导出的时候发生空间分配错误的最长处理时间。如果在此期间错误没有被解决，导出命令将失败
此参数只有在RESUMABLE=y的情况下才有效。


比如在导出的时候增加排序语句，而此时临时表空间不足，就会引起resumable space allocation的问题。


[oracle@lzl ~]$ cat parfile_lzl.txt 
userid=scott/tiger
file=/home/oracle/exp_lzl.dmp
log=/home/oracle/exp_lzl.log
tables=emp1
buffer=1024
query="order by empno"
resumable=y
resumable_name=resumable_lzl
resumable_timeout=15


SQL> select sum(bytes)/1024/1024 from dba_temp_files;


SUM(BYTES)/1024/1024
--------------------
  5


[oracle@lzl ~]$ exp parfile=parfile_lzl.txt 


Export: Release 11.2.0.3.0 - Production on Sun Jul 9 14:19:09 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           EMP1
EXP-00056: ORACLE error 30032 encountered
ORA-30032: the suspended (resumable) statement has timed out
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Export terminated successfully with warnings.
在15s内没有解决问题，导出失败

重新设置resumable_timeout的大小，在timeout期间解决空间分配问题
[oracle@lzl ~]$ vi parfile_lzl.txt 
[oracle@lzl ~]$ cat parfile_lzl.txt 
userid=scott/tiger
file=/home/oracle/exp_lzl.dmp
log=/home/oracle/exp_lzl.log
tables=emp1
buffer=1024
query="order by empno"
resumable=y
resumable_name=resumable_lzl
resumable_timeout=7200
[oracle@lzl ~]$ exp parfile=parfile_lzl.txt 


Export: Release 11.2.0.3.0 - Production on Sun Jul 9 14:36:06 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                           EMP1
导出命令一直停在这里，查看dba_resumable视图
SQL> select name, status, timeout, sql_text, error_msg from dba_resumable;
NAME STATUS     TIMEOUT
--------------- --------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
ERROR_MSG
--------------------------------------------------------------------------------
resumable_lzl SUSPENDED 7200
SELECT /*+NESTED_TABLE_GET_REFS+*/ "SCOTT"."EMP1".* FROM "SCOTT"."EMP1" order by
 empno
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
TEMP表空间不足，增加临时表空间数据文件大小
SQL> alter database tempfile '/u01/app/oracle/oradata/test/temp02.dbf' resize 500m;
Database altered.
导出命令执行成功
[oracle@lzl ~]$ exp parfile=parfile_lzl.txt 
Export: Release 11.2.0.3.0 - Production on Sun Jul 9 14:36:06 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                           EMP1    8257536 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
所以在导出的时候如果RESUMABLE=y，应该观察dba_resumable视图，查看导出hang住的原因并及时解决。

参考文件
What is the use of the RESUMABLE parameter in EXPORT ? (文档 ID 152013.1)
Using RESUMABLE Session to Avoid Transaction Abort Due to Space Errors (文档 ID 136941.1)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31461640/viewspace-2141784/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31461640/viewspace-2141784/