Oracle clears the high waterline of the table, and oracle clears the waterline
When a new record is added, HWM moves up slowly, but HWM does not move down after the record is deleted. Therefore, when you use the delete from command to delete a large amount of data, the HWM may cause performance problems. The most direct method to delete a high watermark is truncate Table. However, this method is too violent. Therefore, use the following method to delete the high watermark line.
-- DELETE data FROM GJDS_BUS_OIL_LOGWHERE VERSION <(13112-100 );
-- Fast Compression
It is best to remember to move when you are idle. The move will produce the tmlock. After moving, remember to re-create the index. |
Alter table GJDS_BUS_OIL_LOG MOVE;
-- After compression, all indexes will expire. You need to re-create the index alter index INDEX_NAME REBUILD;
-- ANALYZE the following TABLE: analyze table GJDS_BUS_OIL_LOG compute statistics for table for all indexes for all indexed columns;
-- View the total number of blocks select SEGMENT_NAME, EXTENTS, blocks from USER_SEGMENTS WHERE SEGMENT_NAME = 'gjds _ BUS_OIL_LOG '; -- View high-water select blocks, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME = 'gjds _ BUS_OIL_LOG 'and owner = 'bus'; -- check the number of select count (DISTINCT partition (ROWID) USED_BLOCK FROM GJDS_BUS_OIL_LOG S;
Another method is to use shrink space, which cannot be used in the following cases ■ IOT mapping tables
■ Tables with rowid based materialized views
■ Tables with function-based indexes -- Function Index
■ SECUREFILE LOBs
■ Compressed tables
If a function index needs to be deleted, you can use -- enable row movementalter table GJDS_BUS_OIL_LOG enable row movement; -- compression segment alter table GJDS_BUS_OIL_LOG shrink space; you also need to re-index and re-analyze the TABLE.
References: http://www.blogjava.net/decode360/archive/2009/07/14/287767.html
How Does oracle view the table's high water level line?
Select blocks, empty_blocks from dba_tables where table_name = 'xxx' and owner = 'xx ';
Blocks is the allocated space (HWM). The actually allocated space is not the actual size.
Why do some oracle tables have no high water level lines? I have created a table myself. Why?
The high water level line is set by the Oracle system. This is related to the usage of data blocks. It is not set by humans, but a performance adjustment by the system itself.