Can any expert explains the reason for the difference that I can see in the tablespace size from these two dynamic views? What is the correct space allocated to the tablespace and what is the current usage in that case and how much space is available here, currently.
From DBA_HIST_TBSPC_SPACE_USAGE
SELECT NAME, TS_SIZE_TOT_GB, TS_USED_GB, TS_SIZE_TOT_GB - TS_USED_GB FREE_SPACE, RTIME, SYSDATE FROM (
SELECT ROUND ((DHTSU.tablespace_size * 32768) / 1024 / 1024/1024) ts_size_gb,
ROUND ((DHTSU.tablespace_maxsize * 32768) / 1024 / 1024/1024) ts_size_tot_gb,
ROUND ( (DHTSU.tablespace_usedsize * 32768) / 1024 / 1024/1024) ts_used_gb,
DHTSU.RTIME,
VT.NAME
FROM DBA_HIST_TBSPC_SPACE_USAGE DHTSU, V$TABLESPACE VT
WHERE DHTSU.TABLESPACE_ID= VT.TS#
AND DHTSU.RTIME = (SELECT MAX(DHTSU1.RTIME) FROM DBA_HIST_TBSPC_SPACE_USAGE DHTSU1 WHERE DHTSU.TABLESPACE_ID=DHTSU1.TABLESPACE_ID)
--ORDER BY rtime DESC
)
WHERE NAME ='MY_TABLESPACE'
The output is
| Name | TS_SIZE_TOT_GB | TS_USED_GB | FREE_SPACE | RTIME | SYSDATE |
|---|
| MY_TABLESPACE | 13056 | 8333 | 4723 | 03/15/2020 18:01:03 | 15/03/2020 18:52:22 |
From dba_data_files
select round(sum(blocks*32768)/1024/1024/1024,0) size_gb,round(sum(user_blocks*32768)/1024/1024/1024,0) size_gb_used, tablespace_name from
dba_data_Files
where tablespace_name ='MY_TABLESPACE'
group by tablespace_name
The output is
| Size_GB | Size_GB_USED
| Tablespace_Name
|
|---|
| 11151 | 11151 | MY_TABLESPACE |
Block size of the tablespace = 32768; Initial Extent =1048576; Next Extent= 1048576; Max Extent= 2147483645; Local/Uniform.
I was expecting DBA_DATA_FILES.SIZE_GB = DBA_HIST_TBSPC_SPACE_USAGE.TS_SIZE_TOT_GB i.e current allocated space size of the tablespace should be equal to sum of bytes of all underlying data files size. Not sure, why is the difference. Which is the correct value of the tablespace and what is the current usage in that case and how much space is available as of now.
There are 314 data files of this tablespace - 18 are autoextensible and 296 are not.