How Gather stats - Dynamic Query in Oracle

 

How to generate the Dynamic Query for Gather stats for Owner/Schema in Oracle :-

set server output off ;

set heading on ;

set pages 1000 set lines 300;

spool gather_stale_table_stats.sql

SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS (''' || OWNER || ''', ''' || TABLE_NAME || ''', CASCADE => TRUE, ' || 'ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ''FOR ALL INDEXED COLUMNS SIZE 254'', ' || 'DEGREE => DBMS_STATS.DEFAULT_DEGREE);' FROM DBA_TABLES WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM DBA_TAB_STATISTICS WHERE OWNER = 'SAM');

SQL> SELECT
  'EXEC DBMS_STATS.GATHER_TABLE_STATS (''' || OWNER || ''', ''' || TABLE_NAME || ''', CASCADE => TRUE, ' ||
  'ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ''FOR ALL INDEXED COLUMNS SIZE 254'', ' ||
  'DEGREE => DBMS_STATS.DEFAULT_DEGREE);'
FROM DBA_TABLES
WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM DBA_TAB_STATISTICS WHERE OWNER = 'SAM');  

'EXECDBMS_STATS.GATHER_TABLE_STATS('''||OWNER||''','''||TABLE_NAME||''',CASCADE=>TRUE,'||'ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>''FORALLINDEXEDCOLUMNSSIZE254'','||'DEGREE=>DBMS_ST
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SAM', 'TEST', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254', DEGREE => DBMS_STATS.DEFAULT_DEGREE);

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SAM', 'ORDERS', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254', DEGREE => DBMS_STATS.DEFAULT_DEGREE);

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SAM', 'CUSTOMERS', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254', DEGREE => DBMS_STATS.DEFAULT_DEGREE);

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SAM', 'SUPPLIER', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254', DEGREE => DBMS_STATS.DEFAULT_DEGREE);

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SAM', 'PRODUCT', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254', DEGREE => DBMS_STATS.DEFAULT_DEGREE);

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SAM', 'EMP', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254', DEGREE => DBMS_STATS.DEFAULT_DEGREE);

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SAM', 'SALES', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254', DEGREE => DBMS_STATS.DEFAULT_DEGREE);

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SAM', 'EMPLOYEES', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254', DEGREE => DBMS_STATS.DEFAULT_DEGREE);