Tablespace management alert script

############################################################################ ## This scripts checks for tablespace usage. If tablespace is 10 percent free, it will send an alert e-mail ## Script to identify %used on tablespace ############################################################################ #!/bin/ksh sqlplus -s <<! oracle/$1@$2 set feed off set linesize 100 set pagesize 200 spool tablespace.alert SELECT F.TABLESPACE_NAME, TO_CHAR ((T.TOTAL_SPACE – F.FREE_SPACE),’999,999′) “USED (MB)”, TO_CHAR (F.FREE_SPACE, ‘999,999’) “FREE (MB)”, TO_CHAR (T.TOTAL_SPACE, ‘999,999’) “TOTAL (MB)”, TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),’999′)||’ %’ PER_FREE FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BLOCKS*(SELECT VALUE/1024 FROM V$PARAMETER WHERE NAME = ‘db_block_size’)/1024) ) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES/1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP … Continue reading Tablespace management alert script

Undo tablespace management

–Undo tablespace management select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB – USAGEMB) FREEMB from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name from dba_data_files a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.contents = ‘UNDO’ group by b.tablespace_name) a, (select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB from DBA_UNDO_EXTENTS c where status <> ‘EXPIRED’ group by c.tablespace_name) b where a.tablespace_name = b.tablespace_name; –show you at any point in time how much UNDO is currently being used by actively┬árunning transactions SELECT (used_ublk * (SELECT block_size FROM dba_tablespaces WHERE contents = ‘UNDO’))/1024/1024 MB FROM v$transaction; ##The query above looks at the total amount of … Continue reading Undo tablespace management

QUICK & EASY TO USE SCRIPTS

set pagesize 200 set linesize 200 col username format a30 col osuser format a20 –Archive log dest. %used col name for a45 SELECT NAME,TO_CHAR(SPACE_LIMIT, ‘999,999,999,999’) AS SPACE_LIMIT, TO_CHAR(SPACE_LIMIT – SPACE_USED + SPACE_RECLAIMABLE, ‘999,999,999,999’) AS SPACE_AVAILABLE, ROUND((SPACE_USED – SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS PERCENT_FULL FROM V$RECOVERY_FILE_DEST; –To check file consuming the most space du -sh * du -h /u01/app/oracle | grep ‘[0-9\.]\+G’ –To get difference between 2 files diff ./initinvprd2.ora /u01/app/oracle/ANTHONY/initinvprd2.ora or diff ./initPROD.ora initTEST.ora –To check cpu usage on OS mpstat 2 100 — Schema_names, tables & Size MB set pagesize 200 set linesize 200 col username format a30 col … Continue reading QUICK & EASY TO USE SCRIPTS