Below script is useful in getting CPU, memory and core, socket information of a database server from SQL prompt. SCRIPT: set pagesize 299 set lines 299 select STAT_NAME,to_char(VALUE) as VALUE ,COMMEN...
SCRIPT
Find sessions consuming lot of CPU
Use below query to find the sessions using a lot of CPU. col program form a30 heading "Program" col CPUMins form 99990 heading "CPU in Mins" select rownum as rank, a.* from ( SELECT v.sid, program, v....
Get ospid from sid
Below is the script to find the os server process id of a SID. set lines 123 col USERNAME for a15 col OSUSER for a8 col MACHINE for a15 col PROGRAM for a20 select b.spid, a.username, a.program , a.osu...
Get hourly database growth report
Below script will generate the database size for past few days(per hour) set serveroutput on Declare v_BaselineSize number(20); v_CurrentSize number(20); v_TotalGrowth number(20); v_Space number(20); ...
Find sessions holding library cache lock
Below are the scripts for finding the sessions holding libary cache lock: For standalone db: select sid Waiter, p1raw, substr(rawtohex(p1),1,30) Handle, substr(rawtohex(p2),1,30) Pin_addr from v$sessi...