What is High Water Mark in Oracle?

 

The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, they are ready to use.
The blocks below the HWM level is used blocks, they are already used.

For example'

if you delete some huge records from the database, that data will delete but the blocks are not ready to used, because that blocks are still below HWM level, so delete command never reset the HWM level,

At the same time you truncate the date, then the data will delete and that used blocks will goto above the HWM level, now its ready to used. now they consider has free blocks.

with 10g there are two different HWM for a table

After deleting rows high water mark remain same, what will happen if i insert new rows now.where these new rows will be stored? above the HWM or below?
The space freed by deletions is available for new insertions, so it will be below, until this free space is filled.
Otherwise; Above the HWM when using the APPEND hint in the INSERT statement (or using DIRECT=Y in sql*loader)

They will remain under the HWM until the object is rebuilt, truncated, or shrunk (shrinking of a segment is a new Oracle 10g feature that is supported only if the segment is in an ASSM tablespace).

How to find Primary Key of a table

 

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = 'TABLENAME' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner AND cols.owner = 'sivaora' AND cons.owner = 'sivaora' ORDER BY cols.table_name, cols.position

This can be useful when getting error
ORA-00001: unique constraint (constraint_name) violated

ORA-00955: name is already used by an existing object

Cause: An attempt was made to create a database object (such as a table, view, cluster, index, or synonym) that already exists. A user's database objects must have distinct names.

Action: Enter a unique name for the database object or modify or drop the existing object so it can be reused.

Cross verify to find the existing object by the given name

SELECT   *   FROM   ALL_OBJECTS where Object_name like '%obj_name%'