Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: PROFILES not working

    So, I think I have everything set up, as instructed, but I'm not getting the timeout error I was expecting. Any ideas?

    Code:
    SQL> connect forbesc@dev
    Enter password: **********
    Connected.
    SQL> show parameter resource_limit
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    resource_limit                       boolean     TRUE
    
    SQL> select version from v$instance;
    
    VERSION
    -----------------
    10.1.0.4.0
    
    SQL> SELECT * FROM DBA_PROFILES
      2  where profile = 'IMD_RO_ACT_USER_PROFILE' and RESOURCE_NAME = 'CONNECT_TIME';
    
    PROFILE                        RESOURCE_NAME                    RESOURCE
    ------------------------------ -------------------------------- --------
    LIMIT
    ----------------------------------------
    IMD_RO_ACT_USER_PROFILE        CONNECT_TIME                     KERNEL
    10
    
    
    SQL> select username, profile
      2  from dba_users where profile = 'IMD_RO_ACT_USER_PROFILE';
    
    USERNAME                       PROFILE
    ------------------------------ ------------------------------
    RO_AC_UCRM                    IMD_RO_ACT_USER_PROFILE
    
    SQL> connect ro_ac_ucrm@dev
    Enter password: ***********
    Connected.
    
    SQL> select to_char(sysdate,'mm/dd/yy hh24:mi:ss') from dual
      2  ;
    
    TO_CHAR(SYSDATE,'
    -----------------
    07/09/08 12:08:01
    
    SQL> /
    
    TO_CHAR(SYSDATE,'
    -----------------
    07/09/08 12:17:53
    
    SQL> /
    
    TO_CHAR(SYSDATE,'
    -----------------
    07/09/08 12:23:07
    Last edited by chuck_forbes; 07-10-08 at 16:55.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I am not seeing 10 minutes of inactivity. I see 9 minutes, but not 10
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    There are 3 times listed there, you might have to scroll in the CODE window:
    07/09/08 12:08:01
    07/09/08 12:17:53
    07/09/08 12:23:07

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by chuck_forbes
    There are 3 times listed there, you might have to scroll in the CODE window:
    07/09/08 12:08:01
    07/09/08 12:17:53
    07/09/08 12:23:07
    Which gives you 9 minutes of inactivity then 6 minutes of inactivity. Stay inactive for a FULL 10 consecutive minutes

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    So this setting doesn't just force you out after 10 minutes, period? That's what I'm looking for. I would guess that the IDLE_TIME attribute does what you're saying.

    Plus, here's 10+ minutes of inactivity
    Code:
    SQL> connect ro_ac_ucrm@dev
    Enter password: ***********
    Connected.
    SQL> select to_char(sysdate, 'HH24:MI:SS') from dual;
    
    TO_CHAR(
    --------
    07:29:47
    
    SQL> /
    
    TO_CHAR(
    --------
    07:54:34
    --=cf
    Last edited by chuck_forbes; 07-10-08 at 16:54.

  6. #6
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by chuck_forbes
    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.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    He is right, connect_time in the profile should do that.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    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.)

    --=Chuck

  9. #9
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    According to the documentation, this setting should do what you are looking for:

    CONNECT_TIME
    Specify the total elapsed time limit for a session, expressed in minutes.

    IDLE_TIME
    Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.

    Do you have any other resource limits set in that profile that you test? Also, I wonder if this limit makes a distinction between elasped wall time and elapsed CPU time?

    Interesting that you had a developer finaly get the time-out. Not sure why it wouldn't work in the other test case.

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    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:

    Code:
    create or replace procedure write_runtime (p_seconds_waited NUMBER, p_recurring NUMBER)
    as
      cnt NUMBER;
    begin
      cnt := 0;
      for i in 1 .. p_recurring
      loop
         dbms_lock.sleep(p_seconds_waited);
         select count(*) into cnt from vab_sum;
         RPTTBL.RPTTBL_PG.WRITE_JOB_LOG ( 'CHUCK', sysdate, null, to_char(cnt));
      end loop;
    end write_runtime;
    I've also performed the following so I don't have to wait as long:
    Code:
    SQL> connect forbesc@dev
    Enter password: **********
    Connected.
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •