Script to generate Statistics script in Oracle
The script will generate the GATHER_TABLE_STATS script for all the tables that don't have statistics up to data.
You can do it for the entire database instance or per user/owner/schema.
set serveroutput on
set serveroutput on size 1000000
declare
sql_string varchar2(2000);
CURSOR OBJ IS
select owner,table_name
from dba_tables
where last_analyzed is null
--and OWNER='&owner'
;
begin
for REC1 IN OBJ LOOP
DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_TABLE_STATS ('''||REC1.owner||''','''||REC1.table_name||''');');
END LOOP;
END;
/
