Oracle Apps DB: Active Sessions

SELECT DISTINCT fu.user_name user_name              
               ,to_char(ic.first_connect, 'DD-Mon-YYYY HH12: MI: SS AM') "First conn"
                ,to_char(ic.last_connect, 'DD-Mon-YYYY HH12: MI: SS AM')"Last conn"
                ,decode((ic.disabled_flag), 'N', 'ACTIVE', 'Y', 'INACTIVE') status
                ,fvl.responsibility_name "Resp"
                ,ic.function_type
                ,ic.time_out
                ,fu.user_id
                ,ic.org_id
                ,fr.menu_id
  FROM fnd_user                   fu
      ,fnd_responsibility         fr
      ,icx_sessions               ic
      ,apps.fnd_responsibility_vl fvl
 WHERE fu.user_id = ic.user_id
   AND fr.responsibility_key = fvl.responsibility_key
   AND fr.responsibility_id = ic.responsibility_id
   AND ic.disabled_flag = 'N'
   AND ic.responsibility_id IS NOT NULL
   AND ic.last_connect > SYSDATE - (ic.time_out / 60) / 96;

NOTE: Copied Code. 

Oracle DB : TableSpace Used Details

SELECT df.tablespace_name "Tablespace",
  totalusedspace "Used MB",
  (df.totalspace - tu.totalusedspace) "Free MB",
  df.totalspace "Total MB",
  ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
FROM
  (SELECT tablespace_name,
    ROUND(SUM(bytes) / 1048576) TotalSpace
  FROM dba_data_files
  GROUP BY tablespace_name
  ) df,
  (SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,
    tablespace_name
  FROM dba_segments
  GROUP BY tablespace_name
  ) tu
WHERE df.tablespace_name = tu.tablespace_name
--and rownum < 5
ORDER BY 1;

NOTE: Copied Code. 

Oracle DB: CPU Usage By USER

Displays CPU usage for each User. Useful to understand database load by user.


SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
    FROM v$session ss, v$sesstat se, v$statname sn
   WHERE     se.STATISTIC# = sn.STATISTIC#
         AND NAME LIKE '%CPU used by this session%'
         AND se.SID = ss.SID
         AND ss.status = 'ACTIVE'
         AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

NOTE: Copied Code.