@CATBLOCK.SQL;
@UTLLOCKT.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;

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;

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';

/* 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;
/