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
Post a Comment