发布在Itpub的，现在转过来这里。主要是在这个博客记录一些重要的脚本~
脚本目的：
主要的目的是整理磁盘碎片。在EBS里面有N多的接口表，或者有一些特殊的表格，经常Insert数据之后又Delete掉的。时间长之后，肯定会产生磁盘碎片，会导致高水位而带来的服务器效能的问题。换句话说这个优化就是清理系统使用过程中所产生的磁盘碎片。
高水位导致浪费磁盘空间之余，查询Table速度又慢，因为可能导致索引查询有问题。
所以，如何自动批量处理这部分的表格是我们遇到的问题。
处理对象：
处理的对象主要是DML(更新或者删除)频繁的临时表，特别是一堆接口表。
处理过程：
根据相关资料，整理了一个可以批量分析表格的是否高水位，然后自动用shrink space处理表格的脚本。
在10G数据库通过测试。
-----------------
注意：1 如果哪位兄台有兴趣使用这个脚本，强烈建议一定要先了解这个代码的处理逻辑，再选择是否使用。
2 鉴于个人的水平限制，如果脚本有什么误处理的地方，也请各位DBA高手斧正。。。
3 所有批量处理数据的DDL脚本必须是在系统空闲的时候执行！最好是停机备份之后再执行。如果在系统繁忙的时候做，会造成大量的等待，从而可能出现服务器瘫痪等问题。切记。
/*
--批量自动清理表空间的脚本
--2013.10.9 Create by sam.t
--逻辑：
处理对象：表(暂不处理分区表)和对应表的索引。
对于总块超过特定块数的（这里有个假设表格都是在最近1周内或者1个月内有分析过的。）
对特定的设定OWNER，再设定：存在空闲的块超过本身块的10%的，或者总的空闲块数量超过500个的。
就对应的表格立刻做：Shrink Segment的动作。如果表格存在索引，那索引也做Rebuild的动作。
*/
---建立日志表格：
CREATE TABLE XYG.XYG_DBA_SHRINK_TABLE_LOG
(BATCH_ID NUMBER
,OWNER VARCHAR2 (30 Byte)
,TABLE_NAME VARCHAR2 (30 Byte)
,BLOCKS NUMBER
,FREE_BLOCKS NUMBER
,SHRINK_BLOCKS NUMBER
,SHRINK_FLAG VARCHAR2(1) DEFAULT 'N' NOT NULL
,CREATED_BY NUMBER DEFAULT -1 NOT NULL --创建者
,CREATION_DATE DATE DEFAULT SYSDATE NOT NULL --创建日期
);
---这个SQL可以看到整理之后压缩了多少数据块，对应多大空间（MB）。注意，我这里一个数据块是8KB的空间。
SELECT SUM(SHRINK_BLOCKS)*8/1024 MB
FROM XYG.XYG_DBA_SHRINK_TABLE_LOG
WHERE SHRINK_FLAG = 'Y';
SELECT A.* FROM XYG.XYG_DBA_SHRINK_TABLE_LOG A WHERE CREATED_BY = -2 AND SHRINK_FLAG = 'Y'
SELECT SUM(SHRINK_BLOCKS)*8/1024 MB FROM XYG.XYG_DBA_SHRINK_TABLE_LOG A WHERE CREATED_BY = -2 AND SHRINK_FLAG = 'Y'
----------------主处理脚本：
DECLARE
L_OWNER VARCHAR2(10);--表格的Owner。
L_SHRINK_TABLE VARCHAR2(50);
L_TABLE_BLOCKS NUMBER;--总设定的要处理的表格最小块数。
L_FREE_RATE NUMBER;--总设定的比例.为空则表示不对此做限制。
L_FREE_BLOCKS NUMBER;---总设定的空闲块数。为空则表示不对此做限制。
---
L_OWNER1 VARCHAR2(10);--特定处理的表格的Owner1。
L_OWNER1_FREE_RATE NUMBER;--对应Owner1的设定的比例.为空则表示不对此做限制。
L_OWNER1_FREE_BLOCKS NUMBER;---对应的Owner1空闲块数。为空则表示不对此做限制。
---下面雷同，可以特殊4个Owner的处理。
L_OWNER2 VARCHAR2(10);
L_OWNER2_FREE_RATE NUMBER;
L_OWNER2_FREE_BLOCKS NUMBER;
L_OWNER3 VARCHAR2(10);
L_OWNER3_FREE_RATE NUMBER;
L_OWNER3_FREE_BLOCKS NUMBER;
L_OWNER4 VARCHAR2(10);
L_OWNER4_FREE_RATE NUMBER;
L_OWNER4_FREE_BLOCKS NUMBER;
--
L_FACT_FREE_RATE NUMBER;
L_FACT_FREE_BLOCKS NUMBER;
L_DEBUG_MODE BOOLEAN := FALSE;
L_ANALY_FREE_BLOCKS NUMBER;
L_ANALY_TABLE BOOLEAN := FALSE;
L_BATCH_ID NUMBER;
L_SHRINK_FLAG VARCHAR2(1);
L_SOURCE_TABLE_BLOCKS NUMBER;--原来的块数
L_SHRINK_TABLE_BLOCKS NUMBER;--整理碎片之后的收缩的块数
L_TOTAL_SHRINK_MB NUMBER;
---分析表格的空间用到的参数
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
----
CURSOR CUR_TABLES IS
SELECT OWNER,TABLE_NAME,BLOCKS,NUM_ROWS,ROW_MOVEMENT
FROM ALL_TABLES
WHERE 1=1
AND OWNER NOT IN ('SYS', 'SYSTEM')
AND TABLE_NAME NOT LIKE '%$%'
AND TEMPORARY = 'N'
AND PARTITIONED = 'NO'
AND BLOCKS > 0
AND (TABLE_NAME LIKE L_SHRINK_TABLE OR L_SHRINK_TABLE IS NULL)
AND (OWNER = L_OWNER OR L_OWNER IS NULL)
AND BLOCKS > L_TABLE_BLOCKS
AND NOT EXISTS(
SELECT 1 FROM XYG.XYG_DBA_SHRINK_TABLE_LOG XDT
WHERE XDT.OWNER = ALL_TABLES.OWNER
AND XDT.TABLE_NAME=ALL_TABLES.TABLE_NAME
AND XDT.SHRINK_FLAG <> 'E'
AND XDT.BATCH_ID = L_BATCH_ID)
ORDER BY OWNER,BLOCKS DESC;
procedure DEBUG_OUT( p_label in varchar2, p_num in number DEFAULT NULL )
is
begin
IF p_num IS NULL THEN
dbms_output.put_line( p_label);
ELSE
dbms_output.put_line( rpad(p_label,40,'.') ||p_num );
END IF;
end;
BEGIN
---输入的参数
--L_OWNER := 'XYG';
--L_SHRINK_TABLE := '%MESSAGE%';
L_TABLE_BLOCKS := 1000;
--默认的限制条件
L_FREE_RATE := 40;--40%以上的空闲空间的才考虑整理
L_FREE_BLOCKS := 1000;
L_DEBUG_MODE := FALSE;--TRUE;
L_BATCH_ID := 1;
---
L_OWNER1 := 'XYG';
L_OWNER1_FREE_RATE := 10;
L_OWNER1_FREE_BLOCKS := 100;
-----------------------
L_TOTAL_SHRINK_MB := 0;
L_SHRINK_FLAG := 'N';
--L_ANALY_TABLE := TRUE;
dbms_output.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('---处理日期:'||TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
---开始处理。
FOR REC_TABLES IN CUR_TABLES LOOP
L_ANALY_FREE_BLOCKS := 0;
L_SHRINK_FLAG := 'N';
L_SHRINK_TABLE_BLOCKS := 0;
---分析表的块的空闲率
dbms_space.space_usage(
segment_owner => REC_TABLES.owner ,
segment_name => REC_TABLES.TABLE_NAME ,
segment_type => 'TABLE' ,
unformatted_blocks => l_unformatted_blocks ,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes ,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks ,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
IF L_DEBUG_MODE THEN
DEBUG_OUT(rpad(' ',50,'*'));
DEBUG_OUT('Show Space Segment:'||REC_TABLES.owner ||'.'||REC_TABLES.TABLE_NAME);
DEBUG_OUT('The segment is analyzed');
DEBUG_OUT( '0% -- 25% free space blocks', l_fs1_blocks);
DEBUG_OUT( '0% -- 25% free space bytes', l_fs1_bytes);
DEBUG_OUT( '25% -- 50% free space blocks', l_fs2_blocks);
DEBUG_OUT( '25% -- 50% free space bytes', l_fs2_bytes);
DEBUG_OUT( '50% -- 75% free space blocks', l_fs3_blocks);
DEBUG_OUT( '50% -- 75% free space bytes', l_fs3_bytes);
DEBUG_OUT( '75% -- 100% free space blocks', l_fs4_blocks);
DEBUG_OUT( '75% -- 100% free space bytes', l_fs4_bytes);
DEBUG_OUT( 'Unused Blocks', l_unformatted_blocks );
DEBUG_OUT( 'Unused Bytes', l_unformatted_bytes );
DEBUG_OUT( 'Total Blocks', l_full_blocks);
DEBUG_OUT( 'Total bytes', l_full_bytes);
END IF;
L_FACT_FREE_RATE := CASE WHEN REC_TABLES.owner = L_OWNER1 THEN L_OWNER1_FREE_RATE
WHEN REC_TABLES.owner = L_OWNER2 THEN L_OWNER2_FREE_RATE
WHEN REC_TABLES.owner = L_OWNER3 THEN L_OWNER3_FREE_RATE
WHEN REC_TABLES.owner = L_OWNER4 THEN L_OWNER4_FREE_RATE
ELSE L_FREE_RATE
END;
L_FACT_FREE_BLOCKS := CASE WHEN REC_TABLES.owner = L_OWNER1 THEN L_OWNER1_FREE_BLOCKS
WHEN REC_TABLES.owner = L_OWNER2 THEN L_OWNER2_FREE_BLOCKS
WHEN REC_TABLES.owner = L_OWNER3 THEN L_OWNER3_FREE_BLOCKS
WHEN REC_TABLES.owner = L_OWNER4 THEN L_OWNER4_FREE_BLOCKS
ELSE L_FREE_BLOCKS
END;
IF L_DEBUG_MODE THEN
DEBUG_OUT('L_FACT_FREE_RATE:'||L_FACT_FREE_RATE);
DEBUG_OUT('L_FACT_FREE_BLOCKS:'||L_FACT_FREE_BLOCKS);
END IF;
L_ANALY_FREE_BLOCKS := (NVL(l_fs1_blocks,0)+NVL(l_fs2_blocks,0)+NVL(l_fs3_blocks,0)+NVL(l_fs4_blocks,0));
SELECT SUM(BLOCKS)
INTO L_SOURCE_TABLE_BLOCKS
FROM dba_segments
WHERE OWNER = REC_TABLES.owner AND SEGMENT_NAME = REC_TABLES.TABLE_NAME AND SEGMENT_TYPE = 'TABLE';
IF L_DEBUG_MODE THEN
DEBUG_OUT('表格总块数:'||L_SOURCE_TABLE_BLOCKS||' 存在空闲的块数:'||L_ANALY_FREE_BLOCKS);
END IF;
IF (L_ANALY_FREE_BLOCKS/L_SOURCE_TABLE_BLOCKS)*100 >=L_FACT_FREE_RATE
OR L_ANALY_FREE_BLOCKS>= L_FACT_FREE_BLOCKS THEN
DEBUG_OUT('处理表格:'||REC_TABLES.owner ||'.'||REC_TABLES.TABLE_NAME);
L_SHRINK_FLAG := 'Y';
IF REC_TABLES.ROW_MOVEMENT = 'DISABLED' THEN
execute immediate 'alter table '||REC_TABLES.owner||'.'||REC_TABLES.TABLE_NAME||' enable row movement';
END IF;
BEGIN
execute immediate 'alter table '||REC_TABLES.owner||'.'||REC_TABLES.TABLE_NAME||' shrink space';
EXCEPTION
WHEN OTHERS THEN
DEBUG_OUT('处理表格出现异常错误，信息:'||SQLCODE ||'.'||SQLERRM);
L_SHRINK_FLAG := 'E';
END;
IF REC_TABLES.ROW_MOVEMENT = 'DISABLED' THEN
execute immediate 'alter table '||REC_TABLES.owner||'.'||REC_TABLES.TABLE_NAME||' disable row movement';
END IF;
COMMIT;
---下面的索引也全部重建。必须的。前提是已经成功有对表做过修整。
FOR REC_INDEX IN (
SELECT AI.OWNER,AI.INDEX_NAME
FROM all_indexes AI
WHERE OWNER NOT IN ('SYS', 'SYSTEM')
AND AI.INDEX_NAME NOT LIKE '%$%'
AND AI.TABLE_OWNER = REC_TABLES.owner
AND AI.TABLE_NAME = REC_TABLES.TABLE_NAME
AND L_SHRINK_FLAG = 'Y'
ORDER BY 2) LOOP
IF L_DEBUG_MODE THEN
DEBUG_OUT('重建索引:'||REC_INDEX.owner||'.'||REC_INDEX.INDEX_NAME);
END IF;
BEGIN
execute immediate 'ALTER INDEX '||REC_INDEX.owner||'.'||REC_INDEX.INDEX_NAME||' REBUILD ONLINE';
EXCEPTION
WHEN OTHERS THEN
DEBUG_OUT('重建表格索引出现异常错误，信息:'||SQLCODE ||'.'||SQLERRM);
--可能是表空间不足，那就不需要再往下处理了。直接退出
exit;
END;
/*
--重新分析索引
begin
dbms_stats.gather_index_stats(ownname => REC_INDEX.owner,
indname => REC_INDEX.INDEX_NAME,
estimate_percent => 33
);
end;*/
END LOOP;
--表格重新分析！
---如果需要重新分析表格，则执行。主要是速度的问题。如果重新分析表格和索引，速度会很慢。可以根据日志表格来统一执行也行。
IF L_ANALY_TABLE AND L_SHRINK_FLAG = 'Y' THEN
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
OWNNAME => REC_TABLES.OWNER
,TABNAME => REC_TABLES.TABLE_NAME
,METHOD_OPT => 'FOR ALL COLUMNS SIZE 1' --不统计直方图的信息 --'FOR ALL INDEXED COLUMNS SIZE AUTO'
,CASCADE => TRUE ---TRUE就是分析索引的意思。
,ESTIMATE_PERCENT => 33--DBMS_STATS.AUTO_SAMPLE_SIZE
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (REC_TABLES.TABLE_NAME || '-' || SQLERRM);
END;
END IF;
IF L_SHRINK_FLAG = 'Y' THEN
SELECT L_SOURCE_TABLE_BLOCKS - SUM(BLOCKS)
INTO L_SHRINK_TABLE_BLOCKS
FROM dba_segments
WHERE OWNER = REC_TABLES.owner AND SEGMENT_NAME = REC_TABLES.TABLE_NAME AND SEGMENT_TYPE = 'TABLE';
--换算为MB单位，注意这里每一个块对应的大小是数据库的设定，可以通过v$parameter查询。我这里设定1块=8kb
L_TOTAL_SHRINK_MB := L_TOTAL_SHRINK_MB + L_SHRINK_TABLE_BLOCKS*8/1024;
END IF;
IF L_DEBUG_MODE THEN
DEBUG_OUT('---整理磁盘碎片之后收缩的块数-空间'||L_SHRINK_TABLE_BLOCKS||'--'||ROUND(L_SHRINK_TABLE_BLOCKS*8/1024,4)||')---');
END IF;
END IF;
---塞入临时的表格。
INSERT INTO XYG.XYG_DBA_SHRINK_TABLE_LOG
(BATCH_ID,OWNER,TABLE_NAME,BLOCKS,FREE_BLOCKS,SHRINK_BLOCKS,SHRINK_FLAG,CREATED_BY)
SELECT L_BATCH_ID,REC_TABLES.owner,REC_TABLES.TABLE_NAME
,L_SOURCE_TABLE_BLOCKS,L_ANALY_FREE_BLOCKS,L_SHRINK_TABLE_BLOCKS,L_SHRINK_FLAG,-2
FROM DUAL;
END LOOP;
DEBUG_OUT('本次整理磁盘碎片之后共收缩的空间(MB)'||L_TOTAL_SHRINK_MB||')');
COMMIT;
END;
------我自己打处理步骤：
预计每半年做一次。每次是需要分批做。
第一批：
1 所有的XYG开头的表格都要处理一次。
L_OWNER := 'XYG'
--L_SHRINK_TABLE := 'XYG_ASSEMBLE_BOX';
L_TABLE_BLOCKS := 100;
--默认的限制条件
L_FREE_RATE := 50;
L_FREE_BLOCKS := 2000;
L_DEBUG_MODE := TRUE;
L_BATCH_ID := 1;
---
L_OWNER1 := 'XYG';
L_OWNER1_FREE_RATE := 10;
L_OWNER1_FREE_BLOCKS := 100;
本次整理磁盘碎片之后共收缩的空间(MB)305)
2 系统所有的接口表，肯定是很多很慢的：
--因为这个语句不可以整理含有函数索引的表的碎片，所以在做之前要删掉索引。再重建。
DROP INDEX AR.RA_INTERFACE_LINES_N7;
CREATE INDEX AR.RA_INTERFACE_LINES_N7 ON AR.RA_INTERFACE_LINES_ALL
(NVL("INTERFACE_STATUS",'SAMT'), SALES_ORDER, CREATION_DATE)
LOGGING
TABLESPACE APPS_TS_INTERFACE
PCTFREE 10
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
---输入的参数
--L_OWNER := 'XYG';
L_SHRINK_TABLE := '%INTER%';
L_TABLE_BLOCKS := 0;
--默认的限制条件
L_FREE_RATE := 10;
L_FREE_BLOCKS := 100;
L_DEBUG_MODE := TRUE;
L_BATCH_ID := 1;
---
L_OWNER1 := 'XYG';
L_OWNER1_FREE_RATE := 10;
L_OWNER1_FREE_BLOCKS := 100;
本次整理磁盘碎片之后共收缩的空间(MB)425.5)
3 系统所有的消息表，可能是临时的作用。
---输入的参数
--L_OWNER := 'XYG';
L_SHRINK_TABLE := '%MESSAGE%';
L_TABLE_BLOCKS := 0;
--默认的限制条件
L_FREE_RATE := 10;
L_FREE_BLOCKS := 100;
L_DEBUG_MODE := TRUE;
L_BATCH_ID := 1;
---
L_OWNER1 := 'XYG';
L_OWNER1_FREE_RATE := 10;
L_OWNER1_FREE_BLOCKS := 100;
4 系统所有的临时表
L_SHRINK_TABLE := '%TEMP%';
本次整理磁盘碎片之后共收缩的空间(MB)25.25)
5 系统所有的可能存在问题的表格--慎重！
正式环境可以不做这个步骤。
---输入的参数
--L_OWNER := 'XYG';
--L_SHRINK_TABLE := '%MESSAGE%';
L_TABLE_BLOCKS := 1000;
--默认的限制条件
L_FREE_RATE := 40;--40%以上的空闲空间的才考虑整理
L_FREE_BLOCKS := 1000;
L_DEBUG_MODE := FALSE;--TRUE;
L_BATCH_ID := 1;
---
L_OWNER1 := 'XYG';
L_OWNER1_FREE_RATE := 10;
L_OWNER1_FREE_BLOCKS := 100;
本次整理磁盘碎片之后共收缩的空间(MB)844.375)
,'WF_DEFERRED'
,'FND_CRM_HISTORY'
,'FND_CONC_REQUEST_ARGUMENTS'
,'FND_RUN_REQ_PP_ACTIONS'
,'FND_CP_GSM_IPC_AQTBL'
,'ICX_TRANSACTIONS'
,'ICX_SESSIONS'
,'ICX_SESSION_ATTRIBUTES'
,'MTL_CATALOG_SEARCH_ITEMS'
,'OE_PROCESSING_MSGS'
,'OE_PROCESSING_MSGS_TL'
,'PO_EMPLOYEE_HIERARCHIES_ALL'
,'WSH_EXCEPTIONS'
,'XLA_AAD_LOADER_DEFNS_T'
,'FND_CP_GSM_OPP_AQTBL'
,'AD_SNAPSHOT_BUGFIXES'
,'FND_RUN_REQUESTS'
,'AD_TASK_TIMING'
,'FND_CONC_PP_ACTIONS'
,'FND_ENV_CONTEXT'
,'WF_JAVA_DEFERRED'
6 重点跟踪某些特定的大表。
主要是：FND_LOG_MESSAGES
------
备注：本文主要参考了文章：http://www.itpub.net/thread-1314057-1-1.html
142
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
