SCRIPT

How to find cpu and memory information of oracle database server

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...

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...