Shulou(Shulou.com)06/01 Report--

In the Oracle database, the disk capacity occupied by the table increases as the data grows. When the data in the table is no longer needed, delete is performed on the table. The high water level, which represents the space occupied by the table, will not decrease with the data deletion, and the high water level will not change. Even if the database is deleted, the space occupied by the table will remain the same. Therefore, if you want to free up space, you need to process the table as follows:

1. If the table space in which the table resides is automatically managed by segment space (i.e. ASSM), shink operation can be performed on the table to release free data blocks and lower the high water level, and shink operation does not have to suspend all businesses:

To see if the tablespace is segment space automatic management (ASSM) can be viewed through the view dba_tablespaces.

Command:

Select tablespace_name,segment_space_manegement from dba_tablespaces

If the value of segment_space_manegement is not AUTO, which means that the tablespace is not ASSM, the shink operation cannot be performed, only the move operation can be performed on the table.

For more information on how to shrink the table, please refer to: http://blog.itpub.net/30484956/viewspace-2678181/

Second, releasing the high water level can also perform move operation on the meter, and all business needs to be stopped during the move operation. However, after the move operation on the table, the indexes and constraints on the table will all fail and need to be rebuilt by rebuild.

Commands for move operation:

The name of the table that alter table table_name move;table_name---- wants to free up space

Third, the move and shink operations of the table are only for the ordinary fields in the table, and separate operations are required for the columns of the LOB data type.

Command to release the space occupied by the LOB field:

Alter table table_name move lob (lobsegment_name) stoge as tablespace_name;table_name---- the table name containing the lob field the original tablespace where the column name tablespace_name----lob field of the LOB data type is stored in the lobsegment_name----l table

Each lob field in a table with a lob field needs to be move, and the stored table space is also the table space where the lob field was specified when the table was originally created.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.