Monday, April 13, 2009

Column DEGREE of the DBA_INDEXES view is VARCHAR type

Not aware DEGREE is VARCHAR2(40) type. So surprised to see the following error. It appears that some values can not be converted to numbers.



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>

1 comment:

Anonymous said...

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