0.Oracle 监视脚本(的83)++.ppt

Oracle OCM 课程实践 ;IOUG-A Master Class;MonitoringThe dba_free_space poltergeist;set pages 100 col ts_name form a20 head Tablespace col pieces form 9990 head Pcs col ts_size form 999,990 head SizeMb col largestpc form 999,990 head LrgMB col totalfree form 999,990 head FreeMb col pct_free form 990 head %Free col whatsused form 999,990 head Used col pct_used form 990 head %Used col problem head Prob?? spool umcdbp1 . . . spool off;select q2.other_tname ts_name, pieces, ts_size ts_size, nvl(largest_chunk,0) largestpc, nvl(total_free,0) totalfree, nvl(round((total_free/ts_size)*100,2),0) pct_free, ts_size-total_free whatsused, nvl(100-round((total_free/ts_size)*100,2),100) pct_used, decode(nvl(100-round((total_free/ts_size)*100,0),100), 85,+,86,+,87,+,88,+,89,++,90,++,91,++, 92,++,93,++,94,+++,95,+++,96,+++,97,++++, 98,+++++,99,+++++,100,+++++,) problem from (select dfs.tablespace_name,count(*) pieces, round(max(dfs.bytes)/1024/1024,2) largest_chunk, round(sum(dfs.bytes)/1024/1024,2) total_free from dba_free_space dfs group by tablespace_name) q1, (select tablespace_name other_tname, round(sum(ddf2.bytes)/1024/1024,2) ts_size from dba_data_files ddf2 group by tablespace_name) q2 where q2.other_tname = q1.tablespace_name(+) order by nvl(100-round((total_free/ts_size)*100,0),100) desc;;MonitoringUnable to extend – available extents;MonitoringUnable to extend – within 5;COL trname for a30 select tsh.tablespace_name, ..||substr(tf.name,length(name)-23) trname, tf.bytes,tsh.bytes_free from v$tempfile tf, v$temp_space_header tsh where tf.file# = tsh.file_id; TS Name File BYTES BYTES_FREE LOC_TEMP ../od01/loc_temp01.dbf 2,098,200,576 1,887,436,800 LOC_TEMP ../od02/loc_temp02.dbf 2,098,200,576 1,258,291,200 LOC_TEMP ../od04/loc_temp03.dbf 2,098,200,576 1,887,4