Analyzing tables in a schema, part II

This tip is a follow-up to the one posted by Printaporn Sangdara on Apr. 6, 2004, regarding analysis of objects in a specific schema.

The original tip requires that a copy of the procedure be compiled in any schema that requires the functionality. The following script allows analysis of any tables within a user-specified schema.

The script is a more streamlined implementation removing the use of dynamic SQL, redundant code and references, and it introduces a more robust exception handling scheme. I tested this script against 8.1.7.0.0. I don't think there is anything that negates it's use with any other version, although, as it is using DBMS_STATS, that pretty much rules out anything prior to 8.0.

For descriptive details, see the original tip.

CREATE OR REPLACE
PROCEDURE analyze_any_schema (
    p_inOwner           IN      all_users.username%TYPE
)
IS

BEGIN


    FOR v_tabs  IN  (
                        SELECT
                            owner,
                            table_name
                        FROM
                            all_tables
                        WHERE
                            owner       =   p_inOwner   AND
                            temporary   <>  'Y'
                    )
    LOOP

        DBMS_OUTPUT.put_line (
            'EXEC  DBMS_STATS.gather_table_stats('''||v_tabs.owner||
            ''','''||v_tabs.table_name||''',NULL,1);'
        );

        BEGIN

 
DBMS_STATS.gather_table_stats(v_tabs.owner,v_tabs.table_name,NULL,1);
            DBMS_OUTPUT.put_line ('Analyzed
'||v_tabs.owner||'.'||table_name||'... ');

        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.put_line ('Exception on analysis of
'||v_tabs.table_name||'!');
                DBMS_OUTPUT.put_line (SUBSTR(SQLERRM,1,255));

        END;

    END LOOP;

END;
/
SHO ERR analyze_any_schema

Reader feedback

Stephen E. writes: In regards to the above, you need to add the following condition to the query against dba_tables.

(IOT_TYPE <> 'IOT_OVERFLOW')

This will avoid errors when using this routine.

Nina N. writes: A cursor name missing in the script prevents compilation:
Incorrect: DBMS_OUTPUT.put_line ('Analyzed '||v_tabs.owner||'.'||table_name||
Correct: DBMS_OUTPUT.put_line ('Analyzed '||v_tabs.owner||'.'||v_tabs.table_name||

Tom L. writes: I'm receiving these errors from this procedure:
SYSTEM:DBA1> show err Errors for PROCEDURE ANALYZE_ANY_SCHEMA: LINE/COL ERROR
-------- -----------------------------------------------------------------
24/13 PL/SQL: Statement ignored 25/23 PLS-00201: identifier 'TABLE_NAME' must be declared Database is 8.1.7.2

Patrick B. writes: I have tried this code on Oracle 8.1.6 and 9.2.0.3 both on AIX, and it consistently fails with the following message:

EXEC DBMS_STATS.gather_table_stats('PAT','SORT_HISTORY',NULL,1); Exception on analysis of SORT_HISTORY! ORA-20000: Object SORT_HISTORY does not exist or insufficient privileges to analyze EXEC DBMS_STATS.gather_table_stats('PAT','SORT_SEG_HISTORY',NULL,1); Exception on analysis of SORT_SEG_HISTORY! ORA-20000: Object SORT_SEG_HISTORY does not exist or insufficient privileges to analyze PL/SQL procedure successfully completed.

I have converted the code to use dynamic SQL ala the original tip and this works fine so there clearly is no issue with the permissions. Anyone have any idea on what could be wrong?

Author's response

On the DBMS_OUTPUT issue, I didn't mention to perform a SET SERVEROUTPUT ON SIZE nnnnnnnn. This is something basic that slipped my mind.

I tested the solution through PL/SQL Developer, which I have configured to automatically set buffer size to the MAX 1000000 bytes. By default, the buffer is only 2K and can very easily be maxed if a schema contains a lot of tables. The user can simply remove the DBMS_OUTPUT calls -- they are in effect redundant, just providing feedback on what the process has run.