script to check database statistics on tables
Hi all
im having issues with this script to check table statistics
SET SERVEROUTPUT ON;
DECLARE
OWNER VARCHAR2(20)
OWNER not in('SYS','SYSTEM','DBSNMP','CTXSYS','MDSYS','XDB','SYSMAN') ;
CURSOR c IS
SELECT table_name
FROM dba_tables
WHERE num_rows = 0 and table_name not like 'MLOG%';
v_table_name user_dba.table_name%TYPE;
BEGIN
OPEN c;
LOOP
FETCH c INTO v_table_name;
DBMS_OUTPUT.PUT_LINE('Found table: ' || v_table_name || ' with 0 rows. Rerunning stats');
EXIT WHEN c%NOTFOUND;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => OWNER, tabname => v_table_name );
END;
END LOOP;
CLOSE c;