Thanks for the question, Ebrahim.
Asked: February 26, 2024 - 2:16 am UTC
Last updated: February 26, 2024 - 4:45 am UTC
Version: Oracle 19c
Viewed 100+ times
You Asked
Dear Tom,
Oracle 4 node RAC version 19c
In my tablespace I have total of 570 partitioned tables that are zero rows. Their initial extent is 8M for each partition, so collectively the empty tables are occupying 2286.03 GB. As they are not needed, I have started to drop them.
After dropping some 300 tables, I wanted to check the space released.
But this query shows the occupied space is not released. I always thought that if I drop a table with purge, the space would immediately be released back to the tablespace.
What am I doing wrong?
select
round(sum (bytes/1024/1024/1024),2) GB
from dba_segments
Where tablespace_name='TOPREP_DAT' and owner ='SAMSUNGLTE';
GB
---
2286.03
and Connor said...
We'll need to see some more detail there, because a "purge" should definitely free the space.
select table_name from dba_tables Where tablespace_name='TOPREP_DAT' and owner ='SAMSUNGLTE';
select table_name, ts_name from dba_tables Where tablespace_name='TOPREP_DAT' and owner ='SAMSUNGLTE';
select segment_name, partition_name from dba_segments Where tablespace_name='TOPREP_DAT' and owner ='SAMSUNGLTE';
to see whats going on.