DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: how long has the session been connected

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Does anyone know how can we determine the connection of a session? Using database event triggers or any views :-?

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    Look in v$session

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Code:
    SQL> l
      1  select username, logon_time from v$session
      2* where username is not null
    SQL> /
    
    USERNAME                       LOGON_TIME
    ------------------------------ -------------------
    SYS                            2001/06/25 14:07:07
    SYSTEM                         2001/06/25 14:07:16
    DBSNMP                         2001/06/25 14:07:19
    Jeff Hunter

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    nono i mean the total connection time

  5. #5
    Join Date
    Nov 2000
    Posts
    344
    Hi Pando,

    What do you mean? Will sysdate - logon_time do it?

    86400 * (to_number(sysdate) - to_number(logon_time))

    will give you the amount of time in seconds that a session
    has been connected to the database.

    -John

  6. #6
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    well, shouldn't that be the difference between the logon_time and the current_time (sysdate) ?

    May be we are missing the point altogether ???

    - Rajeev
    Rajeev Suri

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    We want to store in a table the connection time of each session.
    I have created a log_info table with these columns

    logon_time
    username
    osuser
    program
    audit_id
    logoff_time

    I have created two triggers, one after logon and one for before logoff. I have used audsid for this purpose, the trigger is smth like (I dont have the codes here)


    create or replace trigger logon_trg after logon on database
    declare
    session_rec v$session%rowtype;
    begin

    select *
    into session_rec
    from v$session
    where audsid=userenv('sessionid');

    if
    session_rec.program = 'SQLPLUSW.EXE'
    then
    insert into lsc.log_info
    values(sysdate, user, session_rec.osuser, session_rec.program, userenv('sessionid'), null);
    end if;
    end;
    /

    create or replace trigger logoff_trg before logoff on database
    declare
    session_rec v$session%rowtype;
    begin

    select *
    into session_rec
    from v$session
    where audsid=userenv('sessionid');

    if
    session_rec.program = 'SQLPLUSW.EXE'
    then
    update lsc.log_info
    set logoff_time=sysdate /*here I could use sysdate-logon_time*/
    where audit_id=userenv('sessionid');
    end if;
    end;
    /


    I have tried this and it works however I am trying with 3 or 4 sessions only, since this table is not being truncated after long time obviously my audit_id will be duplicated (I am using it here as a candidate key), I need to know if there is another way to obtain the connection of a session

    Cheers

    [Edited by pando on 06-25-2001 at 04:50 PM]

  8. #8
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Hi Pando,
    The session_id should be unique for each session until the database is recycled. So what you may want to do is create a Sequence and increment it whenever the database is STARTUP. And then use the combination of that Sequence and Session_id as the PK for your log_info table.
    What do you think ?

    - Rajeev
    Rajeev Suri

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    I have thoght about using sequence as well but I dont know how you can make the trigger to *memorize* the sequence of each session when it comes to the update part and update the correct row!

    I am not very good in programming... maybe my logic is wrong

    Also I have noticed that if I sqlplus crashes the update trigger doenst work

    [Edited by pando on 06-25-2001 at 04:51 PM]

  10. #10
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Well what you need to do is as following --

    1. Create a Sequence (let's say log_seq)
    2. Create a database Startup Trigger that will increment the sequence whenever the database is brought up --

    create or replace trigger t1 after startup on database
    ...code to increment the sequence...something like ...SELECT log_seq.NextVal INTO :x from DUAL;

    3. In your Update trigger (logon_trg and logof_trg) you need to use the log_Seq.CurrVal to insert/compare the current value of the Sequence.

    - Rajeev

    Rajeev Suri

Posting Permissions

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


Click Here to Expand Forum to Full Width