120 lines
4.8 KiB
SQL
120 lines
4.8 KiB
SQL
@@header
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display unusable indexes
|
|
* Parameters : 1. Owner (% - wildchar, \ - escape char) , this could also be passed as OWNER.OBJECT_NAME
|
|
* 2. Table Owner (% - wildchar, \ - escape char)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ ------------------------------------------------
|
|
* 27-Jul-15 Vishal Gupta Added owner and table_name as input parameters
|
|
* 12-Mar-12 Vishal Gupta Intial version
|
|
*
|
|
*
|
|
*/
|
|
set term on
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE table_name
|
|
UNDEFINE subobject_name
|
|
UNDEFINE object_type
|
|
UNDEFINE object_status
|
|
UNDEFINE WHERECLAUSE
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE table_name="&&2"
|
|
DEFINE WHERECLAUSE="&&3"
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _table_name NEW_VALUE table_name NOPRINT
|
|
COLUMN _subobject_name NEW_VALUE subobject_name NOPRINT
|
|
COLUMN _object_type NEW_VALUE object_type NOPRINT
|
|
|
|
set term off
|
|
|
|
SELECT DECODE(UPPER('&&owner'),'','%','&&owner') "_owner"
|
|
, DECODE(UPPER('&&table_name'),'','%','&&table_name') "_table_name"
|
|
, DECODE(UPPER('&&subobject_name'),'','%','&&subobject_name') "_subobject_name"
|
|
, DECODE(UPPER('&&object_type'),'','%','&&object_type') "_object_type"
|
|
FROM DUAL
|
|
;
|
|
|
|
|
|
SELECT CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1)
|
|
ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner'))
|
|
END "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE DECODE(UPPER('&&table_name'),'','%',UPPER('&&table_name'))
|
|
END "_table_name"
|
|
FROM DUAL
|
|
;
|
|
|
|
|
|
set term on
|
|
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * Unusable Indexes
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - Table Name = '&&table_name'
|
|
PROMPT * - Where Clause = '&&WHERECLAUSE'
|
|
PROMPT *****************************************************************
|
|
|
|
COLUMN table_owner HEADING "Table Owner" FORMAT a20
|
|
COLUMN table_name HEADING "Table Name" FORMAT a45
|
|
COLUMN index_owner HEADING "Index Owner" FORMAT a20
|
|
COLUMN index_name HEADING "Index Name" FORMAT a45
|
|
COLUMN partition_name HEADING "Partition Name" FORMAT a25
|
|
COLUMN subpartition_name HEADING "SubPartition Name" FORMAT a25
|
|
COLUMN tablespace_name HEADING "Tablespace Name" FORMAT a20
|
|
COLUMN status HEADING "Status" FORMAT a10
|
|
COLUMN uniqueness HEADING "Uniqness" FORMAT a10
|
|
COLUMN rebuild_sql HEADING "Rebuild SQL Statement" FORMAT a200
|
|
|
|
|
|
SELECT i.table_owner || '.' || i.table_name table_name
|
|
, i.owner || '.' || i.index_name index_name
|
|
, ip.partition_name
|
|
, isp.subpartition_name
|
|
, NVL(NVL(isp.tablespace_name,ip.tablespace_name),i.tablespace_name) tablespace_name
|
|
, NVL(NVL(isp.status,ip.status),i.status) status
|
|
, i.uniqueness
|
|
FROM dba_indexes i
|
|
LEFT OUTER JOIN dba_ind_partitions ip ON i.owner = ip.index_owner AND i.index_name = ip.index_name
|
|
LEFT OUTER JOIN dba_ind_subpartitions isp ON ip.index_owner = isp.index_owner AND ip.index_name = isp.index_name AND ip.partition_name = isp.partition_name
|
|
WHERE NVL(NVL(isp.status,ip.status),i.status) NOT IN ('VALID','N/A','USABLE')
|
|
AND i.owner LIKE '&&owner'
|
|
AND i.table_name LIKE '&&table_name'
|
|
ORDER BY 1,2,3,4,5,6
|
|
;
|
|
|
|
SELECT 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" REBUILD'
|
|
|| NVL2(isp.subpartition_name,' SUBPARTITION ' || isp.subpartition_name, NVL2(ip.partition_name,' PARTITION "' || ip.partition_name || '"' , ' '))
|
|
|| ' PARALLEL 4; '
|
|
|| NVL2(isp.subpartition_name,' ' ,NVL2( ip.partition_name, ' ', chr(10) || 'ALTER INDEX "' || i.owner || '"."' || i.index_name || DECODE(i.degree,1,'" NOPARALLEL;','" PARALLEL ' || i.degree || ';') ) )
|
|
rebuild_sql
|
|
FROM dba_indexes i
|
|
LEFT OUTER JOIN dba_ind_partitions ip ON i.owner = ip.index_owner AND i.index_name = ip.index_name
|
|
LEFT OUTER JOIN dba_ind_subpartitions isp ON ip.index_owner = isp.index_owner AND ip.index_name = isp.index_name AND ip.partition_name = isp.partition_name
|
|
WHERE NVL(NVL(isp.status,ip.status),i.status) NOT IN ('VALID','N/A','USABLE')
|
|
AND i.owner LIKE '&&owner'
|
|
AND i.table_name LIKE '&&table_name'
|
|
ORDER BY 1
|
|
;
|
|
|
|
@@footer
|