What is Oracle high watermark

Now explain what "high water level" is:

Logical storage structure of Oracle: Tablespace--> segment--> Area--> block

Block: is the smallest storage unit, now the standard block size is 8k,oracle every I/O operation is also done by block, that is, when Oracle reads data from the data file, it reads how many blocks, not how many rows.

Area: Consisting of a series of adjacent blocks, this is also the basic unit of Oracle space allocation, for example, when we create a table A, first Oracle assigns a space to the table, and with the constant insert data to a, the original area cannot contain the inserted data, Oracle expands in extents, that is, how many extents are allocated to a, not how many blocks.

Segment: is made up of a series of districts, in general, when an object is created (a table, an index), a segment is assigned to the object. So in a sense, a segment is a certain kind of data. such as Create TABLE Pm_user, this segment is the data segment, and create INDEX On Pm_user (NAME), Oracle also assigns a segment to the index, but this is an index segment. The information for a query segment can be obtained from the data dictionary: SELECT * from User_segments.

Table spaces: Contains segments, extents, and blocks. The data for the tablespace is physically stored in the data file in which it resides. A database must have at least one tablespace.

All Oracle segments have an upper bound for the data in the paragraph, and we call this cap "high water mark" or HWM. This HWM is a tag that shows how much of the unused block of data is allocated to this segment.

The HWM usually grows at a rate of 5 pieces of data, in principle HWM will only increase, will not shrink, even if the data in the table all deleted, HWM or for the original value, because of this feature, so that HWM very much like a reservoir of the history of the highest water level, which is the original meaning of HWM, of course, can not say a reservoir no water, The highest water level in the reservoir is about 0. But if we use the TRUNCATE command on the table, the table's HWM will be reset to 0.

How do I know the hwm of a table?

(a) First, the table is analyzed:

ANALYZE TABLE <tablename> Estimate/compute STATISTICS;

b) To view relevant information:

SELECT blocks, empty_blocks, num_rows from User_tables WHERE table_name =<tablename>;

[SYS@ORCL] Sql>select segment_name,segment_type,blocks from dba_segments where segment_name= ' A ';

Segment_name Segment_type BLOCKS

------------ ------------ -------

A TABLE 8

A table can be obtained with 8 blocks assigned.

[TEST1@ORCL] Sql>analyze table A compute statistics;

The table is parsed.

[TEST1@ORCL] Sql>select num_rows,blocks,empty_blocks from User_tables where table_name= ' A '

Num_rows BLOCKS Empty_blocks

---------- ---------- ------------

1 5 3

The BLOCKS column represents the number of database blocks that have been used in the table, that is, the watermark. Empty_blocks represents a block of data that is assigned to the table, but above the waterline, that is, never used.

[TEST1@ORCL] Sql>delete from A;

[TEST1@ORCL] Sql>analyze table A compute statistics;

The table is parsed.

[TEST1@ORCL] Sql>select num_rows,blocks,empty_blocks from User_tables where table_name= ' A ';

Num_rows BLOCKS Empty_blocks

---------- ---------- ------------

0 5 3

[TEST1@ORCL] Sql>select count (Distinct dbms_rowid.rowid_block_number (ROWID) | |

2 Dbms_rowid.rowid_relative_fno (ROWID)) "used" from A;

Used

----------

0 Note: Used = 0 This table name does not have any database blocks to hold the data, that is, no data in the table