Oracle PL/SQL/System Tables Views/V statname

Prints the total UGA for your environment

    
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

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

    
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>

Show when the current user was last active.

    
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>