Play with hao_show_space and table reorganization

--get hao_show_space from http://space.itpub.net/15415488/viewspace-609531

SYS@XE> insert into t1 select * from dba_objects;

14582 rows created.

SYS@XE> commit;

Commit complete.

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................64
UNUSED Bytes............................524288
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................1
FS3 Bytes ..............................8192
--------------------------------------------------
FS4 Blocks..............................17
FS4 Bytes ..............................139264
--------------------------------------------------
FULL BLOCKS.............................288
FULL_BYTES .............................2359296
##################################################
Data Blocks(under HWM)..................306
All Blocks (under HWM)..................320
Total Blocks............................384
Total Bytes.............................3145728
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................521
Last Used Block.........................64

PL/SQL procedure successfully completed.

SYS@XE> delete from t1 where owner='SYS';

6648 rows deleted.

SYS@XE> commit;

Commit complete.

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................64
UNUSED Bytes............................524288
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................15
FS2 Bytes ..............................122880
--------------------------------------------------
FS3 Blocks..............................61
FS3 Bytes ..............................499712
--------------------------------------------------
FS4 Blocks..............................62
FS4 Bytes ..............................507904
--------------------------------------------------
FULL BLOCKS.............................168
FULL_BYTES .............................1376256
##################################################
Data Blocks(under HWM)..................306
All Blocks (under HWM)..................320
Total Blocks............................384
Total Bytes.............................3145728
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................521
Last Used Block.........................64

PL/SQL procedure successfully completed.

SYS@XE> select bytes from dba_segments where segment_name='T1' and owner='SYS';

BYTES
----------
3145728

SYS@XE> select count(distinct substr(rowid,1,15)) from t1;

COUNT(DISTINCTSUBSTR(ROWID,1,15))
---------------------------------
268

SYS@XE> alter table t1 move ;

Table altered.

SYS@XE> select count(distinct substr(rowid,1,15)) from t1;

COUNT(DISTINCTSUBSTR(ROWID,1,15))
---------------------------------
208

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................36
UNUSED Bytes............................294912
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................0
FS4 Bytes ..............................0
--------------------------------------------------
FULL BLOCKS.............................208
FULL_BYTES .............................1703936
##################################################
Data Blocks(under HWM)..................208
All Blocks (under HWM)..................220
Total Blocks............................256
Total Bytes.............................2097152
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................777
Last Used Block.........................92

PL/SQL procedure successfully completed.


SYS@XE> set autotrace on
SYS@XE> select count(*) from t1;

COUNT(*)
----------
15418


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 14146 | 62 (2)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
271 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SYS@XE> delete from t1;

15418 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 775918519

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 61 (0)| 00:00:01 |
| 1 | DELETE | T1 | | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 61 (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
45 recursive calls
17171 db block gets
291 consistent gets
0 physical reads
5533796 redo size
939 bytes sent via SQL*Net to client
929 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15418 rows processed

SYS@XE> commit;

Commit complete.

SYS@XE> select count(*) from t1;

COUNT(*)
----------
0


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 61 (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
212 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................36
UNUSED Bytes............................294912
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................208
FS4 Bytes ..............................1703936
--------------------------------------------------
FULL BLOCKS.............................0
FULL_BYTES .............................0
##################################################
Data Blocks(under HWM)..................208
All Blocks (under HWM)..................220
Total Blocks............................256
Total Bytes.............................2097152
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................777
Last Used Block.........................92

PL/SQL procedure successfully completed.

SYS@XE> alter table t1 move ;

Table altered.

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................5
UNUSED Bytes............................40960
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................0
FS4 Bytes ..............................0
--------------------------------------------------
FULL BLOCKS.............................0
FULL_BYTES .............................0
##################################################
Data Blocks(under HWM)..................0
All Blocks (under HWM)..................3
Total Blocks............................8
Total Bytes.............................65536
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................257
Last Used Block.........................3

PL/SQL procedure successfully completed.

SYS@XE> select count(*) from t1;

COUNT(*)
----------
0


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed