Oracle PL/SQL/System Tables Views/V statname

Prints the total UGA for your environment

   <source lang="sql">
   

SQL> SQL> SELECT SUBSTR (a.NAME, 9, 10) "Name",ROUND (SUM (b.VALUE) / 1024 / 1024,1)|| " M" "Total UGA for all sessions"

 2      FROM v$statname a, v$sesstat b
 3     WHERE a.statistic# = b.statistic# AND a.NAME = "session uga memory"
 4  GROUP BY a.NAME;

Name Total UGA for all sessions

---

------------------------------------------

uga memory 3.5 M 1 row selected. SQL> SQL> SET FEEDBACK ON SQL> SQL> spool off

 </source>
   
  

Show when the current user connected to the system (not available on all platforms).

   <source lang="sql">
   

SQL> SQL> select SID,TO_CHAR(SysDate - (Hsecs-S.Value)/(24*3600*100),"MM/DD/YYYY HH24

MI:SS") Connection_Time

 2    from V$SESSTAT S, V$STATNAME N, V$TIMER
 3   where N.Name = "session connect time"
 4     and N.Statistic# = S.Statistic#
 5     and S.Value != 0;

      SID  CONNECTION_TIME

---

-------------------

        1  03/20/2010 17:59:14

1 row selected. SQL> SQL>

 </source>
   
  

Show when the current user was last active.

   <source lang="sql">
   

SQL> SQL> select SID,TO_CHAR(SysDate - (Hsecs-S.Value)/(24*3600*100),"MM/DD/YYYY HH24

MI:SS") Last_Non_Idle_Time

 2  from V$SESSTAT S, V$STATNAME N, V$TIMER
 3  where N.Name = "process last non-idle time"
 4  and N.Statistic# = S.Statistic#
 5  and S.Value != 0;

      SID  LAST_NON_IDLE_TIME

---

-------------------

        1  03/20/2010 17:59:15

1 row selected. SQL> SQL>

 </source>