Apps DBA

SQL Queries to Check Active or Inactive Sessions Total Count of Sessions: Check Active or Inactive SessionsSELECT Count(s.status) TOTAL_SESSIONSFROM gv$session s; Total Count of Inactive … Read more

Query To Find Database Redolog Temp And Control File Size SELECT ‘Full Data Size: ‘|| SUM(bytes) / 1024 / 1024 / 1024|| ‘ GBytes’ FULL_DATABASE_SIZEFROM … Read more

Query To Find Database Size QUERY TO FIND DATABASE SIZESELECT SUM(tot.bytes / ( 1024 * 1024 * 1024 ))“Total size iN GBs”,SUM(tot.bytes / ( 1024 … Read more

Query To Find Long Running SQL Statements SELECT s.rows_processed,s.loads,s.executions,s.buffer_gets,s.disk_reads,t.sql_text,s.MODULE,s.actionFROM v$sql/area/ s,v$sqltext tWHERE s.address = t.addressAND ( ( buffer_gets > 10000000 )OR ( disk_reads > 1000000 … Read more

Query To Find Undo Tablespace Usage SELECT s.username,s.sid,rn.name,rs.rssize / 1024 / 1024 “Size in MB”,rs.status,t.used_ublk,t.used_urec,do.object_nameFROM v$transaction t,v$session s,v$rollname rn,v$rollstat rs,v$locked_object lo,dba_objects DOWHERE t.addr = s.taddrAND … Read more

Query To Check For Table Locks SELECT a.sid,a.serial#,a.username,c.os_user_name,a.terminal,b.object_id,Substr(b.object_name, 1, 40) object_nameFROM v$session a,dba_objects b,v$locked_object cWHERE a.sid = c.session_idAND b.object_id = c.object_id; Read More Articles Query … Read more

Query To Find Active Sessions In Database SELECT ‘ACTIVE SESSION’,sid,serial#,machine,osuser,username,statusFROM v$sessionWHERE username != ‘NULL’AND status = ‘ACTIVE’; To find active sessions in database is very … Read more

Query To Check Users, Sessions, CPU, and I/O Consumption SELECT p.spid, SPID,To_char(s.logon_time, ‘DDMonYY HH24:MI’) date_login,s.username,Decode(Nvl(p.background, 0), 1, bg.description,s.program), program,ss.value / 100, CPU,physical_reads, disk_io,( Trunc(SYSDATE, ‘J’) … Read more

Query To Check Open Cursor By User SELECT SUM(a.value) total_cur,Avg(a.value) avg_cur,Max(a.value) max_cur,s.username,s.machineFROM v$sesstat a,v$statname b,v$session sWHERE a.statistic# = b.statistic#AND s.sid = a.sidAND b.name = ‘opened … Read more

Query To Check Top 10 Database Load Queries SELECT *FROM (SELECT sql_text,cpu_time / 1000000 cpu_time,elapsed_time / 1000000 elapsed_time,disk_reads,buffer_gets,rows_processedFROM v$sqlareaORDER BY cpu_time DESC,disk_reads DESC)WHERE ROWNUM < … Read more