I am on 12.1.0.2
The issue is that with RESUMABLE_TIMEOUT init parameter set to 0, I am
enable resumable for session via a logon trigger. I have tried a few
incarnations of logon trigger syntax with the same results. Timeout is
being set and resumable is being enabled even if the resumable privilege
has not been granted.
SQL> show user
USER is "SYS"
SQL> show parameter resum
NAME TYPE VALUE
------------------------------------ --------------------------------
------------------------------
resumable_timeout integer 0
SQL> connect restxn_admin
Connected.
SQL> @cr_logon_trigger_final.sql
Trigger created.
SQL> revoke resumable from hrp
2 /
Revoke succeeded.
SQL> connect hrp/hrp
Connected.
SQL> select dbms_resumable.get_timeout from dual;
GET_TIMEOUT
-----------
600
SQL> connect restxn_admin
Connected.
SQL> grant resumable to hrp
2 /
Grant succeeded.
SQL> connect hrp/hrp
Connected.
SQL> select dbms_resumable.get_timeout from dual;
GET_TIMEOUT
-----------
600
SQL> spool off
$ cat cr_logon_trigger_final.sql
CREATE OR REPLACE TRIGGER grant_restxn
AFTER LOGON ON DATABASE
DECLARE
v_match varchar2(1) := 'Y';
sqlstr VARCHAR2(1000) :='';
v_priv user_sys_privs.privilege%type;
BEGIN
SELECT privilege
INTO v_priv
FROM user_sys_privs
WHERE privilege = 'RESUMABLE';
-- dbms_output.put_line('v_priv = '||v_priv);
IF v_priv = 'RESUMABLE'
THEN
sqlstr := 'ALTER SESSION ENABLE RESUMABLE TIMEOUT 600';
dbms_output.put_line('sqlstr = '||sqlstr);
execute immediate sqlstr;
ELSE
-- dbms_output.put_line('null');
NULL;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- dbms_output.put_line('no data found');
NULL;
WHEN OTHERS THEN
-- dbms_output.put_line('exception');
NULL;
END;
/
I have also tailed the alert.log while running a test query to confirm that
the dbms_resumable.get_timeout function isn't the issue. When the output
from the function is 600, I am seeing a suspended txn. When the output is
-1, the statement fails.
What is going on here?
Henry
