So this setting doesn't just force you out after 10 minutes, period? That's what I'm looking for.
So you want to kill a session if it has been logged on for more than 10 minutes regardless of whether it has been idle or not. The only way hat I can think of would be to create a scheduled job that would run every minute / half minute, check the logon_time column of the v$session view and kill any sessions that opened more than 10minutes ago.
Hmmm, that's too bad. I wonder what the difference between IDLE_TIME and CONNECT_TIME are, then.
Strangely, one of our developers using the acct with the PROFILE set in this thread just got a "ORA-02399: exceeded maximum connect time, you are being logged off" error. So, this is working, but I'm not sure how the timing ... is working.
(If you're curious, we have a reporting tool which outputs to the web, and our webserver is set to a 10-minute timeout. So, on occasion a report runs with an unlikely combination of parms causing the report to run beyond that timeout. So, I was trying to kill those requests with this PROFILE setting.)
There's some threshold that I'm unable to understand completely. I created a simple procedure which writes sysdate to a table via a proc declared with autonomous_transaction:
create or replace procedure write_runtime (p_seconds_waited NUMBER, p_recurring NUMBER)
cnt := 0;
for i in 1 .. p_recurring
select count(*) into cnt from vab_sum;
RPTTBL.RPTTBL_PG.WRITE_JOB_LOG ( 'CHUCK', sysdate, null, to_char(cnt));
I've also performed the following so I don't have to wait as long:
SQL> connect forbesc@dev
Enter password: **********
SQL> ALTER PROFILE "IMD_RO_ACT_USER_PROFILE" LIMIT CONNECT_TIME 1;
Now when I run this from my user, I get a timeout at 1 minute. That vab_sum table has about 4,320,910 records in it.
However, if I switch out tables so that I use one with count(*) = 12 , this runs for about 9 minutes. So, I don't know if this is CPU time or not? Seems like the CONNECT_TIME is honored, to the minute, if large SQL statements are being executed, but some other metric is being used if smaller SQL statements are being used.