When you miss the obvious.....

Ever had one of those days where you start looking at something, miss the obvious then get deeper and deeper into looking for the 'obscure' bug that is causing something to happen.

That happened today - see if you can spot the issue quicker than i did.....I'm blaming a lack of coffee.

So one of our guys asked me - i truncated a table - why am i not getting any space back?

At first i thought it must be a mistake in the query they were running - but a quick glance at it seemed to check out OK.

So i went away to have a closer look - here is what i found


SQL> select count(*) from dba_extents where segment_name='PRCFWD' and segment_type='TABLE'
  2  /
        62

SQL> select sum(bytes) from dba_extents where segment_name='PRCFWD' and segment_type='TABLE'
  2  /
 160432128

So it's a table made up of 62 extents - this takes up ~160MB on disk

let's truncate it

SQL> truncate table zainet.PRCFWD;

Table truncated.

Now lets try the queries again

SQL> select count(*) from dba_extents where segment_name='PRCFWD' and segment_ty
  2  /
        62

SQL> select sum(bytes) from dba_extents where segment_name='PRCFWD' and segment_
  2  /
 160432128

right.......

so a truncate (and it did work - there were no rows afterwards) has freed up no space - the HWM has reset though as a select count(*) with a full hint returns instantly - so what is going on here.....?

Now this is our ancient DB  -as confirmed by this select statement

SQL> select * from v$version ;
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

So knowing that it's already making me think bug before I've really looked in too much depth.

Lets check out the tablespace info

SQL> select * from dba_tablespaces  where tablespace_name='DATA01';

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- --- ----------
ALLOCATIO PLU SEGMEN DEF_TAB_
--------- --- ------ --------
DATA01                               8192          65536                       1
 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO  LOCAL
SYSTEM    NO  MANUAL DISABLED

Now i start thinking - hmm didn't this ASSM and local extent management come in at 9i - and I know from past experience 9.2.0.4 has other bugs too

So i'm still thinking - must be a bug and start trawling through MoS for a possible cause - but nothing seems to jump out.

Then i go away and do something else for a while - then i have an idea

minextents! - that'll be it - it must be set to some big number - lets check that

SQL> select min_extents from dba_tables where table_name='PRCFWD'

  2  /

          1

Bang goes that theory

So i try MoS (and google) again - in vain

Then in the car on the way home it hits me (maybe you got it quicker than me)

Initial extent size - this database was created via exp/imp and the compress=y option must have been set - this meant that we set the initial extent size to the total size of the table!

I confirmed it with this simple statement

SQL> select initial_extent from dba_tables where table_name='PRCFWD'

  2  /

     157286400

Now because we use ASSM it doesn't allocate 1 big extent of this size - it creates it made up of multiple small chunks - which is how we get in the situation we are in - all makes sense now when you realize that.

Now to fix it...

SQL> alter table zainet.prcfwd storage (initial 1M);

alter table zainet.prcfwd storage (initial 1M)

                                   *

ERROR at line 1:

ORA-02203: INITIAL storage options not allowed

So that failed - so this isn't fixable - at least not without creating a new segment

So lets do that

SQL> alter table zainet.prcfwd move storage (initial 1M);

Table altered.

Now lets check the extents

  1* select BLOCKS from dba_extents where segment_name='PRCFWD' and segment_type='TABLE'

SQL> /

         8

         8

         8

         8

         8

         8

         8

         8

         8

         8

         8

         8

         8

         8

         8

         8

There are still quite a few (Again ASSM) - but the total is the 1MB we wanted and we freed up all the space

So it's Occam's razor again - don;t look for a complex solution as the likely solution is the simplest one.......