Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: How does Oracle set the High Water Mark?
Oracle 9.2.0.4 Solaris 8 How does Oracle set the HWM? In the example below, I create a table and insert one row. I would expect only one block to be used, instead Oracle seems to have pushed the HWM up to 13 blocks. I can imagine that this might be done to "pre-mark" rows as used during bulk inserts, but how does Oracle decide where to set the HWM? SQL> select * from dba_tablespaces where tablespace_name = 'EXAMPLE'; TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS ------------------------------ ---------- -------------- ----------- ----------- MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ----------- ------------ ---------- --------- --------- --------- --- ---------- ALLOCATIO PLU SEGMEN DEF_TAB_ --------- --- ------ -------- EXAMPLE 4096 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED SQL> create table t (col1 number(1)); Table created. SQL> analyze table t compute statistics; Table analyzed. SQL> insert into t values(1); 1 row created. SQL> commit; Commit complete. SQL> select blocks, empty_blocks from user_tables where table_name = 'T'; BLOCKS EMPTY_BLOCKS ---------- ------------ 0 16 SQL> analyze table t compute statistics; Table analyzed. SQL> select blocks, empty_blocks from user_tables where table_name = 'T'; BLOCKS EMPTY_BLOCKS ---------- ------------ 13 3 ___________________________________________________________________________