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 22.214.171.124.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
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 126.96.36.199
Patrick B. writes: I have tried this code on Oracle 8.1.6 and 188.8.131.52 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?
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.