OraFAQ Forum: Server Administration » Why is DBA_TABLESPACE_USAGE_METRICS.TABLESPACE

Home » RDBMS Server » Server Administration » Why is DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_SIZE greater than Available Space In OS (11.2.0.4 SE, Redhat 7.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Why is DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_SIZE greater than Available Space In OS [message #649558]

Thu, 31 March 2016 00:31

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear all,

first I find out the location of all datafiles that belongs to OLAF tablespace

SELECT file_name, 
       autoextensible 
FROM   dba_data_files 
WHERE  tablespace_name = 'OLAF' 
ORDER  BY file_name;

output

FILE_NAME                                   AUT
------------------------------------------- ---
/u01/app/oracle/oradata/nowatzki/olaf01.dbf YES
/u01/app/oracle/oradata/nowatzki/olaf05.dbf YES
/u01/app/oracle/oradata/nowatzki/olaf09.dbf YES
/u02/app/oracle/oradata/nowatzki/olaf02.dbf YES
/u02/app/oracle/oradata/nowatzki/olaf06.dbf YES
/u02/app/oracle/oradata/nowatzki/olaf10.dbf YES
/u03/app/oracle/oradata/nowatzki/olaf03.dbf YES
/u03/app/oracle/oradata/nowatzki/olaf07.dbf YES
/u03/app/oracle/oradata/nowatzki/olaf11.dbf YES
/u04/app/oracle/oradata/nowatzki/olaf04.dbf YES
/u04/app/oracle/oradata/nowatzki/olaf08.dbf YES

FILE_NAME                                   AUT
------------------------------------------- ---
/u05/app/oracle/oradata/nowatzki/olaf12.dbf YES
/u05/app/oracle/oradata/nowatzki/olaf13.dbf YES

=>datafiles resides in /u01, /u02, /u03, /u04, u05


[oracle@berlin-db ~]$ df -k
Filesystem                         1K-blocks      Used  Available Use% Mounted on
/dev/mapper/rhel_berlin--db-root   22496532  14026904    8469628  63% /
devtmpfs                            16381680         0   16381680   0% /dev
tmpfs                               16391288   6562608    9828680  41% /dev/shm
tmpfs                               16391288     74316   16316972   1% /run
tmpfs                               16391288         0   16391288   0% /sys/fs/cgroup
/dev/sdg                           371433376 127667496  224891512  37% /u05
/dev/sdb                           371433376 317049892   35509116  90% /u04
/dev/sdf                           371433376 324574564   27984444  93% /u03
/dev/sde                           371433376 318629728   33929280  91% /u02
/dev/sdd                           371433376 319297736   33261272  91% /u01
/dev/sdh                           877173548 505695160  326913908  61% /import2
/dev/sdc                          1056763060   4276660  998799312   1% /import
/dev/sda1                             508588    121884     386704  24% /boot
/dev/sdi1                         4291834880 630831532 3661003348  15% /u06

for OLAF tablespace the total available free space is (33261272 + 33929280 + 27984444 + 35509116 + 224891512 ) =>355575624 K

block size is

SELECT value 
FROM   v$parameter 
WHERE  name = 'db_block_size';

output

VALUE
---------------------------------------------------------------------

8192

However from

SELECT TABLESPACE_NAME TBSP_NAME
, USED_SPACE
, TABLESPACE_SIZE TBSP_SIZE
, USED_PERCENT
FROM SYS.DBA_TABLESPACE_USAGE_METRICS WHERE tablespace_name='OLAF';

output

SYS@nowatzki>SELECT TABLESPACE_NAME TBSP_NAME
  2  , USED_SPACE
  3  , TABLESPACE_SIZE TBSP_SIZE
  4  , USED_PERCENT
  5  FROM SYS.DBA_TABLESPACE_USAGE_METRICS WHERE tablespace_name='OLAF';
  

TBSP_NAME                      USED_SPACE  TBSP_SIZE USED_PERCENT
OLAF                            40307541   54525937     73.92361

=>54525937*8192/1024
=>436207496 >> 355575624 K available space at OS
Problem is that tablespace_size is greater than the total available space at the OS level,

so Why is DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_SIZE greater than Available Space In OS

from EM 10G and 11G - Database Tablespace Full Metric Alerts not Generated In Grid Control (Doc ID 357049.1)

Quote:

Important Note:

1. Tablespace alerts are not generated by what is seen in :
Database Instance Home Page --> Administration --> Tablespaces

2. They are generated based on what you see in:
Database Instance Home Page --> All Metrics --> Tablespace Full --> Tablespace Space Used

Case 1 does not take into account maxsize, autoextend, or available disk space whereas Case 2 does. This is explained in detail below.

If DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_SIZE takes care of OS available space as what is mentioned in the MOS document, then why is the value greater than the actual OS available free space?

many thanks in advance

Report message to a moderator