SET term off ver off echo off feed off trims on; 
SET term on; 
PROMPT 
PROMPT Generating COE staging tables... 
PROMPT 
SET term off; 
 
VARIABLE V_DEGREE VARCHAR2; 
BEGIN 
    SELECT TO_CHAR(MIN(TO_NUMBER(VALUE))) 
      INTO :V_DEGREE 
      FROM V$PARAMETER 
     WHERE NAME IN ('parallel_max_servers','cpu_count'); 
END; 
/ 
 
DROP   TABLE COE_SCHEMAS; 
CREATE TABLE COE_SCHEMAS 
    (OWNER VARCHAR2(30)) NOLOGGING CACHE; 
DROP   TABLE COE_TABLES; 
CREATE TABLE COE_TABLES 
    (OWNER VARCHAR2(30),TABLE_NAME VARCHAR2(30), 
     NUM_ROWS NUMBER, LAST_ANALYZED DATE, 
     PERCENT NUMBER, PARTITIONED VARCHAR2(3)) NOLOGGING CACHE; 
 
INSERT INTO COE_SCHEMAS 
SELECT DISTINCT 
       FOU.ORACLE_USERNAME 
FROM 
       FND_ORACLE_USERID         FOU, 
       FND_PRODUCT_INSTALLATIONS FPI 
WHERE 
       FPI.ORACLE_ID        = FOU.ORACLE_ID 
AND    FOU.ORACLE_USERNAME != 'SYS'; 
 
INSERT INTO COE_TABLES 
SELECT AT.OWNER, 
       AT.TABLE_NAME, 
       AT.NUM_ROWS, 
       AT.LAST_ANALYZED, 
       DECODE(NVL(AT.NUM_ROWS,0),0,10,                  -- NULL or 0 (10%) 
       DECODE(SIGN(AT.NUM_ROWS-    10000),-1,99.999999, -- 1-10K (100%) 
       DECODE(SIGN(AT.NUM_ROWS-   100000),-1,16,        -- 10K-100K (16%) 
       DECODE(SIGN(AT.NUM_ROWS-  1000000),-1,12,        -- 100K-1M (12%) 
       DECODE(SIGN(AT.NUM_ROWS- 10000000),-1,8,         -- 1M-10M (8%) 
       DECODE(SIGN(AT.NUM_ROWS-100000000),-1,4,2)))))), -- 10M-100M (4%) 
       AT.PARTITIONED                                   -- >100M (2%) 
FROM 
       ALL_TABLES  AT, 
       COE_SCHEMAS CS 
WHERE 
       CS.OWNER                  = AT.OWNER 
AND   (AT.IOT_TYPE              IS NULL 
OR     AT.IOT_TYPE              <> 'IOT_OVERFLOW') 
AND    AT.TEMPORARY              = 'N' 
AND   ((NVL(AT.NUM_ROWS,0)       =         0 
AND     AT.LAST_ANALYZED         IS NULL) 
OR     (NVL(AT.NUM_ROWS,0)      >=         0 
AND     NVL(AT.NUM_ROWS,0)       <     10000 
AND     TRUNC(AT.LAST_ANALYZED) <= TRUNC(SYSDATE) - 21) 
OR     (AT.NUM_ROWS             >=     10000 
AND     AT.NUM_ROWS              <    100000 
AND     TRUNC(AT.LAST_ANALYZED) <= TRUNC(SYSDATE) - 28) 
OR     (AT.NUM_ROWS             >=    100000 
AND     AT.NUM_ROWS              <   1000000 
AND     TRUNC(AT.LAST_ANALYZED) <= TRUNC(SYSDATE) - 35) 
OR     (AT.NUM_ROWS             >=   1000000 
AND     AT.NUM_ROWS              <  10000000 
AND     TRUNC(AT.LAST_ANALYZED) <= TRUNC(SYSDATE) - 42) 
OR     (AT.NUM_ROWS             >=  10000000 
AND     AT.NUM_ROWS              < 100000000 
AND     TRUNC(AT.LAST_ANALYZED) <= TRUNC(SYSDATE) - 49) 
OR     (AT.NUM_ROWS             >= 100000000 
AND     TRUNC(AT.LAST_ANALYZED) <= TRUNC(SYSDATE) - 56)) 
AND    NOT EXISTS (SELECT NULL 
                   FROM FND_EXCLUDE_TABLE_STATS FETS 
                   WHERE FETS.TABLE_NAME = AT.TABLE_NAME); 
 
