开启spool功能
SQL> spool E:\liuhaichen\text.txt
Started spooling to E:\liuhaichen\text.txt
创建一张临时表
SQL> CREATE TABLE TMP_LIUHC_1 (M INT);
Table created
向表TMP_LIUHC_1插入数据
SQL>
SQL> BEGIN
2 FOR I IN 1..10000 LOOP
3 INSERT INTO TMP_LIUHC_1 VALUES (I);
4 END LOOP;
5 END;
6 /
PL/SQL procedure successfully completed
数据插入之后查看该表的统计信息
SQL> SELECT OWNER, TABLESPACE_NAME, NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TMP_LIUHC_1';
OWNER TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ---------- ------------
STAT STATDATA
此时的表的统计信息还没有被收集,所以查看不到行数和所占有的块数
查看创建临时表所用表空间的管理模式
SQL> SELECT TABLESPACE_NAME,STATUS,SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='STATDATA';
TABLESPACE_NAME STATUS SEGMENT_SPACE_MANAGEMENT
------------------------------ --------- ------------------------
STATDATA ONLINE AUTO
收集表'TMP_LIUHC_1'的统计信息
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('STAT','TMP_LIUHC_1')
/
PL/SQL procedure successfully completed
通过信息收集后可以查看创建临时表的占用情况
SQL> SELECT OWNER, TABLESPACE_NAME, NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TMP_LIUHC_1';
OWNER TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ---------- ------------
STAT STATDATA 10000 20 0
删除临时表中的部分数据
SQL> DELETE FROM TMP_LIUHC_1 WHERE M<1000;
999 rows deleted
SQL> COMMIT;
Commit complete
再次查看临时表的块占用情况
SQL> SELECT OWNER, TABLESPACE_NAME, NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TMP_LIUHC_1';
OWNER TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ---------- ------------
STAT STATDATA 10000 20 0
由于没有重新收集统计信息,所以显示的信息不会改变
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('STAT','TMP_LIUHC_1');
PL/SQL procedure successfully completed
SQL> SELECT OWNER, TABLESPACE_NAME, NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TMP_LIUHC_1';
OWNER TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ---------- ------------
STAT STATDATA 9001 20 0
重新收集后,发现表的行数变少了,但是所占用的块数没有发生改变, 说明该表处于高水位
降低高水位表
SQL> ALTER TABLE TMP_LIUHC_1 ENABLE ROW MOVEMENT;
Table altered
SQL> ALTER TABLE TMP_LIUHC_1 SHRINK SPACE;
Table altered
SQL> SELECT OWNER, TABLESPACE_NAME, NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TMP_LIUHC_1';
OWNER TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ---------- ------------
STAT STATDATA 9001 20 0
重新收集状态信息
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('STAT','TMP_LIUHC_1');
PL/SQL procedure successfully completed
SQL> SELECT OWNER, TABLESPACE_NAME, NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TMP_LIUHC_1';
OWNER TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ---------- ------------
STAT STATDATA 9001 14 0
最后发现占用的块变少了,回归到低水位。
以上是在删除表是使用delete,而实际表占用的块数没有回归到低水位,当再次插入数据时,这些快会被重复利用,且不被其他表利用(自己测试insert)
最后再使用truncate删除表
SQL> truncate table TMP_LIUHC_1;
Table truncated
SQL> SELECT OWNER, TABLESPACE_NAME, NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TMP_LIUHC_1';
OWNER TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ---------- ------------
STAT STATDATA 9001 14 0
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('STAT','TMP_LIUHC_1');
PL/SQL procedure successfully completed
SQL> SELECT OWNER, TABLESPACE_NAME, NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TMP_LIUHC_1';
OWNER TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ---------- ------------
STAT STATDATA 0 0 0
使用truncate删除表后,会自动回归到低水位。
SQL> SPOOL OFF
Stopped spooling to E:\liuhaichen\text.txt
结论,在删除数据时,最好是使用truncate,这样表会自动回到低水位;在工作中,大多数时候都是使用delete,因为如果删除数据发生意外,能够进行恢复。但是这样会导致一个问题,随着数据的增长,而不能一直回到低水位,当使用表不能用到索引,而走全表扫描时,即使表里面的数据只有几百上千行,也会花很长的时间,尤其是在删除几百万行的数据的时候。
PS: 10g之前降低高水位得使用 alter tablename move (并且必须重建索引和保证足够的临时表空间)