SQL> set linesize 150
column OWNER format a20
column SEGMENT_NAME format a20
column PARTITION_NAME format a20
column SEGMENT_TYPE format a15
column TABLESPACE_NAME format a18
select OWNER,SEGMENT_NAME,PARTITION_NAME,
SEGMENT_TYPE,TABLESPACE_NAME
from dba_extents
where FILE_ID=9 and 885 between block_id and block_id+blocks-1;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- -------------------- -------------------- --------------- ------------------
AUD_OWNER AUD_COPY TABLE AUDITS
SQL> set linesize 150
column OBJECT_TYPE format a20
select OBJECT_TYPE
from dba_objects
where owner='AUD_OWNER' and object_name='AUD_COPY';
OBJECT_TYPE
--------------------
TABLE
SQL> set linesize 150
column OWNER format a20
column INDEX_NAME format a20
select owner,INDEX_NAME
from dba_indexes
where TABLE_OWNER='AUD_OWNER'
and TABLE_NAME='AUD_COPY';
OWNER INDEX_NAME
-------------------- --------------------
AUD_OWNER AC_PK
AUD_OWNER AC_IN1
AUD_OWNER AC_IN2
AUD_OWNER AC_IN3
SQL> set linesize 150
column OWNER format a20
column INDEX_NAME format a20
column INDEX_TYPE format a10
column TABLE_OWNER format a15
column TABLE_NAME format a15
select owner,INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, PARTITIONED, STATUS,
TO_CHAR(LAST_ANALYZED, 'DD-MON-YYYY HH24:MI:SS') AS LAST_ANALYZED
from dba_indexes
where TABLE_OWNER='AUD_OWNER'
and TABLE_NAME='AUD_COPY';
OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME PAR STATUS LAST_ANALYZED
-------------------- -------------------- ---------- --------------- --------------- --- -------- -----------------------
AUD_OWNER AC_PK NORMAL AUD_OWNER AUD_COPY NO VALID 28-MAY-2018 22:00:23
AUD_OWNER AC_IN1 NORMAL AUD_OWNER AUD_COPY NO VALID 28-MAY-2018 22:00:30
AUD_OWNER AC_IN2 NORMAL AUD_OWNER AUD_COPY NO VALID 28-MAY-2018 22:00:33
AUD_OWNER AC_IN3 NORMAL AUD_OWNER AUD_COPY NO VALID 28-MAY-2018 22:00:35
SQL> alter index AUD_OWNER.AC_PK rebuild online;
OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME PAR STATUS LAST_ANALYZED
-------------------- -------------------- ---------- --------------- --------------- --- -------- -----------------------
AUD_OWNER AC_PK NORMAL AUD_OWNER AUD_COPY NO VALID 29-MAY-2018 15:06:05
AUD_OWNER AC_IN1 NORMAL AUD_OWNER AUD_COPY NO VALID 28-MAY-2018 22:00:30
AUD_OWNER AC_IN2 NORMAL AUD_OWNER AUD_COPY NO VALID 28-MAY-2018 22:00:33
AUD_OWNER AC_IN3 NORMAL AUD_OWNER AUD_COPY NO VALID 28-MAY-2018 22:00:35
SQL> set linesize 150
select 'alter index '||owner||'.'||index_name||' rebuild online;'
from dba_indexes
where TABLE_OWNER='AUD_OWNER'
and TABLE_NAME='AUD_COPY';
'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINE;'
-----------------------------------------------------------------------------------------
alter index AUD_OWNER.AC_PK rebuild online;
alter index AUD_OWNER.AC_IN1 rebuild online;
alter index AUD_OWNER.AC_IN2 rebuild online;
alter index AUD_OWNER.AC_IN3 rebuild online;
SQL> alter index AUD_OWNER.AC_IN1 rebuild online;
SQL> alter index AUD_OWNER.AC_IN2 rebuild online;
SQL> alter index AUD_OWNER.AC_IN3 rebuild online;
SQL> set linesize 150
column OWNER format a20
column INDEX_NAME format a20
column INDEX_TYPE format a10
column TABLE_OWNER format a15
column TABLE_NAME format a15
select owner,INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, PARTITIONED, STATUS,
TO_CHAR(LAST_ANALYZED, 'DD-MON-YYYY HH24:MI:SS') AS LAST_ANALYZED
from dba_indexes
where TABLE_OWNER='AUD_OWNER'
and TABLE_NAME='AUD_COPY';
OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME PAR STATUS LAST_ANALYZED
-------------------- -------------------- ---------- --------------- --------------- --- -------- -----------------------
AUD_OWNER AC_PK NORMAL AUD_OWNER AUD_COPY NO VALID 29-MAY-2018 15:06:05
AUD_OWNER AC_IN1 NORMAL AUD_OWNER AUD_COPY NO VALID 29-MAY-2018 15:06:43
AUD_OWNER AC_IN2 NORMAL AUD_OWNER AUD_COPY NO VALID 29-MAY-2018 15:06:59
AUD_OWNER AC_IN3 NORMAL AUD_OWNER AUD_COPY NO VALID 29-MAY-2018 15:07:11
SQL> analyze table AUD_OWNER.AUD_COPY validate structure cascade online;
Table analyzed.
**********************************************************************************************************************************
WRH$_SQLSTAT
Rebuild the Partitioned index to fix index corruption issue
[Oracle Support]
Kindly analyze the table :
sql> @?/rdbms/admin/utlvalid.sql
sql> analyze table WRH$_SQLSTAT validate structure cascade online;
[DBA team]
SQL> @?/rdbms/admin/utlvalid.sql
Table created.
SQL> ANALYZE TABLE WRH$_SQLSTAT VALIDATE STRUCTURE;
SQL> analyze table WRH$_SQLSTAT validate structure cascade online;
analyze table WRH$_SQLSTAT validate structure cascade online
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
-- it should fail with Ora-1499 if at least one index is corrupted
SQL> set linesize 150
column OBJECT_TYPE format a20
select OBJECT_TYPE
from dba_objects
where owner='SYS' and object_name='WRH$_SQLSTAT';
OBJECT_TYPE
--------------------
TABLE
TABLE PARTITION
TABLE PARTITION
TABLE PARTITION
TABLE PARTITION
TABLE PARTITION
TABLE PARTITION
TABLE PARTITION
TABLE PARTITION
TABLE PARTITION
10 rows selected.
SQL> set linesize 150
column OWNER format a20
column INDEX_NAME format a25
select owner,INDEX_NAME
from dba_indexes
where TABLE_OWNER='SYS'
and TABLE_NAME='WRH$_SQLSTAT';
OWNER INDEX_NAME
-------------------- -------------------------
SYS WRH$_SQLSTAT_INDEX
SYS WRH$_SQLSTAT_PK
SQL> set linesize 150
column OWNER format a20
column INDEX_NAME format a20
column INDEX_TYPE format a10
column TABLE_OWNER format a15
column TABLE_NAME format a15
column PARTITIONED format a12
select owner,INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, PARTITIONED, STATUS,
TO_CHAR(LAST_ANALYZED, 'DD-MON-YYYY HH24:MI:SS') AS LAST_ANALYZED
from dba_indexes
where TABLE_OWNER='SYS'
and TABLE_NAME='WRH$_SQLSTAT';
OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME PARTITIONED STATUS LAST_ANALYZED
-------------------- -------------------- ---------- --------------- --------------- ------------ -------- -----------------------
SYS WRH$_SQLSTAT_INDEX NORMAL SYS WRH$_SQLSTAT YES N/A 28-MAY-2018 22:00:52
SYS WRH$_SQLSTAT_PK NORMAL SYS WRH$_SQLSTAT YES N/A 28-MAY-2018 22:00:52
SQL> alter index WRH$_SQLSTAT_PK rebuild online;
alter index WRH$_SQLSTAT_PK rebuild online
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
SQL> alter index WRH$_SQLSTAT_INDEX rebuild online;
alter index WRH$_SQLSTAT_INDEX rebuild online
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
select partition_name
from dba_ind_partitions
where index_name='WRH$_SQLSTAT_PK';
PARTITION_NAME
------------------------------
WRH$_SQLSTAT_MXDB_MXSN
WRH$_SQLSTA_2828234821_0
WRH$_SQLSTA_2828234821_108
WRH$_SQLSTA_2828234821_132
WRH$_SQLSTA_2828234821_156
WRH$_SQLSTA_2828234821_180
WRH$_SQLSTA_2828234821_36
WRH$_SQLSTA_2828234821_60
WRH$_SQLSTA_2828234821_84
9 rows selected.
select partition_name
from dba_ind_partitions
where index_name='WRH$_SQLSTAT_INDEX';
PARTITION_NAME
------------------------------
WRH$_SQLSTAT_MXDB_MXSN
WRH$_SQLSTA_2828234821_0
WRH$_SQLSTA_2828234821_108
WRH$_SQLSTA_2828234821_132
WRH$_SQLSTA_2828234821_156
WRH$_SQLSTA_2828234821_180
WRH$_SQLSTA_2828234821_36
WRH$_SQLSTA_2828234821_60
WRH$_SQLSTA_2828234821_84
9 rows selected.
set linesize 150
column index_owner format a15
column index_name format a20
column partition_name format a30
column tablespace_name format a15
select index_owner, index_name, partition_name, status, tablespace_name,
TO_CHAR(LAST_ANALYZED, 'DD-MON-YYYY HH24:MI:SS') AS LAST_ANALYZED
from dba_ind_partitions
where index_name='WRH$_SQLSTAT_INDEX';
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME LAST_ANALYZED
--------------- -------------------- ------------------------------ -------- --------------- -----------------------
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTAT_MXDB_MXSN USABLE SYSAUX 21-MAY-2018 23:00:22
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_0 USABLE SYSAUX 22-MAY-2018 22:01:13
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_108 USABLE SYSAUX 27-MAY-2018 06:00:51
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_132 USABLE SYSAUX 28-MAY-2018 22:01:28
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_156 USABLE SYSAUX 28-MAY-2018 22:01:27
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_180 USABLE SYSAUX
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_36 USABLE SYSAUX 24-MAY-2018 22:01:06
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_60 USABLE SYSAUX 25-MAY-2018 22:00:54
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_84 USABLE SYSAUX 26-MAY-2018 06:00:39
9 rows selected.
set linesize 150
column index_owner format a15
column index_name format a20
column partition_name format a30
column tablespace_name format a15
select index_owner, index_name, partition_name, status, tablespace_name,
TO_CHAR(LAST_ANALYZED, 'DD-MON-YYYY HH24:MI:SS') AS LAST_ANALYZED
from dba_ind_partitions
where index_name='WRH$_SQLSTAT_PK';
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME LAST_ANALYZED
--------------- -------------------- ------------------------------ -------- --------------- -----------------------
SYS WRH$_SQLSTAT_PK WRH$_SQLSTAT_MXDB_MXSN USABLE SYSAUX 21-MAY-2018 23:00:22
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_0 USABLE SYSAUX 22-MAY-2018 22:01:13
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_108 USABLE SYSAUX 27-MAY-2018 06:00:51
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_132 USABLE SYSAUX 28-MAY-2018 22:01:28
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_156 USABLE SYSAUX 28-MAY-2018 22:01:27
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_180 USABLE SYSAUX
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_36 USABLE SYSAUX 24-MAY-2018 22:01:06
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_60 USABLE SYSAUX 25-MAY-2018 22:00:54
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_84 USABLE SYSAUX 26-MAY-2018 06:00:39
9 rows selected.
SQL> select 'alter index '||index_name||' rebuild partition '||partition_name||' online;'
from dba_ind_partitions
where index_name='WRH$_SQLSTAT_INDEX';
'ALTERINDEX'||INDEX_NAME||'REBUILDPARTITION'||PARTITION_NAME||'ONLINE;'
---------------------------------------------------------------------------------------------------
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTAT_MXDB_MXSN online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_0 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_108 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_132 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_156 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_180 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_36 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_60 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_84 online;
9 rows selected.
SQL> select 'alter index '||index_name||' rebuild partition '||partition_name||' online;'
from dba_ind_partitions
where index_name='WRH$_SQLSTAT_PK';
'ALTERINDEX'||INDEX_NAME||'REBUILDPARTITION'||PARTITION_NAME||'ONLINE;'
---------------------------------------------------------------------------------------------------
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTAT_MXDB_MXSN online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_0 online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_108 online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_132 online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_156 online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_180 online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_36 online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_60 online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_84 online;
9 rows selected.
$ vi /tmp/rebuildindex.sql
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTAT_MXDB_MXSN online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_0 online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_108 online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_132 online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_156 online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_180 online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_36 online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_60 online;
alter index WRH$_SQLSTAT_PK rebuild partition WRH$_SQLSTA_2828234821_84 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTAT_MXDB_MXSN online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_0 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_108 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_132 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_156 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_180 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_36 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_60 online;
alter index WRH$_SQLSTAT_INDEX rebuild partition WRH$_SQLSTA_2828234821_84 online;
$ sqlplus / as sysdba;
SQL>@/tmp/rebuildindex.sql
No errors.
set linesize 150
column index_owner format a15
column index_name format a20
column partition_name format a30
column tablespace_name format a15
select index_owner, index_name, partition_name, status, tablespace_name,
TO_CHAR(LAST_ANALYZED, 'DD-MON-YYYY HH24:MI:SS') AS LAST_ANALYZED
from dba_ind_partitions
where index_name='WRH$_SQLSTAT_INDEX';
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME LAST_ANALYZED
--------------- -------------------- ------------------------------ -------- --------------- -----------------------
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTAT_MXDB_MXSN USABLE SYSAUX 29-MAY-2018 15:46:53
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_0 USABLE SYSAUX 29-MAY-2018 15:46:53
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_108 USABLE SYSAUX 29-MAY-2018 15:46:53
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_132 USABLE SYSAUX 29-MAY-2018 15:46:53
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_156 USABLE SYSAUX 29-MAY-2018 15:46:53
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_180 USABLE SYSAUX 29-MAY-2018 15:46:53
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_36 USABLE SYSAUX 29-MAY-2018 15:46:53
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_60 USABLE SYSAUX 29-MAY-2018 15:46:53
SYS WRH$_SQLSTAT_INDEX WRH$_SQLSTA_2828234821_84 USABLE SYSAUX 29-MAY-2018 15:46:53
9 rows selected.
set linesize 150
column index_owner format a15
column index_name format a20
column partition_name format a30
column tablespace_name format a15
select index_owner, index_name, partition_name, status, tablespace_name,
TO_CHAR(LAST_ANALYZED, 'DD-MON-YYYY HH24:MI:SS') AS LAST_ANALYZED
from dba_ind_partitions
where index_name='WRH$_SQLSTAT_PK';
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME LAST_ANALYZED
--------------- -------------------- ------------------------------ -------- --------------- -----------------------
SYS WRH$_SQLSTAT_PK WRH$_SQLSTAT_MXDB_MXSN USABLE SYSAUX 29-MAY-2018 15:46:52
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_0 USABLE SYSAUX 29-MAY-2018 15:46:52
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_108 USABLE SYSAUX 29-MAY-2018 15:46:52
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_132 USABLE SYSAUX 29-MAY-2018 15:46:52
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_156 USABLE SYSAUX 29-MAY-2018 15:46:52
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_180 USABLE SYSAUX 29-MAY-2018 15:46:52
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_36 USABLE SYSAUX 29-MAY-2018 15:46:52
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_60 USABLE SYSAUX 29-MAY-2018 15:46:52
SYS WRH$_SQLSTAT_PK WRH$_SQLSTA_2828234821_84 USABLE SYSAUX 29-MAY-2018 15:46:52
9 rows selected.
SQL> ANALYZE TABLE WRH$_SQLSTAT VALIDATE STRUCTURE;
Table analyzed.
SQL> analyze table WRH$_SQLSTAT validate structure cascade online;
Table analyzed.