Dears all,
Ours Is a windows environment, we have few table spaces and there are data files associated with them, with auto extend ON(so max it can grow 32 GB).
We would like to get a query to know if there is a table space which has a data file which is growing say beyond 25 GB and there is no other Data file < 25 GB associated with that table space so that if this grows 32 GB then the table space will become full.
I searched but I am getting queries where tablespace or data file is full, but unable to get for my requirement, can someone please help
SELECT df.tablespace_name "TABLESPACE NAME",
df.file_name "FILE NAME",
Round(df.bytes/1024/1024,0) "SIZE MB"
FROM DBA_DATA_FILES DF
where Round(df.bytes/1024/1024/1024,0) >=25
and not exists
(SELECT 1 from DBA_DATA_FILES Df1 where Round(df.bytes/1024/1024/1024,0) < 25 and DF1.file_name!=DF.file_name )
order by 1