oracle 碎片查询与处理,oracle清理相关日志，查询归档碎片等情况

cp /var/log/messages /var/log/messagesdate +%Y%m%d%H%M&&>/var/log/messages

echo “cat find /oracle -name 'alert*.log'>>find /oracle -name 'alert*.log'``date +%Y%m%d%H%M&&>find /oracle -name 'alert*.log'”

echo “cat find /grid -name 'crsd.log'>>find /grid -name 'crsd.log'``date +%Y%m%d%H%M&&>find /grid -name 'crsd.log'”

echo “cat find /grid -name 'ocssd.log'>>find /grid -name 'ocssd.log'``date +%Y%m%d%H%M&&>find /grid -name 'ocssd.log'”

find . -name ‘*txt’ -atime +5|xargs rm -rf

set line200

col path for a40

select name,path from vasm_disk where name like '%DGDATA%';

select count(name) from vasm_disk where name like ‘%DGDATA%’;

select name,path from vasm_disk where name like '%DGSYS%';

select count(name) from vasm_disk where name like ‘%DGSYS%’;

select name,path from vasm_disk where name like '%OCR_VOTE%';

select count(name) from vasm_disk where name like ‘%OCR_VOTE%’;

日志切换和生成速度、大小：

SQL>select group#,bytes from vlog;SQL>selecttochar(firsttime,′yyyy−mm−ddhh24′),count(∗)fromvlog;

SQL> select to_char(first_time,'yyyy-mm-dd hh24'),count(*) from vlog;SQL>selecttoc​har(firstt​ime,′yyyy−mm−ddhh24′),count(∗)fromvlog_history where thread#=2 group by to_char(first_time,‘yyyy-mm-dd hh24’) order by to_char(first_time,‘yyyy-mm-dd hh24’);

SQL> SELECT RUN_ID,NAME,CHECK_NAME,RUN_MODE,SRC_INCIDENT FROM V$HM_RUN; #了解曾经进行的各种Health Monitor检查历史情况：

RUN_ID NAME CHECK_NAME RUN_MODE SRC_INCIDENT

61 HM_RUN_61 DB Structure Integrity Check REACTIVE 0

41 HM_RUN_41 DB Structure Integrity Check REACTIVE 0

SQL> SELECT TYPE,DESCRIPTION FROM V$HM_FINDING WHERE RUN_ID=41; #了解某次检查中发现的问题

no rows selected

SQL> SELECT TYPE,DESCRIPTION FROM V$HM_FINDING WHERE RUN_ID = 61;

no rows selected

5.表空间碎片评估方法

以下语句将按表空间计算FSFI(free space fragmentation index)值,如果FSFI值<30%,则该表空间的碎片较多：

select a.tablespace_name,sqrt(max(a.blocks)/sum(a.blocks)*(100/sqrt(count(a.blocks)))) FSFI

from dba_free_space a,dba_tablespaces b

where a.tablespace_name=b.tablespace_name and b.contents not in (‘TEMPORARY’,‘UNDO’)

group by a.tablespace_name order by FSFI;

6.表碎片的评估方法

显示碎片率最高的前100个表：

col frag format 999999.99

col owner format a30

col table_name format a30

select * from (

select a.owner,a.table_name,a.num_rows,a.avg_row_lena.num_rows,sum(b.bytes),(a.avg_row_lena.num_rows)/sum(b.bytes) frag

from dba_tables a,dba_segments b

where a.table_name=b.segment_name and a.owner=b.owner and a.owner not in (‘SYS’,‘SYSTEM’,‘OUTLN’,‘DMSYS’,‘TSMSYS’,‘DBSNMP’,‘WMSYS’,’

EXFSYS’,‘CTXSYS’,‘XDB’,‘OLAPSYS’,‘ORDSYS’,‘MDSYS’,‘SYSMAN’)

group by a.owner,a.table_name,a.avg_row_len,a.num_rows

having a.avg_row_len*a.num_rows/sum(b.bytes)<0.7

order by sum(b.bytes) desc)

where rownum<=100;

7.索引碎片的评估方法

下列语句显示索引高度Blevel>=3,并且索引大小超过100MB的索引：

col tablespace_name format a20

col owner format a10

col index_name format a30

select id.tablespace_name,id.owner,id.index_name,id.blevel,sum(sg.bytes)/1024/1024,sg.blocks,sg.extents

from dba_indexes id,dba_segments sg

where id.owner=sg.owner and id.index_name=sg.segment_name and id.tablespace_name=sg.tablespace_name

and id.owner not in (‘SYS’,‘SYSTEM’,‘USER’,‘DBSNMP’,‘ORDSYS’,‘OUTLN’) and sg.extents>100 and id.blevel>=3

group by id.tablespace_name,id.owner,id.index_name,id.blevel,sg.blocks,sg.extents

having sum(sg.bytes)/1024/1024>100;

下列语句中PCT_DELETED的含义为索引被删除项与索引项总数的所占比例，如果PCT_DELETED>=20%,则说明该索引碎片严

重，由于analyze语句会对被分析索引产生锁，即在生产系统运行时会产生一定影响因此优先考虑按blevel分析方法：

analyze index validate structure;

select del_lf_rows*100/decode(lf_rows,0,1,lf_rows) pct_deleted from index_stats;

8.查看automatic segment advisor分析结果

查看automatic segment advisor的finding结果

select af.task_name,ao.attr2 segname,ao.attr3 partition,ao.type,af.message

from dba_advisor_findings af,dba_advisor_objects ao

where ao.task_id=af.task_id

and ao.object_id=af.object_id;

只查询可以进行shrink操作的对象

select f.impact,o.type,o.attr1,o.attr2,f.message,f.more_info

from dba_advisor_findings f,dba_advisor_objects o

where f.object_id=o.object_id and f.task_name=o.task_name and f.message like ‘%shrink%’

order by f.impact desc;

查看automatic segment advisor的recommendations结果

告诉客户那些表、索引存在碎片、预估回收多少空间和推荐的空间回收语句，结合上面手工产生的结果进行综合评估制定策略

select tablespace_name,segment_name,segment_type,partition_name,recommendations,c1

from table(dbms_space.asa_recommendations(‘FALSE’,‘FALSE’,‘FALSE’));