Oracle dbf problem

Hi Richard,

I'm making that squealing noise again - the one you love :o)

What point have I missed totally then ?

>> Yes, deallocate unused gets rid of unused blocks above the HWM.
That's the
>> issue that Pauline is facing, having created a table way way too big
for the
>> expected size of data and now wanting to free the space back to the
>> tablespace.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

--
-- Default tablespace is 64KB uniform sized LMT, just in case !
--
SQL> create table test (a varchar2(100)) storage (initial 500k next
500k);
Table created.

SQL> insert into test (a) values ('Hello Richard!');
1 row created.

SQL> commit;
Commit complete.

SQL> analyze table test compute statistics;
Table analyzed.


SQL> set lines 350
SQL> SELECT
TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN
  2  FROM user_tables
  3* WHERE table_name = 'TEST'

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
AVG_SPACE AVG_ROW_LEN
------------------------------ ---------- ---------- ------------
---------- -----------
TEST                                    1          1           62
8082          18

SQL> alter table test deallocate unused;
Table altered.

SQL> analyze table test compute statistics;
Table analyzed.

SQL> SELECT
TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN
  2  FROM user_tables 
  3  WHERE table_name = 'TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
AVG_SPACE AVG_ROW_LEN
------------------------------ ---------- ---------- ------------
---------- -----------
TEST                                    1          1           62
8082          18

SQL> drop table test;
Table dropped.


So I created the table way too big, inserted some data and deallocated -
no change in the blocks used or free - so I'm puzzled. I have dome this
so many times before and never been able to reduce the space used (by
much) even on a schema with 2000 odd tables and about 6000 indexes. You
can go off HWMs you know.


Regards,
Norman.