SQL> select * from v$version;

BANNER
----------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE??? 10.2.0.1.0????? Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

?

?

---1.创建测试表
CREATE TABLE T1 TABLESPACE TEST AS SELECT * FROM DBA_OBJECTS;

--2 查询segment 头块
SQL> SELECT HEADER_FILE,HEADER_BLOCK FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'T1';

HEADER_FILE HEADER_BLOCK
----------- ------------
???????? 22????????? 795

--3 DUMP segment 头块

ALTER SYSTEM DUMP DATAFILE 22 BLOCK 795;

--4 查看结尾为2492的trace文件 (ORAHOME\product\10.2.0\admin\orcl\udump)

SELECT SPID
? FROM V$PROCESS
?WHERE ADDR = (SELECT PADDR
???????????????? FROM V$SESSION
??????????????? WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT));
SPID
------------
2492

--HWM 为 0x058008fb
Highwater::? 0x058008fb? ?

--5 计算HWM 对应的文件编号和块
SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(TO_NUMBER('058008FB','XXXXXXXX')) AS HWM_FILEID FROM DUAL;

HWM_FILEID
----------
??????? 22

SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(TO_NUMBER('058008FB','XXXXXXXX')) AS HWM_BLOCKID FROM DUAL;

HWM_BLOCKID
-----------
?????? 2299
????? ?
--6. 分析表，查看sgemnt空块的数及最大使用的Block ID
SQL> SELECT FILE_ID,MIN(BLOCK_ID),MAX(BLOCK_ID+BLOCKS) FROM DBA_EXTENTS WHERE SEGMENT_NAME='T1' GROUP BY FILE_ID;

?? FILE_ID MIN(BLOCK_ID) MAX(BLOCK_ID+BLOCKS)
---------- ------------- --------------------
??????? 22?????????? 793???????????????? 2313
?????? ?
ANALYZE TABLE T1 COMPUTE STATISTICS；

SQL> SELECT EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME = 'T1';

EMPTY_BLOCKS
------------
????????? 14
SQL> SELECT 2313 -14 FROM DUAL;--等于HWM_BLOCKID

?? 2313-14
----------
????? 2299
--7.记录下delete数据前count(*)的统计信息
SQL> set autotrace traceonly;
SQL> select count(*) from T1;


执行计划
----------------------
Plan hash value: 3724264953

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


统计信息
----------------------
????????? 0? recursive calls
????????? 0? db block gets
??????? 739? 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

--8 删除数据
SQL> set autotrace off;
SQL> select count(*) from T1;

? COUNT(*)
----------
???? 53176
??? ?
SQL> delete T1 where rownum <= 30000;

已删除30000行。

SQL> COMMIT;

提交完成。

--9 重新dump 数据头,发现Highwater仍然是原来的值。说明DELETE 并不能使HWM下降。
ALTER SYSTEM DUMP DATAFILE 22 BLOCK 795;

Highwater::? 0x058008fb

--10. 分析删除数据