OraFAQ Forum: Server Administration » Large Number of Empty Blocks

Home » RDBMS Server » Server Administration » Large Number of Empty Blocks

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Large Number of Empty Blocks [message #157095]

Tue, 31 January 2006 05:30

David King
Messages: 8
Registered: December 2004

Junior Member

Hi All,

I have a table that has a large number of empty blocks, see below.
BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
2508 65204 850 0

My question is: Is this a waste of space? Should these be deallocated back to the tablespace? If so, should I just do a ALTER TABLE with a new storage clause?

Thanks for the help in advance. FYI, running Oracle 9.2.0.1.0

Report message to a moderator

Re: Large Number of Empty Blocks [message #157124 is a reply to message #157095]

Tue, 31 January 2006 08:01

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Please have a look here. This is almost a similiar discussion.
If that does not help you, post again.
http://www.orafaq.com/forum/m/150944/42800/#msg_150807

Report message to a moderator

Re: Large Number of Empty Blocks [message #157609 is a reply to message #157095]

Fri, 03 February 2006 09:29

aciolac
Messages: 242
Registered: February 2006

Senior Member

user ALTER TABLE table_name MOVE to free unused blocks.

Report message to a moderator

Re: Large Number of Empty Blocks [message #157653 is a reply to message #157095]

Fri, 03 February 2006 11:32

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Are you sure an alter table move will have anything to do with empty blocks, unless you are moving it to a tablespace with different storage characteristics? Alter table move by itself won't, and it will cause side effects that you didn't mention.

MYDBA > 
MYDBA > create table test(a number, data char(100));

Table created.

MYDBA > 
MYDBA > analyze table test compute statistics;

Table analyzed.

MYDBA > 
MYDBA > select blocks, empty_blocks
  2  from user_tables where table_name = 'TEST';

    BLOCKS EMPTY_BLOCKS
---------- ------------
         0            8

MYDBA > 
MYDBA > alter table test move;

Table altered.

MYDBA > 
MYDBA > analyze table test compute statistics;

Table analyzed.

MYDBA > 
MYDBA > select blocks, empty_blocks
  2  from user_tables where table_name = 'TEST';

    BLOCKS EMPTY_BLOCKS
---------- ------------
         0            8

MYDBA > 
MYDBA > insert into test select rownum, 'x' from dual connect by level <= 1000;

1000 rows created.

MYDBA > commit;

Commit complete.

MYDBA > 
MYDBA > analyze table test compute statistics;

Table analyzed.

MYDBA > 
MYDBA > select blocks, empty_blocks
  2  from user_tables where table_name = 'TEST';

    BLOCKS EMPTY_BLOCKS
---------- ------------
        20            4

MYDBA > 
MYDBA > alter table test move;

Table altered.

MYDBA > 
MYDBA > analyze table test compute statistics;

Table analyzed.

MYDBA > 
MYDBA > select blocks, empty_blocks
  2  from user_tables where table_name = 'TEST';

    BLOCKS EMPTY_BLOCKS
---------- ------------
        20            4

MYDBA > 
MYDBA > drop table test;

Table dropped.

MYDBA > set echo off;

OP: Check out the link that Mahesh pointed you to. Empty block isn't necessarily wasted space if your table data will ever be growing. Also read concepts guide early chapters on blocks and tables and tablespaces and extents.

Report message to a moderator

Re: Large Number of Empty Blocks [message #157656 is a reply to message #157653]

Fri, 03 February 2006 11:39

aciolac
Messages: 242
Registered: February 2006

Senior Member

1) ALTER TABLE MOVE frees emty blocks. If you want to verify this create one table and insert for example 1 GB of dates. After that delete ~ hals of the rows, and make alter table move. You will see that size of table will decrease(but possible that not - depent from INITIAL_EXTENT and NEXT_EXTENT of table).
2)I your example possible thay you are created table whith the parameter INITAL_EXTENT=8 blocks. Yuor table are empty, but size of table cannot be smaller than INITIAL_EXTENT.

Report message to a moderator

Re: Large Number of Empty Blocks [message #157658 is a reply to message #157095]

Fri, 03 February 2006 11:53

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Yes, duh, move can free empty blocks. Have I forgotten all of my basics lately (in reference to another thread). Don't even need the delete to show it.

MYDBA > 
MYDBA > create table test(a number, data char(100));

Table created.

MYDBA > 
MYDBA > analyze table test compute statistics;

Table analyzed.

MYDBA > 
MYDBA > select blocks, empty_blocks
  2  from user_tables where table_name = 'TEST';

    BLOCKS EMPTY_BLOCKS
---------- ------------
         0            8

MYDBA > 
MYDBA > alter table test move;

Table altered.

MYDBA > 
MYDBA > analyze table test compute statistics;

Table analyzed.

MYDBA > 
MYDBA > select blocks, empty_blocks
  2  from user_tables where table_name = 'TEST';

    BLOCKS EMPTY_BLOCKS
---------- ------------
         0            8

MYDBA > 
MYDBA > insert into test select rownum, 'x' from dual connect by level <= 10000;

10000 rows created.

MYDBA > commit;

Commit complete.

MYDBA > 
MYDBA > analyze table test compute statistics;

Table analyzed.

MYDBA > 
MYDBA > select blocks, empty_blocks
  2  from user_tables where table_name = 'TEST';

    BLOCKS EMPTY_BLOCKS
---------- ------------
       180           76

MYDBA > 
MYDBA > alter table test move;

Table altered.

MYDBA > 
MYDBA > analyze table test compute statistics;

Table analyzed.

MYDBA > 
MYDBA > select blocks, empty_blocks
  2  from user_tables where table_name = 'TEST';

    BLOCKS EMPTY_BLOCKS
---------- ------------
       166           90

MYDBA > 
MYDBA > delete from test where mod(a,2) = 0;

5000 rows deleted.

MYDBA > commit;

Commit complete.

MYDBA > 
MYDBA > analyze table test compute statistics;

Table analyzed.

MYDBA > 
MYDBA > select blocks, empty_blocks
  2  from user_tables where table_name = 'TEST';

    BLOCKS EMPTY_BLOCKS
---------- ------------
       166           90

MYDBA > 
MYDBA > alter table test move;

Table altered.

MYDBA > 
MYDBA > analyze table test compute statistics;

Table analyzed.

MYDBA > 
MYDBA > select blocks, empty_blocks
  2  from user_tables where table_name = 'TEST';

    BLOCKS EMPTY_BLOCKS
---------- ------------
        85            3

MYDBA > 
MYDBA > drop table test;

Table dropped.

MYDBA > set echo off;

Report message to a moderator

Previous Topic:	Permissions Issues?
Next Topic:	Ora-24324 & Other Errors In Sql*plus

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 14:17:02 CDT 2024