Young Haung, thanks a lot for your patient and tests.
I´m opened a SR on MOS and support replied me that, along with setting
the resumable_timeout
parameter, *he have* also to perform a *ALTER SESSION ENABLE RESUMABLE
*(explicity
or implicity for all/some sessions with a LOGON TRIGGER) in Oracle RDBMS
11gR2.
And we did several tests here (in both 11.2.0.2 and 11.2.0.4) and we
conclude exactly as you: setting resumable_timeout instance wide is enough!
But the Oracle analyst said that the documentation says this.
I checked 10g, 11gR1 and 11gR2 docs. And, surprising, 10g and 11gR1 docs
says, OK, that setting resumable_timeout instance wide is enough... but
11gR2 docs don't!!!
Look:
*10g: *
Resumable Space Allocation Overview
...
1. A statement executes in a resumable mode only if its session has been
enabled for resumable space allocation by one of the following actions:
o The RESUMABLE_TIMEOUT initialization parameter is set to a nonzero value.
o The ALTER SESSION ENABLE RESUMABLE statement is issued.
http://docs.oracle.com/cd/B19306_01/server.102/b14231/schema.htm#i1006595
*11gR1 *
Resumable Space Allocation Overview
...
A statement executes in a resumable mode only if its session has been
enabled for resumable space allocation by one of the following actions:
o
The RESUMABLE_TIMEOUT initialization parameter is set to a nonzero value.
o
The ALTER SESSION ENABLE RESUMABLE statement is issued.
http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema002.htm#i1006595
But
11gR2 has a different text:
*11gR2 *
How Resumable Space Allocation Works
A statement executes in resumable mode only if its session has been enabled
for resumable space allocation by one of the following actions:
o
The ALTER SESSION ENABLE RESUMABLE statement is issued in the session
before the statement executes when the RESUMABLE_TIMEOUT initialization
parameter is set to a nonzero value.
o
The ALTER SESSION ENABLE RESUMABLE TIMEOUT timeout_value statement is
issued in the session before the statement executes, and the timeout_value
is a nonzero value.
http://docs.oracle.com/cd/E11882_01/server.112/e25494/schema.htm#ADMIN11582
*--*
*Att*
*Luis Santos*
2016-10-21 12:33 GMT-02:00 Yong Huang <yong321@xxxxxxxxx>:
1. In Oracle 10g+, along setting the *resumable_timeout *parameter*to
system wide, we still have to issue an *alter session enable resumable
use this feature?
Luis,
My test in 11.2.0.3 and 12.1.0.2 shows that you don't have to. A
system-wide non-zero value for this parameter will enable resumable with
this timeout value for the session that runs out of space. But if the
session explicitly enables resumable, it will have a timeout of 7200
seconds (if not set) or whatever timeout it explicitly sets. Confusingly,
in the former case, dba_resumable only shows the session already starting
to hang waiting for more space, while in the latter case, the view shows
the session as soon as it enables resumable.
Yong Huang
