Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Oracle table defragmentation

2025-01-25 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

It is Singles' Day again, and Singles Day is a nightmare for it people engaged in e-commerce. This is just a prelude. Here are the main points:

Table defragmentation, first collect those tables that need to be defragmented:

1.1 check table fragments based on statistical information:

SELECT table_name

ROUND ((blocks 8), 2) "high water space k"

ROUND ((num_rows avg_row_len / 1024), 2) "Real use Space k"

ROUND ((blocks 10 / 100) 8,2) "reserved space (pctfree) k"

ROUND ((blocks 8-(num_rows avg_row_len / 1024)-blocks 8 10 / 100), 2) "waste of space k"

FROM user_tables

WHERE temporary ='N'

ORDER BY 5 DESC

1.2. Is to communicate with business developers who have done a lot of delete and update operations to determine the scope of the tables to be sorted out.

2.1. Here are the defragmentation steps:

Alter table app_info enable row movement;-Open Line Mobility

Alter table app_info shrink space cascade;-compress tables and related data segments and downgrade HWM (this step will affect business)

Alter table app_info shrink space compact;-only compress but not down-regulate HWM

Alter table app_info shrink space;-- downgrade HWM (this step will affect the business)

Alter table app_info disable row movement;-turn off line movement

Where alter table app_info shrink space compact; alter table app_info shrink space; two steps are equal to alter table app_info shrink space cascade; operation

Note:

IOT indexes organize tables, base tables for materialized views created with rowid, tables with functional indexes, SECUREFILE large objects, compressed tables cannot use Shrink operations.

3.1 it is best to re-collect statistics after defragmentation:

Begin

Dbms_stats.gather_table_stats (ownname = > 'chunqiu',tabname = >' app_info',cascade = > true)

End

4.1 the following is a reference written in the plsql statement block in preparation for the following script:

Begin

EXECUTE IMMEDIATE 'alter table app_info shrink space'

EXECUTE IMMEDIATE 'alter table app_info disable row movement'

End

5.1 what if there are too many watches? The special feature is that the final lowering of the high water mark basically needs to be operated during the business trough at night, and some may even apply for a war-free card. Write a script below for batch processing and scheduled tasks to save dba time and have a good rest:

Create table T_TABALE

(

Table_name VARCHAR2 (200) not null

Compact_status NUMBER default 0 not null

Shrink_status NUMBER default 0 not null

)

Alter table T_TABALE add constraint PK_T_TABALE primary key (TABLE_NAME)

Insert the name of the table you want to organize into the table.

5.1. Enable row movement first:

BEGIN

FOR i IN (select table_name from T_TABALE) LOOP

EXECUTE IMMEDIATE 'alter table' | | i.table_name | | 'enable row movement'

END LOOP

END

5.2. Defragment:

BEGIN

FOR i IN (select table_name from T_TABALE where compact_status = 0) LOOP

Begin

EXECUTE IMMEDIATE 'alter table' | | i.table_name | | 'shrink space compact'

Update T_TABALE set status = 1 where table_name = i.table_name

Commit

EXCEPTION WHEN OTHERS THEN null

END

END LOOP

END

5.3. To reduce the high water level, it is best to perform the steps in combination with scheduled tasks at night:

This can be written as a scheduled task and executed at night.

BEGIN

FOR i IN (select table_name from T_TABALE where compact_status = 0) LOOP

Begin

EXECUTE IMMEDIATE 'alter table' | | i.table_name | | 'shrink space'

Update T_TABALE set shrink_status = 1 where table_name = i.table_name

Commit

EXCEPTION WHEN OTHERS THEN null

END

END LOOP

END

5.5 close row movement:

BEGIN

FOR i IN (select table_name from T_TABALE) LOOP

BEGIN

EXECUTE IMMEDIATE 'alter table' | | i.table_name | | 'disable row movement'

END LOOP

END

6.1 finally, don't forget to collect statistics, and the batch script that collects statistics can be implemented on its own.

Suddenly remembered, for reference:

BEGIN

FOR i IN (select blocks*8/1024/1024, table_name from dba_tables where table_name in (select table_name from pacs.T_TABALE where status = 11) order by 1) LOOP

Begin

EXECUTE IMMEDIATE 'begin dbms_stats.gather_table_stats (ownname = >' | |''pacs''' | |', tabname = >''| | i.table_name | |''| cascade = > true); end;'

Update pacs.T_TABALE set status = 12 where table_name = i.table_name

Commit

-- EXCEPTION WHEN OTHERS THEN null

END

END LOOP

END

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.

Views: 251

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report