Search method of ORACLE High Water level Meter

Introduction of high water level

The database has been running for a period of time, and after some column deletion, insertion and change operations, the high water mark of some tables may be very different from the actual table storage data. In order to improve the efficiency of retrieving the table, it is recommended that these tables be shrunk.

Look up the table of the high water mark look up the storage space needed by the table: the table is stored in the data file in the form of data blocks. The storage structure of the table is: rows × rows. If you know the total number of rows, the average length of each row, multiply the two, and divide by 90% utilization, then you can know the actual storage space.

The storage structure of the table

The average length of each row and the total number of rows are obtained from the statistics to know the stored SIZE

The actual storage space of the lookup table: the data is actually stored in blocks in the data file. 8K for each data file, and the number of blocks is multiplied by 8k, so you can know how much space has actually been stored.

C) find the table under a tablespace in the database that can actually be stored with the greatest difference from the required tablespace. The lookup script is as follows:

SELECT NUM_ROWS,AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9 NEED, BLOCKS*8/1024 TRUE, (BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9) RECOVER_MB,TABLE_NAME

FROM dba_tables

WHERE tablespace_name='PSAPSR3' AND BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9 > 100

AND rownum select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS

From user_tables

Where table_name = upper ('table_name')

+

Author: JOHN

ORACLE technology blog: ORACLE hunter's note database technology group: 367875324 (please note ORACLE management)

+