DB: 12.1.0.2
OS: Linux, kernal 2.6.32
I am trying to understand how the DBA_EXTENTS and DBA_FREE_SPACE views report on space within tablespaces. I created two tablespaces and a table that uses one of the tablespaces for non-LOB columns and the other tablespace for an LOB column. I load the table with filler data and check the values in DBA_EXTENTS and DBA_FREE_SPACE. Then I delete half of the records and the results from DBA_EXTENTS and DBA_FREE_SPACE barely change. Then I delete the remaining records and still DBA_EXTENTS and DBA_FREE_SPACE report very similar information. Finally I truncate the empty table and DBA_EXTENTS and DBA_FREE_SPACE report what I would expect - very little space used, lots of space free.
Short of truncating the table, is there a way to reclaim unused space from the tablespace?
The exact steps I followed are shown below.
1) created two tablespaces
create bigfile tablespace test1_data
datafile '/.../test1_data.dbf'
size 1G reuse
autoextend on next 1G maxsize 40G
nologging
extent management local uniform size 256K
segment space management auto;
create bigfile tablespace test1_lob
datafile '/.../test1_lob.dbf'
size 5G reuse
autoextend on next 1G maxsize 50G
nologging
extent management local uniform size 256K
segment space management auto;
2) created a table with an LOB column that uses both tablespaces
create table attachments
(at_id integer not null ,
doc_id integer not null ,
at_type varchar2(256) not null ,
at_name varchar2(256) not null ,
at_data blob )
logging tablespace test1_data
lob (at_data) store as ( tablespace test1_lob ) ;
3) loaded the table with filler data
select count(*) from attachments;
1,304,200
4) checked the space used and the free space afterwards.
select tablespace_name,sum(bytes) as bytes, sum(bytes)/1024/1024/1024 as gigs
from dba_extents where tablespace_name like 'TEST%'
group by tablespace_name order by 1;
TEST1_DATA 125,829,120 0
TEST1_LOB 22,059,941,888 21
select tablespace_name,sum(bytes) as bytes, sum(bytes)/1024/1024/1024 as gigs
from dba_free_space where tablespace_name like 'TEST%'
group by tablespace_name order by 1;
TEST1_DATA 876,609,536 1
TEST1_LOB 1,155,530,752 1
5) deleted the even numbered records from the table
select count(*) from attachments;
652,100
6) purged the recyclebin;
purge dba_recyclebin;
DBA Recyclebin purged.
7) checked used and free space again
select tablespace_name,sum(bytes) as bytes, sum(bytes)/1024/1024/1024 as gigs
from dba_extents where tablespace_name like 'TEST%'
group by tablespace_name order by 1;
TEST1_DATA 125,829,120 0
TEST1_LOB 22,123,118,592 21
select tablespace_name,sum(bytes) as bytes, sum(bytes)/1024/1024/1024 as gigs
from dba_free_space where tablespace_name like 'TEST%'
group by tablespace_name order by 1;
TEST1_DATA 876,609,536 1
TEST1_LOB 1,092,354,048 1
8) deleted the remainder of the records from the table
select count(*) from attachments;
0
9) purge the recyclebin again;
purge dba_recyclebin;
DBA Recyclebin purged.
10) checked used and free space again
select tablespace_name,sum(bytes) as bytes, sum(bytes)/1024/1024/1024 as gigs
from dba_extents where tablespace_name like 'TEST%'
group by tablespace_name order by 1;
TEST1_DATA 125,829,120 0
TEST1_LOB 22,185,771,008 21
select tablespace_name,sum(bytes) as bytes, sum(bytes)/1024/1024/1024 as gigs
from dba_free_space where tablespace_name like 'TEST%'
group by tablespace_name order by 1;
TEST1_DATA 876,609,536 1
TEST1_LOB 1,029,701,632 1
11) truncated the table
truncate table attachments;
Table truncated.
12) checked space one more time
select tablespace_name,sum(bytes) as bytes, sum(bytes)/1024/1024/1024 as gigs
from dba_extents where tablespace_name like 'TEST%'
group by tablespace_name order by 1;
TEST1_DATA 262,144 0
TEST1_LOB 524,288 0
select tablespace_name,sum(bytes) as bytes, sum(bytes)/1024/1024/1024 as gigs
from dba_free_space where tablespace_name like 'TEST%'
group by tablespace_name order by 1;
TEST1_DATA 1,002,176,512 1
TEST1_LOB 23,214,948,352 22