Tablespace Usage

Below query can be used to find the tablespace usage along with the consideration of Autoextend being enabled.



whenever sqlerror exit sql.sqlcode

alter session set nls_date_format='dd/mm/yyyy:hh24:mi:ss';
set pagesize 200 linesize 120 trimspool on heading on feedback off echo off

column TABLESPACE_NAME        format A30            heading 'Tablespace|Name'
column ALLOCATED_SIZE_MO      format 999,999,990.99 heading 'space|Allocated'
column MAX_SIZE_MO            format 999,999,990.99 heading 'space|Maximum'
column FREE_SIZE_MO           format 999,999,990.99 heading 'space|Free'
column USED_SIZE_MO           format 999,999,990.99 heading 'space|Utilised'
column PERCENT_USED           format 990.99         heading '%|Used'
column PERCENT_USED_MAX_SIZE  format 990.99         heading '% Used|of Max'

TTITLE OFF
TTITLE left _date center "Rapport Tablespace Space Utilisation (MO)" skip 2

select
TABLESPACE_NAME,
SUM( USED_SIZE_MO ) AS USED_SIZE_MO ,
SUM ( FREE_SIZE_MO ) AS FREE_SIZE_MO ,
SUM( ALLOCATED_SIZE_MO)  AS ALLOCATED_SIZE_MO,
SUM ( MAX_SIZE_MO ) AS MAX_SIZE_MO,
SUM( USED_SIZE_MO ) / SUM( ALLOCATED_SIZE_MO) * 100  AS PERCENT_USED,
DECODE ( NVL( SUM ( MAX_SIZE_MO ), 0) ,
                     0 , 0 ,
                         SUM( USED_SIZE_MO ) / SUM ( MAX_SIZE_MO ) * 100
       )
