CRM On Demand - Administration (MOSC)

MOSC Banner

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;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center