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>
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.
Subscribe to:
Post Comments (Atom)
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