定時收集gc事件的指令碼

create or replace procedure shsnc.gc_event(TBS_NAME VARCHAR2) is
  T_TABLE VARCHAR2(100) := 'GC_EVENT_TAB';
  v_sql01 VARCHAR2(2000);
  v_sql02 VARCHAR2(2000);
  v_sql03 VARCHAR2(2000);
  v_sql04 VARCHAR2(2000);
  v_sql05 VARCHAR2(2000);
  T_COUNT NUMBER;
begin
  SELECT COUNT(1) INTO T_COUNT FROM DBA_TABLES WHERE TABLE_NAME = T_TABLE;
  IF T_COUNT > 0 THEN
    v_sql01 := 'insert into shsnc.' || T_TABLE || ' select SYS_CONTEXT(''USERENV'', ''INSTANCE'') INSTANCE,s.sid,
       s.sql_id,
       s.username,
       s.module,
       s.machine,
       s.program,
       s.status,
       s.state,
       t.FORCE_MATCHING_SIGNATURE,
       t.EXACT_MATCHING_SIGNATURE,
       t.sql_fulltext 
  from v$session s, v$sql t
 where s.event like ''gc%''
   and s.wait_class = ''Cluster'' 
   and s.sql_id=t.sql_id';
    EXECUTE IMMEDIATE v_sql01;
    commit;
    v_sql04 := 'delete shsnc.' || T_TABLE ||
               ' a WHERE a.rowid !=(select max(rowid) from shsnc.' ||
               T_TABLE || ' b where a.sql_id=b.sql_id)';
    execute immediate v_sql04;
    commit;
    v_sql05 := 'delete shsnc.' || T_TABLE ||
               ' a WHERE a.FORCE_MATCHING_SIGNATURE !=EXACT_MATCHING_SIGNATURE';
    execute immediate v_sql05;
    commit;
  else
    v_sql02 := 'create table shsnc.' || T_TABLE ||
               '(instance number,sid number,sql_id VARCHAR2(13),username VARCHAR2(30),module VARCHAR2(64),machine VARCHAR2(64),program VARCHAR2(48),status VARCHAR2(8),state VARCHAR2(19),FORCE_MATCHING_SIGNATURE number,EXACT_MATCHING_SIGNATURE number,sql_fulltext CLOB) tablespace ' ||
               TBS_NAME;
    execute immediate v_sql02;
    v_sql03 := 'insert into shsnc.' || T_TABLE || ' select SYS_CONTEXT(''USERENV'', ''INSTANCE'') INSTANCE,s.sid,
       s.sql_id,
       s.username,
       s.module,
       s.machine,
       s.program,
       s.status,
       s.state,
       t.FORCE_MATCHING_SIGNATURE,
       t.EXACT_MATCHING_SIGNATURE,
       t.sql_fulltext 
  from v$session s, v$sql t
 where s.event like ''gc%''
   and s.wait_class = ''Cluster'' 
   and s.sql_id=t.sql_id';
    EXECUTE IMMEDIATE v_sql03;
    COMMIT;
    v_sql04 := 'delete shsnc.' || T_TABLE ||
               ' a WHERE a.rowid !=(select max(rowid) from shsnc.' ||
               T_TABLE || ' b where a.sql_id=b.sql_id)';
    execute immediate v_sql04;
    commit;
    v_sql05 := 'delete shsnc.' || T_TABLE ||
               ' a WHERE a.FORCE_MATCHING_SIGNATURE !=EXACT_MATCHING_SIGNATURE';
    execute immediate v_sql05;
    commit;
  END IF;
END;
/