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