SQL Script Gather Table Statistics

Updated: 26-Sep-2002
Version: 1.1

Description

Dynamically analyzes all tables for an owner

Parameters

1 - Table Owner

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set echo on
set feed on
set timing on
set verify off
set serveroutput on

ACCEPT own PROMPT 'Enter value for table owner : '

DECLARE

sql_stmt    VARCHAR2(1024);

BEGIN

FOR tab_rec IN (SELECT owner,table_name 
               FROM all_tables WHERE owner = UPPER('&own')  
               )  LOOP

sql_stmt := 'BEGIN dbms_stats.gather_table_stats  (ownname  => :1 , tabname => :2 , partname=> null, estimate_percent => 20, degree => 5 , cascade => true); END;'  ;

dbms_output.put_line (sql_stmt);

EXECUTE IMMEDIATE sql_stmt USING tab_rec.owner, tab_rec.table_name ;

END LOOP;

END;
/

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links