SET term off ver off echo off feed off trims on; 
SET head off pages 0 lin 200; 
SPOOL coe_fix_stats.sql; 
COLUMN DUMMY1 FORMAT A30 NOPRINT; 
SELECT OWNER||TABLE_NAME||'1' DUMMY1, 
    SUBSTR('EXEC DBMS_STATS.DELETE_TABLE_STATS('|| 
    'ownname => '''||OWNER||''', '|| 
    'tabname => '''||TABLE_NAME||''');',1,200) 
FROM 
    COE_TABLES 
UNION ALL 
SELECT OWNER||TABLE_NAME||'2' DUMMY1, -- COMPUTES: EMPTY_BLOCKS, 
    SUBSTR('ANALYZE TABLE '||         -- AVG_SPACE, AVG_ROW_LEN 
    OWNER||'.'||TABLE_NAME|| 
    ' ESTIMATE STATISTICS SAMPLE 1 ROWS;',1,200) 
FROM 
    COE_TABLES 
UNION ALL 
SELECT OWNER||TABLE_NAME||'3', 
    SUBSTR('EXEC FND_STATS.GATHER_TABLE_STATS('|| 
    'ownname=>'''||OWNER||''','|| 
    'tabname=>'''||TABLE_NAME||''','|| 
    'percent=>'||TO_CHAR(PERCENT)||','|| 
    'degree=>'|| 
    DECODE(SIGN(NVL(NUM_ROWS,0)-10000),-1,'1',0,'1',:V_DEGREE)||','|| 
    'granularity=>'||DECODE(PARTITIONED,'YES','''PARTITION''','''DEFAULT''')|| 
    ');',1,200) 
FROM 
    COE_TABLES 
ORDER BY 1; 
SPOOL off; 
 
COLUMN OWNER_TABLE FORMAT A40 HEADING 'Owner.Table'; 
COLUMN PARTITIONED FORMAT A6 HEADING 'Parti-|tioned'; 
COLUMN NUM_ROWS FORMAT B999,999,999 HEADING 'Former|Num Rows'; 
COLUMN DAYS FORMAT B9,999,999.9 HEADING 'Days since|last analyze'; 
COLUMN PERCENT FORMAT B99,999 HEADING 'Sample|Percent'; 
COLUMN SAMPLE_ROWS FORMAT B999,999,999 HEADING 'Sample|Rows'; 
COLUMN MODULE FORMAT A10 HEADING 'Module'; 
COLUMN MODULE_TOTAL FORMAT 99,999,999 - 
       HEADING 'Tables|Requiring|Stats|Gathering'; 
COLUMN LAST_ANALYZED_DATE FORMAT A14 HEADING 'Last Analyzed'; 
COLUMN TABLES FORMAT 999,999 HEADING 'Tables'; 
 
SET head on pages 1000 feed on; 
SPOOL coe_stats.txt; 
SELECT 
    OWNER||'.'||TABLE_NAME 
        OWNER_TABLE, 
    PARTITIONED, 
    NUM_ROWS, 
    SYSDATE - LAST_ANALYZED 
        DAYS, 
    ROUND(PERCENT) 
        PERCENT, 
    ROUND(PERCENT * NUM_ROWS / 100) 
        SAMPLE_ROWS 
FROM 
    COE_TABLES 
ORDER BY OWNER||'.'||TABLE_NAME; 
 
SET term on; 
 
SELECT NVL(TO_CHAR(LAST_ANALYZED,'YYYY-MM-DD'),'Never Analyzed') 
       LAST_ANALYZED_DATE, 
       COUNT(*) TABLES 
  FROM COE_TABLES 
 GROUP BY NVL(TO_CHAR(LAST_ANALYZED,'YYYY-MM-DD'),'Never Analyzed'); 
 
SELECT 
    SUBSTR(OWNER,1,10) MODULE, 
    COUNT(*)           MODULE_TOTAL 
FROM 
    COE_TABLES 
GROUP BY 
    SUBSTR(OWNER,1,10); 
 
SELECT 
    COUNT(*)           MODULE_TOTAL 
FROM 
    COE_TABLES; 
 
SET pages 0 echo on; 
START coe_fix_stats.sql; 
SPOOL off; 
SET ver on trims off pages 24 lin 80;