Oracle Shrink Table size Oracle Shrink Table

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. Here we need to emphasize that this new 10g feature is only valid for the ASSM tablespace, otherwise the ORA-10635: Invalid segment or tablespace type will be reported.

If you perform DML operations on a table frequently, data in database blocks will be distributed in a sparse manner, wasting a lot of space. At the same time, it will also affect the performance of full table scan because full table scan requires more data blocks to be accessed. From oracle10g, the table can use shrink to reorganize the data so that the data distribution is closer, while reducing HWM to release idle data blocks.

Segment shrink is divided into two phases:

1. Data restructuring (compact): Data is arranged in front of the segment as much as possible through a series of insert and delete operations. In this process, www.bkjia.com needs to apply the RX lock to the table, that is, to lock only the row to be moved. 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 www.bkjia.com is executed, it is best to execute utlrp. SQL to compile invalid objects.

========================================================== ========================================================== ================

Utlrp. SQL and utlgp. SQL
The utlrp. SQL and utlgp. SQL scripts are provided by Oracle to recompile all invalid objects in the database. they are typically run after major database changes such as upgrades or patches. they are located in the $ ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. the utlrp. SQL script simply callthe utlgp. SQL script with a command line parameter of "0 ". the utlgp. SQL accepts a single integer parameter that indicates the level of parallelism as follows:

0-The level of parallelism is derived based on the CPU_COUNT parameter.
1-The recompilation is run serially, one object at a time.
N-The recompilation is run in parallel with "N" number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

========================================================== ========================================================== ================

1 2 3 Next Page