In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.