The setting of INITIAL Parameter causes the truncate table to fail to reduce the case of the high water level line, and the truncate water level line

The setting of INITIAL Parameter causes the truncate table to fail to reduce the case of the high water level line, and the truncate water level line

Using the following SQL statement in a database, I found a batch of tables that need to reduce the high-water level line. Several of the tables do not have data, so I plan to use TRUNCATE to reduce the high-water level line HWM.

SELECT a.owner, 

       a.segment_name, 

       a.segment_type, 

       a.tablespace_name, 

       a.blocks              "real block", 

       a.bytes / 1024 / 1024 "realSizeMB", 

       b.last_analyzed, 

       b.num_rows 

FROM   dba_segments a, 

       dba_tables b 

WHERE  a.owner = b.owner 

       AND a.segment_name = b.table_name 

       AND B.partitioned = 'NO' 

       AND b.num_rows < 5000 

       AND a.blocks > 1000 

       AND a.bytes / 1024 / 1024 > 500 

ORDER  BY 6 DESC 

First, let's take a look at the space usage of one of the tables, as shown below. As a result, after performing TRUNCATE on the table, we found that the high water level line HWM has not changed at all.

SQL> exec show_space('INV_MONTH_END_LOCATION', 'INVENTORY');

Unformatted Blocks .....................               0

FS1 Blocks (0-25)  .....................               0

FS2 Blocks (25-50) .....................               0

FS3 Blocks (50-75) .....................               0

FS4 Blocks (75-100).....................               0

Full Blocks        .....................               0

Total Blocks............................         434,176

Total Bytes.............................   3,556,769,792

Total MBytes............................           3,392

Unused Blocks...........................         434,142

Unused Bytes............................   3,556,491,264

Last Used Ext FileId....................              40

Last Used Ext BlockId...................               9

Last Used Block.........................              34

PL/SQL procedure successfully completed.

SQL> exec show_space('INV_MONTH_END_LOCATION', 'INVENTORY');

Unformatted Blocks .....................               0

FS1 Blocks (0-25)  .....................               0

FS2 Blocks (25-50) .....................               0

FS3 Blocks (50-75) .....................               0

FS4 Blocks (75-100).....................               0

Full Blocks        .....................               0

Total Blocks............................         434,176

Total Bytes.............................   3,556,769,792

Total MBytes............................           3,392

Unused Blocks...........................         434,142

Unused Bytes............................   3,556,491,264

Last Used Ext FileId....................              40

Last Used Ext BlockId...................               9

Last Used Block.........................              34

PL/SQL procedure successfully completed.

At that time, I was dumb. Is it true that TRUNCATE will not release the storage space and reduce the high water level line? So I checked the information and confirmed that TRUNCATE would release the storage space and reduce the high water level line. So where is the problem? So I re-collected the statistics for the table and found that the problem still exists.

SQL> exec dbms_stats.gather_table_stats('INVENTORY','INV_MONTH_END_LOCATION', cascade=>true);

PL/SQL procedure successfully completed.

Finally, I generated an SQL statement to create the table and finally found the problem. As shown below. Initial and next decide to create a segment and extend the segment. initial indicates that the segment size allocated to the table during initialization is 3,556,769,792 bytes. That is, 3392 MB. However, I don't know who set this parameter, so I can only DROP the table and modify this parameter to recreate the table.

In addition, in this case, the use of alter move cannot release the tablespace and reduce the high-water level line. Remember.