analyze_schema.sql
rem ************************************************************** rem * rem * analyze_schema.sql rem * rem * DESCRIPTION rem * This script generates the analyze command for all tables rem * in the instance. The sql statements are spooled to rem * analyze_run.lst, which is then executed to do the analyze. rem * rem * HISTORY rem * rem * 02/05/97 S. Patterson Created rem * 07/09/98 J. Saricos Estimating stats only. rem * Owner no longer needed. rem * 09/30/98 J. Saricos Change to use package rem * dbms_utlity.analyze_schema. rem * 10/08/98 C. Ringler Changed spelling of 'utlity' rem * to 'utility' rem * 10/12/98 J. Saricos Added join to DBA_USERS rem ************************************************************** set heading off pause off pages 0 embedded on lines 200 col fnd_owner new_val fnd_owner noprint col fnd_table new_val fnd_table noprint rem rem Get owner of FND tables rem select owner fnd_owner, table_name fnd_table from sys.dba_tables where table_name = 'FND_ORACLE_USERID' and owner in ('FND','APPLSYS'); rem rem Generate ANALYZE commands rem spool /work/analz/&1/analyze_run.lst select 'execute dbms_utility.analyze_schema('''||oracle_username|| ''',''ESTIMATE'')' from &fnd_owner..&fnd_table, dba_users where oracle_username=username; spool off exit