#####多個服務器容量統計腳本： declare v_sql varchar2(4000); begin for rec in (select * from suxing.conf11g_dblink_tab) loop v_sql :='insert into suxing.space_gather11G_tab select * from ( with I as (select instance_name from v$instance@'||rec.db_link||'), A as (select round(sum(bytes)/1024/1024/1024,2) aa from dba_data_files@'||rec.db_link||'), B as (select round(sum(bytes)/1024/1024/1024,2) bb from dba_free_space@'||rec.db_link||'), C as (select round(sum(BYTES / 1024 / 1024 / 1024), 2) cc from v$datafile@'||rec.db_link||' a, v$tablespace@'||rec.db_link||' b where a.TS# = b.TS# and a.CREATION_TIME < to_date(''2017-07-15 10:00:00'', ''YYYY-MM-DD HH24:MI:SS'')), D as (select round(sum(BYTES / 1024 / 1024 / 1024), 2) dd from v$datafile@'||rec.db_link||' a, v$tablespace@'||rec.db_link||' b where a.TS# = b.TS# and a.CREATION_TIME < to_date(''2017-04-15 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')), E as (select round(sum(BYTES / 1024 / 1024 / 1024), 2) ee from v\$datafile@'||rec.db_link||' a, v\$tablespace@'||rec.db_link||' b where a.TS# = b.TS# and a.CREATION_TIME < to_date(''2017-06-15 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')), G as (select GROUP_NUMBER,NAME,TOTAL_MB/1024 totalGB,FREE_MB/1024 freeGB from v$asm_diskgroup@'||rec.db_link||' where name like ''DATA%'') select I.instance_name,A.aa Total_G,A.aa-B.bb Used_G,B.bb Free_G,G.totalGB,G.freeGB,(C.cc - D.dd)/3 AVRG3,C.cc - E.ee,sysdate,to_char(sysdate,''yymmdd'') from I,A,B,C,D,E,G )'; --dbms_output.put_line(v_sql); execute immediate v_sql; commit; end loop; end;
#####多個服務器備份檢查結果采集腳本： declare v_sql varchar2(4000); begin for rec in (select * from suxing.CONF_BACKUPCHECK_DBLINK_TAB) loop v_sql :='insert into suxing.db_backup_check_alltab select * from ( with I as (select instance_name from v$instance@'||rec.db_link||'), BC as ( select start_time, t.INPUT_TYPE, status from v$rman_backup_job_details@'||rec.db_link||' t where t.start_time in (select max(start_time) from v$rman_backup_job_details@'||rec.db_link||') and t.INPUT_TYPE = ''ARCHIVELOG'' group by t.start_time, t.INPUT_TYPE, t.STATUS union all select start_time, t.INPUT_TYPE, status from v$rman_backup_job_details@'||rec.db_link||' t where t.start_time in (select max(start_time) from v$rman_backup_job_details@'||rec.db_link||' t where t.INPUT_TYPE = ''DB INCR'') group by t.start_time, t.INPUT_TYPE, t.STATUS) select I.instance_name,BC.*,to_char(sysdate,''mmddhh34'') from I,BC)'; --dbms_output.put_line(v_sql); execute immediate v_sql; commit; end loop; end;
