system@DB10G> select index_name, degree from dba_indexes where degree > 2;
select index_name, degree from dba_indexes where degree > 2
*
ERROR at line 1:
ORA-01722: invalid number
system@DB10G> select index_name, degree from all_indexes where degree > 2;
select index_name, degree from all_indexes where degree > 2
*
ERROR at line 1:
ORA-01722: invalid number
system@DB10G> select index_name, degree from user_indexes where degree > 2;
system@DB10G> desc dba_indexes;
Name Null? Type
------------------------------------ -------- ---------------
OWNER NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
.....
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
.....
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)
system@DB10G>
Everything Changes
1 week ago
1 comment:
The following query can be used to find all tables and indexes that have degree not equal 1. Copied from http://blog.tanelpoder.com/?s=index+rebuild
SELECT
'INDEX' OBJECT_TYPE, OWNER, INDEX_NAME, TRIM(DEGREE)
FROM
DBA_INDEXES
WHERE
TRIM(DEGREE) > TO_CHAR(1)
UNION ALL
SELECT
'TABLE', OWNER, TABLE_NAME, TRIM(DEGREE)
FROM
DBA_TABLES
WHERE
TRIM(DEGREE) > TO_CHAR(1)
/
-- Denis
Post a Comment