Pages

Thursday, September 27, 2012

Verify Database 32/64 bit creation


to check  database been created originally in a 32-bit environment and is now on a 64-bit platform


select decode(instr(metadata, 'B023'),
              0,
              '64bit Database',
              '32bit Database') "DB Creation"
  from kopm$;



Saturday, September 8, 2012

DB Buffer Usage Query


-- DB Buffer Usage


select decode(state,
            0, 'Free',
            1, decode(lrba_seq, 0, 'Available', 'Being Modified'),     
            2, 'Not Modified',
            3, 'Being Read',
               'Other') block_status,
            count(*) "count"
from sys.x$bh
group by decode(state,
            0, 'Free',
            1, decode(lrba_seq, 0, 'Available', 'Being Modified'),     
            2, 'Not Modified',
            3, 'Being Read',
       'Other');



CPU USAGE BY current Users


--- CPU USAGE BY USER


        SELECT s.sid,
     s.serial#,
     nvl(s.username, '[ORACLE PROCESS]') user_name,
     s.osuser os_user,
     k.ksusestv cpu_usage,
     s.program,
     s.client_info,
     s.module,
     s.machine,
     s.action,
     s.logon_time
FROM v$session s,
     sys.x$ksusesta k
WHERE k.indx = s.sid
      AND k.ksusestn = 12
  AND s.type != 'BACKGROUND'
ORDER BY k.ksusestv DESC