What is the difference between Allocated Size Used % and Total Size Used % in Oracle tablespaces?
The "Tablespaces" dashboard has 2 columns (see screenshot) which can tell the customer the current space usage:
· Allocated Size Used % - This tells how much storage is used out of the entire allocated data files for each tablespaces. Sum of bytes all the data files of the tablespace (without taking into account the disk size/maxsize/autoextensible).
Total Allocated Size should data file allocated size (not tablespace used and free).
· Total Size Used % - This tells how much storage is used out of the entire allocated data files for each tablespaces and also takes into account auto extend data files, the MAXBYTES property of each data file and the available space on the relevant mount points
When a datafile is not an auto-extensible we are not taking the file system free size into account, so the right formula will be
Total Size Used % = (Used_Space / Total_Allocated_Size) * 100
The 'allocated size used %' should always be equal or higher than the 'Total size used %' as the 'Total size used %' may take into account the free space in the file system/ASM.
Examples from the screenshot above:
· The "USERS" tablespace (in the top of the table) has 2 data files and one of them is set to be auto extend, therefore the "Total Size Used %" is lower (58.07%) than the "Allocated Size Used %" (93.80) because we take into account the MAXBYTES and the available space on the disk or ASM disk group of this auto extend data files.
· The "TBS_PERM_02" tablespace (in the bottom of the table) has 1 auto extend data file. Both the "Total Size Used %" and the "Allocated Size Used %" value is 10% (its allocated size is 10MB and its current size is 1MB). For this data file the MAXSIZE is 10MB (see screenshot below) so although it's configured to be auto extend, it can't be larger than 10MB so we take that into account: