Shulou(Shulou.com)05/31 Report--

This article focuses on "how to use alter table move and shrink space". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn how to use alter table move and shrink space.

It is well known that alter table move or shrink space can shrink segments to eliminate partial row migration, eliminate space debris, and make data more compact, but move is still different from shrink space.

Move will move the high water level, but will not release the application space, is below the high water level (below HWM) operation.

Shrink space will also move the high water level, but it will also free up the space for application, which can be done both above and below the high water level (below and above HWM).

Move solves the problem:

a. We can use move to move one table from the current tablespace to another tablespace:

Alter table t move tablespace tablespace_name

b. We can also use move to change the storage parameters of table's existing block, such as:

Alter table t move storage (initial 30k next 50k)

c. In addition, the move operation can also be used to solve the problem of row migration in table.

Some considerations for using move:

A. Index on table requires rebuild:

As we discussed earlier, the rowid of the data has changed after the move operation. We know that index uses rowid to fetch data rows, so index on table must be rebuild.

Alter index index_name rebuild online

Lock on table when b.move

When we move table, we can find that exclusive lock is added to table by querying the v$locked_objects view.

c. About the use of space in move:

There is one thing to note when we use alter table move to reduce the HWM of table. At this time, the current tablespace needs to have twice as much free space as table.

Shrink space syntax:

Alter table

Shrink space [| compact | cascade]

Alter table

Shrink space compcat

Shrink the table, but keep the high water mark

Alter table

Shrink space

Shrink the table, lower the high water mark

Alter table

Shrink space cascade

Shrink the table, lower the high water mark, and shrink the related indexes as well

There are two prerequisites for using shrink:

1. Row movement must be enabled for the table, such as:

Alter table nonsrt.TAB_EZG_BIZ_UNCONTRACTED enable row movement

Alter table nonsrt.TAB_EZG_BIZ_UNCONTRACTED shrink space

2. The segment space management (segment space management) of the table space where the table segment is located must be auto.

Segment shrink is divided into two phases:

1. Data reorganization (compact): arrange the data in front of the segment as far as possible through a series of insert and delete operations. In the process, you need to put a RX lock on the table, that is, only on the rows that need to be moved. Because it involves the change of rowid, it needs enable row movement. At the same time, disable should be based on rowid trigger. This process has little impact on the business.

2. HWM adjustment: the second stage is to adjust the HWM location and release free data blocks. This process requires an X lock on the table, which blocks all DML statements on the table. It may have a greater impact on systems with particularly busy business.

Note: the shrink space statement is executed in both phases.

Shrink space compact only executes the first phase.

If the system business is busy, you can first execute shrink space compact to reorganize data, and then execute shrink space to reduce HWM to release free data blocks when the business is not busy.

At this point, I believe you have a deeper understanding of "how to use alter table move and shrink space". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.