本篇文章主要介绍了"分享个ORACLE整理表碎片的脚本"，主要涉及到方面的内容，对于Oracle感兴趣的同学可以参考一下：
表和存储过程的初始化设置文件文件夹autoshell文件夹下shell编写autoshell文件夹下sql编写开始执行shell由于业务数据库中不少表因为经常的...
#!/bin/sh########################################################################## DB TUNING MONITOR SCRIPT ## MUST run the initial scrpit at the first time ## to create procedure and table ########################################################################### Path Define
work_path=$HOME/monitor
#log=${work_path}/log#date_format_yyyymmdd
date_yyyymmdd=`date '+%Y%m%d'`
cd$work_path/log
rm -f *
### CHECH TABLE IF NEED TO BE REBUILD START ########################
sqlplus "/ as sysdba"</dev/null
set feedback off
setecho off
set heading off
set lines 300set pagesize 50000set trimspool on
col segment_name Format a32
col Owner Format a12
spool table_monitor_insert.temp
truncate table PERFSTAT.TB_MONITOR;
select 'insert into PERFSTAT.TB_MONITOR(owner,TABLE_NAME,CURRENT_SIZE) values( ' || '''' ||
Owner || ''',' || '''' || segment_name || '''' || ',' || sumsize || ');'
from (select Owner, segment_name, sum(bytes / 1024 / 1024) sumsize
from dba_segments
where segment_type = 'TABLE'
and Owner not in
('ANONYMOUS', 'AURORA$', 'AURORA', 'CTXSYS', 'DBSNMP', 'DIP',
'DMSYS', 'DVF', 'DVSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA',
'MDSYS', 'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPSYS',
'ORACLE_OCM', 'ORAWSM', 'ORDPLUGINS', 'ORDSYS', 'OSE',
'OUTLN', 'PERFSTAT', 'PM', 'QS', 'QS_ADM', 'QS_CB',
'QS_CBADM', 'QS_CS', 'QS_ES', 'QS_OS', 'QS_WS', 'REPADMIN',
'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN',
'SYSTEM', 'TRACESVR', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WK_TEST',
'WKUSER', 'WMSYS', 'XDB', 'EUL4_ZHS', 'EUL4_US', 'SYSADMIN',
'APPLSYS')
and segment_name in
(select table_name
from dba_tables a
where Owner not in
('ANONYMOUS', 'AURORA$', 'AURORA', 'CTXSYS', 'DBSNMP',
'DIP', 'DMSYS', 'DVF', 'DVSYS', 'EXFSYS', 'HR',
'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM',
'ODM_MTR', 'OE', 'OLAPSYS', 'ORACLE_OCM', 'ORAWSM',
'ORDPLUGINS', 'ORDSYS', 'OSE', 'OUTLN', 'PERFSTAT', 'PM', 'QS',
'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 'QS_ES',
'QS_OS', 'QS_WS', 'REPADMIN', 'SCOTT', 'SH',
'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM',
'TRACESVR', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WK_TEST',
'WKUSER', 'WMSYS', 'XDB', 'EUL4_ZHS', 'EUL4_US',
'SYSADMIN', 'APPLSYS')
and table_name not in
(select table_name from dba_part_tables))
Group by Owner, segment_name
having sum(bytes / 1024 / 1024) > 102
order by 3)
/
spool off
set feedback on
set heading on
!cat table_monitor_insert.temp |grep insert |grep -v SQL >table_monitor_insert.sql
@table_monitor_insert
commit
exit
EOF
sqlplus "/ as sysdba"</dev/null
update TB_MONITOR set WASTE_THRESHOLD=40
where CURRENT_SIZE > 2048;
commit;
update TB_MONITOR set WASTE_THRESHOLD=60
where CURRENT_SIZE between 512 and 2047;
commit;
update TB_MONITOR set WASTE_THRESHOLD=70
where CURRENT_SIZE <512 ;
commit;
update TB_MONITOR c set SGM_SPACE_MANAGEMENT=(select b.SEGMENT_SPACE_MANAGEMENT from dba_tables a,dba_tablespaces b
where a.tablespace_name=b.tablespace_name and a.owner=c.owner
and a.table_name=c.table_name);
commit;
select * from TB_MONITOR where SGM_SPACE_MANAGEMENT is null;
delete TB_MONITOR where SGM_SPACE_MANAGEMENT is null;
exit
EOF
sqlplus "/ as sysdba"</dev/null
set feedback on;
set pages 10000;
set line 250;
setecho on;
set heading on;
set trimspool on
col 属主 for a12;
col 表名 for a30;
col 表的大小阈值 for9
col 浪费率的大小阈值 for99exec perfstat.p_tb_monitor;
spool tb_need_to_rebuild.txt
select table_name 表名,
owner 属主,
SIZE_THRESHOLD 表的大小阈值,
WASTE_THRESHOLD 浪费率的大小阈值,
SGM_SPACE_MANAGEMENT 段管理方式,
CURRENT_SIZE 表当前大小MB,
CURRENT_WASTE 碎片率
from TB_MONITOR
WHERE SGM_SPACE_MANAGEMENT IS NOT NULL
AND ((SIZE_THRESHOLD IS NOT NULL AND SIZE_THRESHOLD < CURRENT_SIZE AND
WASTE_THRESHOLD < CURRENT_WASTE) or
(SIZE_THRESHOLD is null and WASTE_THRESHOLD < CURRENT_WASTE))
order by 碎片率 desc;
spool off
exit
EOF
#check if need to send alert mailcd$work_path/log
sqlplus -s"/ as sysdba"</dev/null
set feedback off
set pages 0set head off
setecho off
----to construct sql for the tables which need to rebuild;
spool rebuild.tmp
@${work_path}/autoshell/tb_monitor_construct.sql
spool off
!echo"REM ================================= ">rebuild.sql;
!echo"REM SQL for move tables:">>rebuild.sql;
!echo"REM ================================= ">>rebuild.sql;
!cat rebuild.tmp |grep 'alter table' |grep -v SQL >>rebuild.sql;
!echo"REM ================================= ">>rebuild.sql;
!echo"REM SQL for rebuild indexes:">>rebuild.sql;
!echo"REM ================================= ">>rebuild.sql;
!cat rebuild.tmp |grep 'alter index' |grep -v SQL >>rebuild.sql;
!echo"REM ================================= ">>rebuild.sql;
!echo"REM SQL for gather Table statistics:">>rebuild.sql;
!echo"REM ================================= ">>rebuild.sql;
!cat rebuild.tmp |grep dbms_stats.gather_table_stats |grep -v SQL |grep -v select >>rebuild.sql;
!echo"REM ================================= ">>rebuild.sql;
!echo"REM SQL for gather Index statistics:">>rebuild.sql;
!echo"REM ================================= ">>rebuild.sql;
!cat rebuild.tmp |grep dbms_stats.gather_index_stats |grep -v SQL |grep -v select >>rebuild.sql;
exit
EOF
cp rebuild.sql $work_path/report/rebuild_${date_yyyymmdd}.sql
cp tb_need_to_rebuild.txt $work_path/report/tb_need_to_rebuild_${date_yyyymmdd}.txt
autoshell文件夹下sql编写
autoshell文件夹下建立tb_monitor_construct.sql文件，内容如下：
----construct sql for move tables：set feedback off;set pages 100;set heading on;
col table_name for a30;
set trimspool on;set line 500
col script_for_gather_stat for a250
select'alter table ' || owner || '.' || table_name || ' move nologging parallel 8;'from perfstat.TB_MONITOR
WHERE SGM_SPACE_MANAGEMENT ISNOTNULLAND ((SIZE_THRESHOLD ISNOTNULLAND SIZE_THRESHOLD < CURRENT_SIZE AND
WASTE_THRESHOLD < CURRENT_WASTE) or
(SIZE_THRESHOLD isnulland WASTE_THRESHOLD < CURRENT_WASTE));select'alter table ' || owner || '.' || table_name || ' parallel 1 logging;'from perfstat.TB_MONITOR
WHERE SGM_SPACE_MANAGEMENT ISNOTNULLAND ((SIZE_THRESHOLD ISNOTNULLAND SIZE_THRESHOLD < CURRENT_SIZE AND
WASTE_THRESHOLD < CURRENT_WASTE) or
(SIZE_THRESHOLD isnulland WASTE_THRESHOLD < CURRENT_WASTE));------表回收碎片后，需要重建索引，以下是手工构建重建的语句------关于重建索引的并行度问题，如果在rebuild的时候指定并行度，那么，rebuild完成后最后将并行度改回来。-----否则，将倾向于走全表扫select'alter index ' || owner || '.' || index_name || ' rebuild online nologging parallel 8;'from dba_indexes a
where (a.table_name, a.owner) in
(SELECT table_name, owner
FROM TB_MONITOR
WHERE SGM_SPACE_MANAGEMENT ISNOTNULLAND ((SIZE_THRESHOLD ISNOTNULLAND
SIZE_THRESHOLD < CURRENT_SIZE AND
WASTE_THRESHOLD < CURRENT_WASTE) or
(SIZE_THRESHOLD isnulland WASTE_THRESHOLD < CURRENT_WASTE)));select'alter index ' || owner || '.' || index_name || ' parallel 1 logging;'from dba_indexes a
where (a.table_name, a.owner) in
(SELECT table_name, owner
FROM TB_MONITOR
WHERE SGM_SPACE_MANAGEMENT ISNOTNULLAND ((SIZE_THRESHOLD ISNOTNULLAND
SIZE_THRESHOLD < CURRENT_SIZE AND
WASTE_THRESHOLD < CURRENT_WASTE) or
(SIZE_THRESHOLD isnulland WASTE_THRESHOLD < CURRENT_WASTE)));--------表重建完毕后，需要收集一次统计信息--可以采用如下的语句一次性生成分析语句select'exec dbms_stats.gather_table_stats(OWNNAME=>' || '''' ||
upper(owner) || ''',tabname=>' || '''' || upper(table_name) ||
''',cascade=>TRUE,estimate_percent=>10,degree=>4);'as script_for_gather_stat
from (select owner,
segment_name table_name,
sum(bytes) / 1024 / 1024 size_m
from dba_segments
where (owner, segment_name) in
(SELECT owner, table_name
FROM perfstat.TB_MONITOR
WHERE SGM_SPACE_MANAGEMENT ISNOTNULLAND ((SIZE_THRESHOLD ISNOTNULLAND
SIZE_THRESHOLD < CURRENT_SIZE AND
WASTE_THRESHOLD < CURRENT_WASTE) or
(SIZE_THRESHOLD isnulland
WASTE_THRESHOLD < CURRENT_WASTE)))
groupby segment_name, owner
orderby size_m) a
;--------表统计完毕后，需要统计相关索引信息--可以采用如下的语句一次性生成分析语句select'exec dbms_stats.gather_index_stats(OWNNAME=>' || '''' ||
upper(owner) || ''',indname=>' || '''' || upper(index_name) ||
''',estimate_percent=>10,degree=>4);'as script_for_gather_inx_stat
from dba_indexes a
where (a.table_name, a.owner) in
(SELECT table_name, owner
FROM TB_MONITOR
WHERE SGM_SPACE_MANAGEMENT ISNOTNULLAND ((SIZE_THRESHOLD ISNOTNULLAND
SIZE_THRESHOLD < CURRENT_SIZE AND
WASTE_THRESHOLD < CURRENT_WASTE) or
(SIZE_THRESHOLD isnulland WASTE_THRESHOLD < CURRENT_WASTE)));
开始执行shell
在 autoshell下执行tb_monitor_auto.sh，如果没有报错，会在report文件下生产一个txt文件和一个sql文件，执行sql文件即可，可以写成一个脚本，在后头执行：
sqlplus -s “/ as sysdba”
@rebuild_20160126.sql
exit
EOF
').addClass('pre-numbering').hide();
$(this).addClass('has-numbering').parent().append($numbering);
for (i = 1; i <= lines; i++) {
$numbering.append($('
').text(i));
};
$numbering.fadeIn(1700);
});
});
以上就介绍了分享个ORACLE整理表碎片的脚本，包括了方面的内容，希望对Oracle有兴趣的朋友有所帮助。
本文网址链接：http://www.codes51.com/article/detail_304772_3.html
