Oracle Database Discussions

Hi
As far as I know, coalesce would happen once we drop table.

Why automatic coalesce doesnt happen in this scenerio?


create tablespace tun_tbs datafile '/oracle/tun.dbf' size 5m;

create table tun (a number) tablespace tun_tbs;

alter table tun allocate extent;
alter table tun allocate extent;
alter table tun allocate extent;
alter table tun allocate extent;
..
..


drop table tun;

SQL> select  block_id,blocks,BYTES from dba_free_space where TABLESPACE_NAME='TUN_TBS';

  BLOCK_ID     BLOCKS      BYTES
---------- ---------- ----------
        73        568    4653056
         9          8      65536
        17          8      65536
        25          8      65536
        33          8      65536
        41          8      65536
        49          8      65536
        57          8      65536
        65          8      65536

I was expecting something like this:

BLOCK_ID     BLOCKS      BYTES
---------- ---------- ----------
        73        568    4653056
         9         64    524288