SLT Monitoring.sql

Source: DMF/PuC/AIF/SLT Monitoring.sql

DECLARE
 
 l_count number;
 
  C_TAB     SYS_REFCURSOR;
  L_TAB_SQL varchar2(2000);
 
  TYPE tabs IS RECORD(
    lines_count     NUMBER);
 
  l_tab tabs;
bEGIN
 
  
  dbms_output.put_line('------------------------------------------------------------------------');
  dbms_output.put_line('HyperCare SLT table');
  dbms_output.put_line('Script: SLT Monitoring.sql');
  dbms_output.put_line('------------------------------------------------------------------------');
  dbms_output.put_line(RPAD('Owner Name', 30) || RPAD('Table Name', 30) || ' N of Rows');
  
  FOR c IN (select owner, table_name from all_tables where owner in ('SAP_SLT_PROD_PV1','SAP_SLT_PROD_PR1','SAP_SLT_PROD_P01','SAP_SLT_TEST_QV1','SAP_SLT_TEST_QR1') ORDER BY OWNER, TABLE_NAME) LOOP
  
    L_TAB_SQL := 'select count(1) from ' || c.owner || '.' || c.table_name;
  
    OPEN C_TAB FOR L_TAB_SQL;
    loop
      FETCH C_TAB
        INTO l_TAB;
    
      EXIT WHEN C_TAB%NOTFOUND;
    
      --if l_TAB.lines_count > 0 then
        dbms_output.put_line(RPAD(c.owner,30) || RPAD(C.table_name,30) || ' ' || l_tab.lines_count);
      --end if;
    end loop;
    
    CLOSE C_TAB;
  end loop;    
 
  dbms_output.put_line('');
  
END;
/