Re: Table fragmented

On Dec 4, 3:03 pm, "astalavista" <nob..._at_nowhere.com> wrote:
> Table size (with fragmentation)
>
> SQL> select table_name,round((blocks*8),2)||'kb' "size"
> 2 from user_tables
> 3 where table_name = 'BIG1';
>
> TABLE_NAME size
> ------------------------------ ------------------------------------------
> BIG1 72952kb
>
> Actual data in table:
>
> SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
> 2 from user_tables
> 3 where table_name = 'BIG1';
>
> TABLE_NAME                     size
> ------------------------------ ------------------------------------------
> BIG1                           30604.2kb
>
> Note = 72952 - 30604 = 42348 Kb is wasted space in table
>
> The difference between two values is 60% and Pctfree 10% (default) - so, the
> table has 50% extra space which is wasted because there is no data.

SQL> create table space_waster(
  2 stuff varchar2(4000)
  3 )
  4 storage(initial 4M next 4M);

Table created.

SQL>
SQL> insert into space_waster
  2 values('This is a terrible waste of space, but, dammit, it''s MY waste of space!!! It''s MINE, MINE, MINE, MINE, MINE, MINE, MINE, ALL MINE!!!!!

Hahahahahahahahahahahahahahahahahahahahahahahaha!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'SPACE_WASTER', cascade=>true);

PL/SQL procedure successfully completed.

SQL>
SQL> select (pct_free/100)*8000*blocks/1024||'kb' "room for updates"   2 from user_tables
  3 where table_name = 'SPACE_WASTER';

room for updates

SQL>
SQL> select table_name,round((blocks*8192/1024),2)||'kb' "current size"
  2 from user_tables
  3 where table_name = 'SPACE_WASTER';

TABLE_NAME                     current size
------------------------------
------------------------------------------
SPACE_WASTER                   480kb

SQL>
SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "occupied space"
  2 from user_tables
  3 where table_name = 'SPACE_WASTER';

TABLE_NAME                     occupied space
------------------------------
------------------------------------------
SPACE_WASTER                   .28kb

SQL> Gee, is it wasted space because I haven't populated it yet? Or, don't I understand the 'problem'?

David Fitzjarrell Received on Fri Dec 05 2008 - 09:06:52 CST