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)
Why is DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_SIZE greater than Available Space In OS [message #649558] Thu, 31 March 2016 00:31 Go to next message
juniordbanewbie
Messages: 176
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
Re: Why is DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_SIZE greater than Available Space In OS [message #649559 is a reply to message #649558] Thu, 31 March 2016 00:43 Go to previous message
Michel Cadot
Messages: 65319
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe because MOS note is wrong.

[Updated on: Thu, 31 March 2016 01:01]

Report message to a moderator

Previous Topic: installing oracl 11g with rac option in windows server 2008 desktop
Next Topic: error 12560 database oracle 8i isntallation
Goto Forum:
  


Current Time: Tue Jan 23 11:11:50 CST 2018

Total time taken to generate the page: 0.04007 seconds