Use of the oracle Shrink command

Starting from 10 Gb, oracle began to provide Shrink commands. If our tablespace supports automatic segment space management (ASSM), we can use this feature to narrow down the segment, that is, reduce the HWM. Segment shrink is divided into two phases: 1. Data restructuring (compact): through a series of insert and delete operations, data is arranged in front of the segment as much as possible. In this process, the RX lock must be applied to the table, that is, the row to be moved must be locked. Due to changes in rowid, enable row movement is required. At the same time, the rowid-based trigger must be disable. This process has little impact on the business. 2. HWM adjustment: the second stage is to adjust the HWM location and release idle data blocks. This process requires an X lock on the table, which will block all DML statements on the table. It may have a big impact on systems with extremely busy services. The shrink space statement is executed in both stages. Shrink space compact only executes the first stage. If the system is busy, run shrink space compact to reorganize the data, and then run shrink space to reduce HWM to release idle data blocks when the business is not busy. The row migration function must be enabled for shrink. Alter table table_name enable row movement; note: the alter table XXX enable row movement statement will invalidate the objects (such as stored procedures, packages, and views) that reference table XXX. After the execution is complete, it is best to execute utlrp. SQL to compile invalid objects. Syntax: alter table <table_name> shrink space [<null> | compact | cascade]; alter table <table_name> shrink space compcat; www.2cto.com shrink table, it is equivalent to setting up data in the block, but it will keep high water mark; alter table <tablespace_name> shrink space; shrink the table to reduce the high water mark; alter table <tablespace_name> shrink space cascade; shrink the table to lower the high water mark, and the related indexes must also be reduced to lower the limit. Alter index idxname shrink space; shrink index 1: a SQL script for a common table. If you change the script, 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 occurs when the sharding table is processed for shrink space. shrink space has some limitations. creating a function index (including full-text index) on a table will fail. The SQL script generates 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'; www.2cto.com select 'alter table' | segment_name | 'modify subpartition' | partition_name | 'shrink space; '| chr (10) from us Er_segments where segment_type = 'table subpartition'; in addition, for frequently-operated tables, the db_buffer_pool of oracle that can be cached to memory is composed of three parts: buffer_pool_defualt buffer_pool_keep buffer_pool_recycle if you want to pin the table to the memory, that is, pin the table to the keep area. Related commands: alter table ..... Storage (buffer_pool keep); this command indicates that the table is cached in the keep area if it is cached. You can run the following statement: select table_name from dba_tables where buffer_pool = 'keep '; query that the modified table is in the KEEP area. But it does not mean that the table has been cached. The following statement caches the table: alter table .... Cache; you can use select table_name from dba _ tables where rtrim (cache) = 'y' to query whether the table has been cached. The table added to the keep area does not mean that it cannot be removed from the memory, but is not easy to remove from the memory. You can also manually remove the memory. The command is as follows: alter table... Nocache; Author: 13594135