I believe this is better explained with an example.
Sql code:
lets first check if your database table spaces that re in manual and auto segment space management.
SELECT tablespace_name, extent_management, segment_space_management
FROM dba_tablespaces;
TABLESPACE_NAME EXTENT_MANAGEMENT SEGMENT_SPACE_MANAGEMENT
------------------------------ ----------------- ------------------------
SYSTEM LOCAL MANUAL
SYSAUX LOCAL AUTO
UNDOTBS1 LOCAL MANUAL
TEMP LOCAL MANUAL
USERS LOCAL AUTO
SQL> create table test ( x number )
2 tablespace users
3 storage ( initial 10M next 10M )
4 /
Table created.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select blocks, extents from user_segments where segment_name = 'TEST';
BLOCKS EXTENTS
---------- ----------
1280 10
SQL> select blocks, empty_blocks from user_tables where table_name = 'TEST';
BLOCKS EMPTY_BLOCKS
---------- ------------
0 1280
So, I started creating a table named TEST and requested initially 10mb allocated, which turned out to be 1280 blocks and 10 extents. From there you can see:
- table TEST has 1280 blocks allocated (blocks in user_segment)
- none of which are *formatted* to receive data (blocks in user_tables)
Then, I insert some data
sql code:
SQL> insert into TEST
select rownum
from dual
connect by level <= 100000;
100000 rows created.
SQL> analyze table TEST compute statistics;
Table analyzed.
SQL> select blocks, extents from user_segments where segment_name = 'TEST';
BLOCKS EXTENTS
---------- ----------
1280 10
SQL> select blocks, empty_blocks from user_tables where table_name = 'TEST';
BLOCKS EMPTY_BLOCKS
---------- ------------
186 1094
I inserted 100,000 rows, from there you can see:
- allocated blocks/extents for the table did not change
- however, blocks formated to receive data were raised by 186 and the remaining blocks are empty
186 blocks are the HWM now, because those are the blocks that sometime were formatted to receive data. Blocks above 186 are allocated blocks which have never been formatted to receive data.
I will delete some data now to show you it will not raise empty_blocks nor it will lower the blocks that are formatted to receive data (that is, the HWM).
sql code:
SQL> delete from TEST where rownum <= 90000;
90000 rows deleted.
SQL> analyze table TEST compute statistics;
Table analyzed.
SQL> select blocks, empty_blocks from user_tables where table_name = 'TEST';
BLOCKS EMPTY_BLOCKS
---------- ------------
186 1094
See, the delete did nothing to change the HWM, but.. that is because HWM is never reset back when you delete the rows (in oracle)
sql code:
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from TEST;
USED_BLOCKS
-----------
16
tells me only 16 of those 186 contains data. The rest blocks belong to the segment's freelist to be used for inserts/updates.
Now, I will *move* the table to show you how it will re-adjust the HWM.
sql code:
SQL> alter table TEST move tablespace users;
Table altered.
SQL> analyze table TEST compute statistics;
Table analyzed.
SQL> select blocks, empty_blocks from user_tables where table_name = 'TEST';
BLOCKS EMPTY_BLOCKS
---------- ------------
20 1260
See, it shrinked down the HWM to just 20 from 186 and raised the empty_blocks, but..
sql code:
SQL> select blocks, extents from user_segments where segment_name = 'TEST';
BLOCKS EXTENTS
---------- ----------
1280 10
tells you it did nothing to *shrink* the allocated space asigned to the segment, meaning that at this stage the segment will still be using,
at the operating system level space, the same kind of storage. Now, to *reclaim* that space we will use shrink.
sql code:
SQL> alter table TEST enable row movement;
Table altered.
sql > alter table TEST shrink space compact;
Table altered.
SQL> alter table TEST shrink space;
Table altered.
SQL> analyze table TEST compute statistics;
Table analyzed.
SQL> select blocks, extents from user_segments where segment_name = 'TEST';
BLOCKS EXTENTS
---------- ----------
128 1
SQL> select blocks, empty_blocks from user_tables where table_name = 'TEST';
BLOCKS EMPTY_BLOCKS
---------- ------------
20 108
