DECLARE
M_ERRBUF VARCHAR2 (500) := NULL;
M_RETCODE VARCHAR2 (10) := NULL;
M_DATE_FMT VARCHAR2 (50) := '''' || 'DD-MON-YYYY HH24:MI:SS' || '''';
--
CURSOR C1
IS
SELECT OWNER, OBJECT_NAME
FROM ALL_OBJECTS
WHERE 1 = 1
AND OBJECT_TYPE = 'TABLE'
AND OBJECT_NAME IN ('PER_ALL_PEOPLE_F',
'PER_ALL_ASSIGNMENTS_F'
)
ORDER BY OWNER, OBJECT_NAME;
BEGIN
DBMS_OUTPUT.
PUT_LINE ('SOP = ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=' || M_DATE_FMT;
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=APPS';
--
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE ('OBJ = ' || TRIM (I.OWNER || '.' || I.OBJECT_NAME));
DBMS_OUTPUT.PUT_LINE ('1-B' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
--
FND_STATS.VERIFY_STATS (SCHEMANAME => I.OWNER,
TABLELIST => I.OBJECT_NAME,
DAYS_OLD => NULL,
COLUMN_STAT => FALSE);
--
FND_STATS.GATHER_TABLE_STATS (OWNNAME => I.OWNER,
TABNAME => I.OBJECT_NAME,
PERCENT => 100,
DEGREE => NULL,
PARTNAME => NULL,
BACKUP_FLAG => 'NOBACKUP',
CASCADE => TRUE,
GRANULARITY => 'DEFAULT',
HMODE => 'LASTRUN',
INVALIDATE => 'Y');
DBMS_OUTPUT.PUT_LINE ('1-E' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
--
DBMS_OUTPUT.PUT_LINE ('2-B' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
FND_STATS.VERIFY_STATS (SCHEMANAME => I.OWNER,
TABLELIST => I.OBJECT_NAME,
DAYS_OLD => NULL,
COLUMN_STAT => FALSE);
DBMS_OUTPUT.PUT_LINE ('2-E' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
END LOOP;
COMMIT;
--
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=APPS';
--
DBMS_OUTPUT.
PUT_LINE (TO_CHAR (M_RETCODE) || ': ' || SUBSTR (M_ERRBUF, 1, 220));
DBMS_OUTPUT.
PUT_LINE ('EOP = ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.
PUT_LINE (TO_CHAR (SQLCODE) || ': ' || SUBSTR (SQLERRM, 1, 220));
END;
M_ERRBUF VARCHAR2 (500) := NULL;
M_RETCODE VARCHAR2 (10) := NULL;
M_DATE_FMT VARCHAR2 (50) := '''' || 'DD-MON-YYYY HH24:MI:SS' || '''';
--
CURSOR C1
IS
SELECT OWNER, OBJECT_NAME
FROM ALL_OBJECTS
WHERE 1 = 1
AND OBJECT_TYPE = 'TABLE'
AND OBJECT_NAME IN ('PER_ALL_PEOPLE_F',
'PER_ALL_ASSIGNMENTS_F'
)
ORDER BY OWNER, OBJECT_NAME;
BEGIN
DBMS_OUTPUT.
PUT_LINE ('SOP = ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=' || M_DATE_FMT;
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=APPS';
--
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE ('OBJ = ' || TRIM (I.OWNER || '.' || I.OBJECT_NAME));
DBMS_OUTPUT.PUT_LINE ('1-B' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
--
FND_STATS.VERIFY_STATS (SCHEMANAME => I.OWNER,
TABLELIST => I.OBJECT_NAME,
DAYS_OLD => NULL,
COLUMN_STAT => FALSE);
--
FND_STATS.GATHER_TABLE_STATS (OWNNAME => I.OWNER,
TABNAME => I.OBJECT_NAME,
PERCENT => 100,
DEGREE => NULL,
PARTNAME => NULL,
BACKUP_FLAG => 'NOBACKUP',
CASCADE => TRUE,
GRANULARITY => 'DEFAULT',
HMODE => 'LASTRUN',
INVALIDATE => 'Y');
DBMS_OUTPUT.PUT_LINE ('1-E' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
--
DBMS_OUTPUT.PUT_LINE ('2-B' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
FND_STATS.VERIFY_STATS (SCHEMANAME => I.OWNER,
TABLELIST => I.OBJECT_NAME,
DAYS_OLD => NULL,
COLUMN_STAT => FALSE);
DBMS_OUTPUT.PUT_LINE ('2-E' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
END LOOP;
COMMIT;
--
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=APPS';
--
DBMS_OUTPUT.
PUT_LINE (TO_CHAR (M_RETCODE) || ': ' || SUBSTR (M_ERRBUF, 1, 220));
DBMS_OUTPUT.
PUT_LINE ('EOP = ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.
PUT_LINE (TO_CHAR (SQLCODE) || ': ' || SUBSTR (SQLERRM, 1, 220));
END;
No comments:
Post a Comment