SPACE MONITIRING SCRIPTS

ASM DISK USAGE:


SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off

COLUMN disk_group_name        FORMAT a20           HEAD 'Disk Group Name'
COLUMN disk_file_path         FORMAT a17           HEAD 'Path'
COLUMN disk_file_name         FORMAT a20           HEAD 'File Name'
COLUMN disk_file_fail_group   FORMAT a20           HEAD 'Fail Group'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'File Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

break on report on disk_group_name skip 1

compute sum label ""              of total_mb used_mb on disk_group_name
compute sum label "Grand Total: " of total_mb used_mb on report

SELECT
    NVL(a.name, '[CANDIDATE]')                       disk_group_name
  , b.path                                           disk_file_path
  , b.name                                           disk_file_name
  , b.failgroup                                      disk_file_fail_group
  , b.total_mb                                       total_mb
  , (b.total_mb - b.free_mb)                         used_mb
FROM
    v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
    3
/
------------------------------------------------------------------------------------------------------------------------
TEMP SPACE USAGE:

set serveroutput on
DECLARE
   TS_NAME VARCHAR2(20) := null;
   TEMP_TOTAL NUMBER := 0;
   TEMP_USED NUMBER := 0;
   TEMP_FREE NUMBER := 0;
   PERCENT_USED NUMBER := 0;
BEGIN
SELECT   A.tablespace_name , D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024
         INTO
         TS_NAME,TEMP_TOTAL,TEMP_USED, TEMP_FREE
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = 'TEMP11'
GROUP by A.tablespace_name, D.mb_total;

PERCENT_USED:= ROUND(TEMP_USED*100/TEMP_TOTAL,2);
dbms_output.put_line('Tablespace: ' || TS_NAME || ', TOTAL: ' || TEMP_TOTAL || ', USED: ' || TEMP_USED || ', PERCENT USED: ' || PERCENT_USED);

END;
/
--------------------------------------------------------------------------------------------------------------------

UNDO TABLESPACE USAGE

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;

-----------------------------------------------------------------------------
############################################################################################# -- -- %Purpose: Show Segments with critical Number of Extents, soon reaching MAX_EXTENTS -- -- ############################################################################################# -- clear columns - breaks - computes set pagesize 100 column owner format a15 column segment_name format a20 column segment_type format a20 SELECT owner,segment_name,segment_type,extents,max_extents FROM dba_segments WHERE max_extents <= 10*(extents) AND max_extents != 0; column owner clear column segment_name clear column segment_type clear

-- #############################################################################################
--
-- %Purpose: Show INITIAL, NEXT, Total Extents, Total Blocks of DB-Objects
--
-- #############################################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
spool show_object_storage_info.lst

ttitle 'Object Storage Information' -
skip 2

SELECT SUBSTR(s.owner,1,20) || '.' ||SUBSTR(s.segment_name,1,20) "Object Name",
       SUBSTR(s.segment_type,1,10) "Type",
       SUBSTR(s.tablespace_name,1,10) Tspace,
       NVL(NVL(t.initial_extent, i.initial_extent),r.initial_extent) "FstExt",
       NVL(NVL(t.next_extent,i.next_extent),R.NEXT_EXTENT) "NxtExt",
       s.extents "TotExt",
       s.blocks "TotBlks"
 FROM  dba_rollback_segs R,
       dba_indexes I,
       dba_tables T,
       dba_segments S
WHERE s.segment_type           IN ('CACHE','CLUSTER','INDEX','ROLLBACK','TABLE','TEMPORARY')
  AND s.owner                  NOT IN ('SYSTEM')
  AND s.owner            =     t.owner (+)
  AND s.segment_name     =     t.table_name (+)
  AND s.tablespace_name  =     t.tablespace_name (+)
  AND s.owner            =     i.owner (+)
  AND s.segment_name     =     i.index_name (+)
  AND s.tablespace_name  =     i.tablespace_name (+)
  AND s.owner            =     r.owner (+)
  AND s.segment_name     =     r.segment_name (+)
  AND s.tablespace_name  =     r.tablespace_name (+)
ORDER BY 2,1;
spool off;
set feed on echo off termout on pages 24 verify on
ttitle off

----------------------------------------------------------------------------------------

Check tablespace Usage

REM
clear columns
column tablespace format a20
column total_mb format 999,999,999,999.99
column used_mb format 999,999,999,999.99
column free_mb format 999,999,999.99
column pct_used format 999.99
column graph format a25 heading "GRAPH (X=5%)"
column status format a10
compute sum of total_mb on report
compute sum of used_mb on report
compute sum of free_mb on report
break on report 
set lines 200 pages 100
select  total.ts tablespace,
        DECODE(total.mb,null,'OFFLINE',dbat.status) status,
 total.mb total_mb,
 NVL(total.mb - free.mb,total.mb) used_mb,
 NVL(free.mb,0) free_mb,
        DECODE(total.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total.mb)*100,2),100)) pct_used,
 CASE WHEN (total.mb IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
 ELSE '['|| DECODE(free.mb,
                             null,'XXXXXXXXXXXXXXXXXXXX',
                             NVL(RPAD(LPAD('X',trunc((100-ROUND( (free.mb)/(total.mb) * 100, 2))/5),'X'),20,'-'),
  '--------------------'))||']' 
         END as GRAPH
from
 (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,
 (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free,
        dba_tablespaces dbat
where total.ts=free.ts(+) and
      total.ts=dbat.tablespace_name
UNION ALL
select  sh.tablespace_name, 
        'TEMP',
 SUM(sh.bytes_used+sh.bytes_free)/1024/1024 total_mb,
 SUM(sh.bytes_used)/1024/1024 used_mb,
 SUM(sh.bytes_free)/1024/1024 free_mb,
        ROUND(SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free)*100,2) pct_used,
        '['||DECODE(SUM(sh.bytes_free),0,'XXXXXXXXXXXXXXXXXXXX',
              NVL(RPAD(LPAD('X',(TRUNC(ROUND((SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free))*100,2)/5)),'X'),20,'-'),
                '--------------------'))||']'
FROM v$temp_space_header sh
GROUP BY tablespace_name
order by 1
/

-----------------------------------------------------------------------------------

Checking AUTO EXTEND ON

select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files where TABLESPACE_NAME like 'TS__'; 

Check the values in AUTOEXTENSIBLE column; if it’s NO this feature is not enabled on the file in column FILE_NAME.