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

This article mainly introduces "analyzing the high water level and shrink operation of Oracle table database". In the daily operation, I believe that many people have doubts in analyzing the high water level and shrink operation of Oracle table database. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "analyzing the high water level and shrink operation of Oracle table database". Next, please follow the editor to study!

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. 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.

Shrink must enable the row migration feature.

Alter table table_name enable row movement

Note: the alter table XXX enable row movement statement causes objects (such as stored procedures, packages, views, and so on) that reference the table XXX to become invalid. When the execution is complete, it is best to execute utlrp.sql to compile the invalid object.

Syntax:

Alter table shrink space [| compact | cascade]

Alter table shrink space compcat

Shrink the table, which is equivalent to strengthening the data in the block, but will 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 index for a while.

Alter index idxname shrink space; retractive index

1: normal table Sql script. Changing the script will generate the corresponding statement.

Select'alter table'| | table_name | | enable row movement;' | | chr (10) | | 'alter table' | | table_name | | 'shrink space;' | | chr (10) from user_tables

Select'alter index'| | index_name | | 'shrink space;' | | chr (10) from user_indexes

2: ORA-10631 error occurred during shrink space in the processing of partition table. Shrink space has some restrictions. Building functional indexes (including full-text indexes) on the table will fail.

Sql script. Changing the script will generate the corresponding statement.

Select 'alter table' | | table_name | | 'enable row movement;' | | chr (10) | |' alter table'| | table_name | | 'shrink space;' | | chr (10) from user_tables where

Select 'alter index' | | index_name | | 'shrink space;' | | chr (10) from user_indexes where uniqueness='NONUNIQUE'

Select 'alter table' | | segment_name | | 'modify subpartition' | | partition_name | | 'shrink space;' | | chr (10) from user_segments where segment_type='TABLE SUBPARTITION''

In addition, the db_buffer_pool that can be cached to in-memory oracle for frequently manipulated tables consists of three parts:

Buffer_pool_defualt

Buffer_pool_keep

Buffer_pool_recycle

If you want to nail the watch in memory, that is, nail the watch in the keepsake area.

The relevant commands are:

Alter table... .. Storage (buffer_pool keep)

This command caches the representation table in the keep area if it is cached.

You can use the statement:

Select table_name from dba_tables where buffer_pool='KEEP'

Query that the change table is placed in the keep area. But that doesn't mean the table has been cached.

The following statement caches the table:

Alter table... . Cache

Can be passed through

Select table_name from dba_ tables where rtrim (cache) ='Y'

The query indicates that the table has been cached.

The table added to the keep section does not mean that it cannot be moved out of memory, but it is less easy to move out of memory.

You can also remove memory manually, with the following command:

Alter table... Nocache

At this point, the study on "analyzing the high water level and shrink operation of Oracle database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.