| 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
|
|
|
|
|
|
|
|
| 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.
|
|
|
|
| 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.
|
|
|
|
| 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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
