Hi Tom
I found a stange thing from my company's database
I wrote a script as like this to reporduce the operation on my vm
SQL> !cat a.sql
select a.tablespace_id
, c.tablespace_name
, a.tablespace_size * c.block_size /1024/1024 as tbspc_size
, d.datafile_size
from
dba_hist_tbspc_space_usage a
join
v$tablespace b on a.tablespace_id = b.ts#
join
dba_tablespaces c on b.name = c.tablespace_name
join
(select tablespace_name, sum(bytes)/1024/1024 as datafile_size
from dba_data_files
group by tablespace_name
) d on c.tablespace_name = d.tablespace_name
where
a.snap_id = (select max(snap_id) from dba_hist_snapshot)
and
a.tablespace_id = 6
order by 1
/
LAB like this
SQL> @a
TABLESPACE_ID TABLESPACE_NAME TBSPC_SIZE DATAFILE_SIZE
------------- ------------------------------ ---------- ----------
6 TBS1 10 10
SQL> alter tablespace tbs1 add datafile '/oracle/app/oradata/orcl11/tbs1b.dbf' size 10m;
Tablespace altered.
SQL> alter tablespace tbs1 drop datafile '/oracle/app/oradata/orcl11/tbs1b.dbf' ;
Tablespace altered.
SQL> alter database datafile '/oracle/app/oradata/orcl11/tbs1.dbf' resize 12m;
Database altered.
SQL> @a
TABLESPACE_ID TABLESPACE_NAME TBSPC_SIZE DATAFILE_SIZE
------------- ------------------------------ ---------- ----------
6 TBS1 10 12
SQL> EXEC dbms_workload_repository.create_snapshot;
SQL> @a
TABLESPACE_ID TABLESPACE_NAME TBSPC_SIZE DATAFILE_SIZE
------------- ------------------------------ ---------- ----------
6 TBS1 22 12
IS it right !?
Yeah that looks like a bug - I replicated that on 19c
SQL> create tablespace XXX datafile 'X:\ORACLE\ORADATA\DB19\XXX.DBF' size 10m;
Tablespace created.
SQL> EXEC dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL> select ts#
2 from v$tablespace
3 where name = 'XXX';
TS#
----------
6
SQL> select * from dba_hist_tbspc_space_usage
2 where tablespace_id = 6
3 and con_id = 1
4 @pr
==============================
SNAP_ID : 14124
DBID : 741782391
TABLESPACE_ID : 6
TABLESPACE_SIZE : 1280
TABLESPACE_MAXSIZE : 1280
TABLESPACE_USEDSIZE : 128
RTIME : 02/01/2021 09:22:54
CON_DBID : 741782391
CON_ID : 1
SQL> alter tablespace XXX add datafile 'X:\ORACLE\ORADATA\DB19\XXX2.DBF' size 10m;
Tablespace altered.
SQL> alter tablespace XXX drop datafile 'X:\ORACLE\ORADATA\DB19\XXX2.DBF';
Tablespace altered.
SQL> EXEC dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL> select * from dba_hist_tbspc_space_usage
2 where tablespace_id = 6
3 and con_id = 1
4 @pr
==============================
SNAP_ID : 14124
DBID : 741782391
TABLESPACE_ID : 6
TABLESPACE_SIZE : 1280
TABLESPACE_MAXSIZE : 1280
TABLESPACE_USEDSIZE : 128
RTIME : 02/01/2021 09:22:54
CON_DBID : 741782391
CON_ID : 1
==============================
SNAP_ID : 14125
DBID : 741782391
TABLESPACE_ID : 6
TABLESPACE_SIZE : 2560
TABLESPACE_MAXSIZE : 2560
TABLESPACE_USEDSIZE : 256
RTIME : 02/01/2021 09:25:07
CON_DBID : 741782391
CON_ID : 1
==============================
Deep down the tablespace information comes from X$KTTEFINFO - I suspect its not being cleaned up for dropped datafiles