AS PERCENT_USED_MAX_SIZE
from
(
select
A.TABLESPACE_NAME,
A.FILE_NAME,
'NO' AS TEMP_FILE ,
--round ( C.BLOCK_SIZE/1024 , 2 ) AS BLOCK_SIZE_KO,
DECODE ( NVL( MAXBYTES, 0),
         0 , round ( BYTES/1024/1024 , 2 ),
             round ( MAXBYTES/1024/1024 , 2 )
       )
AS MAX_SIZE_MO,
round ( BYTES/1024/1024 , 2 ) AS ALLOCATED_SIZE_MO,
DECODE ( NVL(FREE_SIZE_MO_BIS,0),
                  0 , round ( BYTES/1024/1024 , 2 ) ,
                      round ( BYTES/1024/1024 - FREE_SIZE_MO_BIS , 2 ) )
AS USED_SIZE_MO,
DECODE ( NVL(FREE_SIZE_MO_BIS,0) ,
                  0 , 0 ,
                      round ( FREE_SIZE_MO_BIS , 2 ) )
AS FREE_SIZE_MO,
DECODE (
       AUTOEXTENSIBLE ,
       'NO', DECODE (
                     NVL(FREE_SIZE_MO_BIS,0) ,
                     0 , round ( ( BYTES/1024/1024  ) / ( BYTES/1024/1024 ) * 100 , 2 ) ,
                         round ( ( BYTES/1024/1024 - FREE_SIZE_MO_BIS ) / ( BYTES/1024/1024 ) * 100 , 2 )
                     ),
       'YES', DECODE (
                     NVL(FREE_SIZE_MO_BIS,0) ,
                     0 , round ( ( BYTES/1024/1024 ) / ( MAXBYTES/1024/1024 ) * 100 , 2 ) ,
                         round ( ( BYTES/1024/1024 - FREE_SIZE_MO_BIS ) / ( MAXBYTES/1024/1024 ) * 100 , 2 )
                     )
        )
AS PERCENT_USED,
AUTOEXTENSIBLE ,
round ( BYTES/BLOCKS/1024  * INCREMENT_BY , 2 ) AS FILE_EXTENT_SIZE_KO,
round ( NEXT_EXTENT/1024 , 2 ) AS NEXT_KO
from dba_data_files A,
(select FILE_ID,SUM(BYTES/1024/1024) AS FREE_SIZE_MO_BIS from DBA_FREE_SPACE GROUP BY FILE_ID) B,
DBA_TABLESPACES C
where
B.FILE_ID (+)= A.FILE_ID
and
A.TABLESPACE_NAME = C.TABLESPACE_NAME and a.TABLESPACE_NAME like '%&TBSNAME%'
union
select
A.TABLESPACE_NAME,
A.FILE_NAME,
'YES' AS TEMP_FILE ,
--round ( C.BLOCK_SIZE/1024 , 2 ) AS BLOCK_SIZE_KO,
DECODE ( NVL( A.MAX_BYTES_MO, 0),
         0 , A.BYTES_MO,
             A.MAX_BYTES_MO
       )
AS MAX_SIZE_MO,
A.BYTES_MO ALLOCATED_SIZE_MO,
NVL(B.BYTES_CACHED_MO,0) USED_SIZE_MO,
A.BYTES_MO - NVL(B.BYTES_CACHED_MO,0) FREE_SIZE_MO,
DECODE ( A.AUTOEXTENSIBLE ,
         'NO' , round( (NVL(B.BYTES_CACHED_MO,0) / A.BYTES_MO ) * 100 , 2),
         'YES', round( (NVL(B.BYTES_CACHED_MO,0) / A.MAX_BYTES_MO ) * 100 , 2)
       )
AS PERCENT_USED,
A.AUTOEXTENSIBLE ,
round ( A.BYTES_MO/A.BLOCKS*1024* A.INCREMENT_BY , 2 ) AS FILE_EXTENT_SIZE_KO,
round ( C.NEXT_EXTENT/1024 , 2 ) AS NEXT_KO
from
 (select FILE_ID,
         sum (BYTES_CACHED) /1024/1024 BYTES_CACHED_MO
  from V$TEMP_EXTENT_POOL
  group by FILE_ID
 ) B,
 (select FILE_ID,
         FILE_NAME,
         TABLESPACE_NAME,
                 INCREMENT_BY,
                 AUTOEXTENSIBLE,
                 BLOCKS,
             round ( MAXBYTES/1024/1024 , 2 ) MAX_BYTES_MO,
             round ( BYTES/1024/1024 , 2) BYTES_MO
  FROM DBA_TEMP_FILES
  ) A,
  DBA_TABLESPACES C
  where A.FILE_ID=B.FILE_ID (+)
    and A.TABLESPACE_NAME = C.TABLESPACE_NAME
)
GROUP BY TABLESPACE_NAME

ORDER BY PERCENT_USED_MAX_SIZE ;



Output :

Tablespace Name: The tablespace name

Space Utilised (MB): Space used out of the space allocated.

Space Free (MB): Space free out of the  space allocated

Space Allocated (MB): This is the initial allocated size specified by the size parameter while adding datafile or the allocated size plus the autoextensible value. If the autoextend segment gets utilized, another segment will be added specified by the autoextend and this becomes the new space allocated.

Space Maximum (MB): Maximum value a tablespace can grow. This is specified in Maxsize parameter while adding datafile.

%Used: Space Utilised/Space Allocated * 100

%Used of Max: Space Utilised/ Space Maximum* 100

Tablespace            space           space           space           space       %  % Used
Name               Utilised            Free       Allocated         Maximum    Used  of Max
--------------------------- --------------- --------------- --------------- ------- -------
CER_DEVE_TEMP          4.00           59.00           63.00           63.00    6.35    6.35
TEMP               2,303.00       30,464.00       32,767.00       32,767.98    7.03    7.03
DEV_PATU           2,383.75          177.25        2,561.00       32,767.98   93.08    7.27
UNDOTBS2           2,761.88        3,585.13        6,347.00       32,767.00   43.51    8.43

Comments