Category: PIPER-Rx Reports (Free) Name: FDBA002-20 Index Rebuild ReportDefFileName: .\FDBA002-20 Index Rebuild.fr3 ===Begin Comments=== Copyright (C) 2004 G Piper This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details. a copy of the GNU Affero General Public Licenses provided is part of the down load Terms and Conditions available on our site www.piper-rx.com or you can refere to www.gnu.org/licenses/gpl.html ===End Comments=== ===Begin SQL=== -- **************************************************** -- ** Index rebuild list -- ** -- ** Special for PIPER-Rx blog -- ** -- ** Warning: This report will be slow... -- ** ------------------------------------------------- -- ** Version 1.0 Jan-11 G.Piper - PIPER-Rx -- ** Version 2.0 Feb-12 G.Piper - Piper-Rx -- ** -- **************************************************** SELECT report_headings.report_date, report_headings.sid_name, :MINIMUM_ROWS minimum_rows, :REBUILD_THREHOLD_MONTHS rebuild_threshold, dob.owner, dind.table_name, dob.object_name index_name, dind.num_rows row_count, initcap(dind.uniqueness) index_type, initcap(dind.partitioned) partitioned, dind.temporary, dind.generated, to_char(dob.created, 'DD-Mon-YY HH24:MI') created_date, to_char(dob.last_ddl_time, 'DD-Mon-YY HH24:MI') last_ddl_date, lpad(trunc((months_between(sysdate , dob.last_ddl_time)) /12 ),2,'0') ||':'|| lpad(trunc((months_between(sysdate , dob.last_ddl_time)) - (trunc((months_between(sysdate , dob.last_ddl_time)) /12 ) *12 )),2,'0') ||':'|| lpad(trunc(((months_between(sysdate , dob.last_ddl_time)) - (trunc((months_between(sysdate , dob.last_ddl_time)) /12 ) *12 ) - (trunc((months_between(sysdate , dob.last_ddl_time)) - (trunc((months_between(sysdate , dob.last_ddl_time)) /12 ) *12 )))) * to_char(last_day(sysdate), 'DD')),2,'0') "Last Rebuilt YY-MM-DD", Decode( sign(months_between (sysdate, dob.last_ddl_time) - :REBUILD_THREHOLD_MONTHS ), -1, null, 'Rebuild') rebuild_status FROM dba_objects dob, dba_indexes dind, ( SELECT to_char(sysdate, 'DD-Mon-YY HH24:MI') report_date, vd.name sid_name FROM gv$database vd WHERE vd.inst_id = 1 ) report_headings WHERE dob.owner = dind.owner and dob.object_name = dind.index_name and dob.object_name in (SELECT index_name FROM dba_indexes WHERE (table_name in ('FND_CONCURRENT_REQUESTS', --Sign-on Audit Purge 'FND_LOGINS', 'FND_LOGIN_RESPONSIBILITIES', 'FND_LOGIN_FORMS', -- LOBS 'FND_LOBS', -- Self Service Purge 'ICX_SESSIONS', 'ICX_SESSION_ATTRIBUTES', 'ICX_TRANSACTIONS', 'ICX_TEXT', 'ICX_CONTEXT_RESULTS_TEMP', 'ICX_FAILURES', 'ICX_REQUISITIONER_INFO', 'FND_SESSION_VALUES', -- Workflow Purge 'WF_ACTIVITIES', 'WF_ACTIVITIES_TL', 'WF_ACTIVITY_ATTR_VALUES', 'WF_ACTIVITY_ATTRIBUTES', 'WF_ACTIVITY_ATTRIBUTES_TL', 'WF_ACTIVITY_TRANSITIONS', 'WF_COMMENTS', 'WF_ITEM_ACTIVITY_STATUSES', 'WF_ITEM_ACTIVITY_STATUSES_H', 'WF_ITEMS', 'WF_NOTIFICATION_ATTRIBUTES', 'WF_NOTIFICATIONS', 'WF_PROCESS_ACTIVITIES' ) or table_name like '%INTERFACE%') and (num_rows > :MINIMUM_ROWS or num_rows is null )) and dob.object_type = 'INDEX' ORDER by dind.table_name, dob.object_name ===End SQL=== ParamName: REBUILD_THREHOLD_MONTHS ParamType: Number ParamValue: 6 ParamExp: ParamName: MINIMUM_ROWS ParamType: Number ParamValue: 5000 ParamExp: ===Begin Report Definition===