{{parent page="Oracle"}}

===ID-ing deadlocks===
cd $ORACLE_HOME/rdbms/admin
connect as sys
%%(sql)
@CATBLOCK.SQL; 
@UTLLOCKT.SQL;
%%

===List active SQL===
%%(sql)
select s.sid,
	   s.serial#,
	   s.username,
	   optimizer_mode,
	   hash_value,
	   address,
	   sql_text
  from v$sqlarea sqlarea, v$session s
 where s.sql_hash_value = sqlarea.hash_value
   and s.sql_address    = sqlarea.address
   and s.username is not null;
%%

=== Check tablespace activity ===
%%(sql)
SELECT s.sid, s.serial#, s.username, s.program,i.block_changes, i.block_gets 
FROM v$session s, v$sess_io i 
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;

SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;
%%


===  Check excessive read & related SQL  ===
%%(sql)
select '    ' || b.username usr, a.disk_reads drd,sql_text sqltxt 
from v$sqlarea a, dba_users b where disk_reads > 10000 
	    and a.parsing_user_id = b.user_id order by disk_reads desc
	    and usr = 'SOME_USER';
%%

=== Freespace monitor script ===

%%(sql;freespace.sql)
/* SP_SIMPLE_MAILER */
create or replace procedure SP_SIMPLE_MAILER(
from_name varchar2, 
to_name varchar2,
subject varchar2,
message varchar2,
smtp_host varchar2) is

conn UTL_SMTP.CONNECTION;
mesg varchar2(32767);
crlf varchar2(2):= chr(13) || chr(10);

begin
-- Open the SMTP connection ...
-- ------------------------
conn:= utl_smtp.open_connection( smtp_host, 25);

-- Initial handshaking ...
-- -------------------
utl_smtp.helo( conn, smtp_host );
utl_smtp.mail( conn, from_name );
utl_smtp.rcpt( conn, to_name );
utl_smtp.open_data ( conn );

mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || from_name || crlf ||
'Subject: ' || subject || crlf ||
'To: ' || to_name || crlf || 
crlf || message;

-- build the start of the mail message ...
-- -----------------------------------
utl_smtp.write_data ( conn, mesg );
utl_smtp.close_data( conn );
utl_smtp.quit( conn );

end;
/

/* SP_TBS_LOW_SPACE_ALERT */
create or replace PROCEDURE SP_TBS_LOW_SPACE_ALERT IS
CURSOR sql_f_space IS 
SELECT D.TABLESPACE_NAME tbs_name,
D.STATUS tbs_status,
ROUND((A.BYTES/1024/1024),2) total_size,
ROUND(((A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),2) used_size,
ROUND(((A.BYTES/1024/1024)-(A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),2) free_size,
ROUND((((A.BYTES/1024/1024)-(A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024)/(A.BYTES/1024/1024))*100.0,2) pct_free
FROM SYS.DBA_TABLESPACES D,SYS.SM$TS_AVAIL A,SYS.SM$TS_FREE F
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME
AND F.TABLESPACE_NAME(+)=D.TABLESPACE_NAME
UNION ALL
SELECT d.tablespace_name tbs_name,
d.status tbs_status,
ROUND((a.bytes / 1024 / 1024),2) total_size,
ROUND(NVL(t.bytes, 0)/1024/1024,2) used_size,
ROUND((a.bytes / 1024 / 1024) - (NVL(t.bytes,0)/1024/1024),2) free_size,
ROUND(100 - (NVL(t.bytes /a.bytes * 100, 0)),2) pct_free
FROM sys.dba_tablespaces d,
( select tablespace_name, sum(bytes) bytes
from dba_temp_files group by tablespace_name) a,
( select tablespace_name, sum(bytes_cached) bytes
from SYS.v_$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY';

tbs_sp_rec sql_f_space%ROWTYPE;

crlf varchar2(2):= chr(13) || chr(10);
message varchar2(32767);

-- email VARCHAR2(50) := null;
BEGIN
FOR tbs_sp_rec IN sql_f_space LOOP
  IF (tbs_sp_rec.pct_free <= 10) THEN
	message:= 'Detected some tablespace to have pct_free of under 10' || crlf ||
	'tablespace_name: ' || tbs_sp_rec.tbs_name || crlf ||
	'used/total (MB): ' || tbs_sp_rec.used_size || '/' || tbs_sp_rec.total_size || crlf ||
	'pct_free: ' || tbs_sp_rec.pct_free || crlf;
	SP_SIMPLE_MAILER('newdb@ecvision.com','sysadmin@ecvision.com', 'newdb tbs low space warning', message, 'mail1.ecvision.com');
  END IF;
END LOOP;
END;
/

/* Add job to run SP_TBS_LOW_SPACE_ALERT daily*/
/*
  Daily: 'SYSDATE +1'
  Hourly: 'SYSDATE + 1/24'
  Every 10min: 'SYSDATE + 10/1440'
  Every 30sec: 'SYSDATE + 30/86400'
  3rd argument
  TO_DATE('Thursday, October 28, 2004 2 10 00 PM', 'DAY, MONTH DD, YYYY HH:MI:SS AM')
*/
DECLARE jobno number; 
BEGIN DBMS_JOB.SUBMIT(
jobno, 
'SP_TBS_LOW_SPACE_ALERT;', 
TO_DATE(SYSDATE+1), 
'SYSDATE +1'); 
COMMIT; 
END;
/

/* Checking job */
select job, next_date, next_sec, failures, broken, substr(what,1,40) description from user_jobs;

/* remove job */
BEGIN
DBMS_JOB.REMOVE(1);
COMMIT;
END;
/
%%