OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » lobindex

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

lobindex [message #157676]

Fri, 03 February 2006 15:19

TLegend33
Messages: 203
Registered: March 2005

Senior Member

I created a table with a clob column and with it a lobindex was created. During my testing, I noticed this lobindex displayed a status of 'N/A' when I queried user_indexes. I created another clob index on the same table and a new lobindex was created, that too displayed 'N/A' for status. As I understand it, the lobindex should be created whenever a lob column is added to the table. Can anyone explain why I get a status of 'N/A'??

Thanks.

Report message to a moderator

Re: lobindex [message #157702 is a reply to message #157676]

Sat, 04 February 2006 04:16

Frank Naude
Messages: 4580
Registered: April 1998

Senior Member

Are these indexes partitioned? If so, the logindex shouldn't have a status (N/A) - use the status on partition level. Examples:

Non-partitioned lobindex:

SQL> CREATE TABLE lob_tab1(col1 nUMBER, col2 CLOB);

Table created.

SQL>
SQL> SELECT index_name, index_type, status
  2    FROM user_indexes
  3   WHERE table_name = 'LOB_TAB1'
  4  /

INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
SYS_IL0000053989C00002$$       LOB                         VALID

Partitioned lobindex:

SQL> CREATE TABLE lob_tab2(col1 NUMBER, col2 CLOB)
  2  PARTITION BY LIST (col1) (
  3    PARTITION p1 VALUES (1)
  4  )
  5  /

Table created.

SQL>
SQL> SELECT index_name, index_type, status
  2    FROM user_indexes
  3   WHERE table_name = 'LOB_TAB2'
  4  /

INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
SYS_IL0000053992C00002$$       LOB                         N/A

SQL>
SQL> SELECT index_name, partition_name, status
  2    FROM user_ind_partitions
  3   WHERE index_name = (SELECT index_name
  4                         FROM user_indexes
  5                        WHERE table_name = 'LOB_TAB2')
  6  /

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
SYS_IL0000053992C00002$$       SYS_IL_P44                     USABLE

Best regards.

Frank

Report message to a moderator

Re: lobindex [message #157867 is a reply to message #157702]

Mon, 06 February 2006 16:00

TLegend33
Messages: 203
Registered: March 2005

Senior Member

Yes, the table is partitioned. You're exactly right. I must assume that a status of 'useable' indicates the index is functioning correctly. Thanks very much for your help.

Report message to a moderator

Previous Topic:	ORA-01031: insufficient privileges when creating trigger/synonym
Next Topic:	Maximum data segment size

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 14:33:10 CDT 2024