WARNING: This web is not used anymore. Please use PDBService.ConnectionsByOsUsername instead!
 
#
# Shows connections for some users grouped by their duration for last 2 days
#
create or replace view cms_listconn as
  select count(*) total, os_username,trunc(logoff_time, 'DD') day, round(((logoff_time-timestamp)*86400), -3) seconds
    from dba_audit_session
    where action_name='LOGOFF'
      and username like 'CMS%'
      and os_username in ('phedex', 'phtab')
      and trunc(logoff_time, 'DD') in ( trunc(sysdate-1, 'DD'), trunc(sysdate-2, 'DD'), trunc(sysdate, 'DD'))
    group by os_username, trunc(logoff_time, 'DD'), round(((logoff_time-timestamp)*86400), -3)
    order by seconds, os_username;

#
# Show connections for some users with less than 100 secons for last 2 days
#
create or replace view cms_shortconn as
  select username, os_username, timestamp, round((logoff_time-timestamp)*86400) duration
    from dba_audit_session
    where action_name='LOGOFF'
      and os_username in ('phedex', 'phtab')
      and trunc(logoff_time, 'DD') in ( trunc(sysdate-1, 'DD'), trunc(sysdate-2, 'DD'), trunc(sysdate, 'DD'))
      and round(((logoff_time-timestamp)*86400), -2)

# define public synonym
create public synonym cms_listconn for cms_listconn;
create public synonym cms_shortconn for cms_shortconn;

# grant select to one user
grant select on cms_listconn to cms_transfermgmt;
grant select on cms_shortconn to cms_transfermgmt;

# test
select * from cms_listconn;
select * from cms_shortconn;

-- MiguelAnjo - 21 Mar 2005

Edit | Attach | Watch | Print version | History: r4 < r3 < r2 < r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r4 - 2005-11-29 - unknown
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    PSSGroup All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback