Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Drop Index

JomarFeb 18 2010 — edited Feb 18 2010
Hi,

I can't drop this index.

SQL Error: ORA-22864: cannot ALTER or DROP LOB indexes
22864. 00000 -  "cannot ALTER or DROP LOB indexes"
*Cause:    An attempt was made to ALTER or DROP a LOB index.
*Action:   Do not operate directly on the system-defined LOB index.
           Perform operations on the corresponding LOB column.

I need drop this index. 
How do i can drop index?


Regards

Jomar

PS:

CREATE UNIQUE INDEX in_tablea ON tablea
  (
    PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE USER_INDEX 
PARALLEL (DEGREE 0 INSTANCES 0) ;

CREATE TABLE tablea  (
    "Q_NAME" VARCHAR2(30 BYTE),
    "MSGID" RAW(16),
    "CORRID"   VARCHAR2(128 BYTE),
    "PRIORITY" NUMBER,
    "STATE"    NUMBER,
    "DELAY" TIMESTAMP (6),
    "EXPIRATION" NUMBER,
    "TIME_MANAGER_INFO" TIMESTAMP (6),
    "LOCAL_ORDER_NO" NUMBER,
    "CHAIN_NO"       NUMBER,
    "CSCN"           NUMBER,
    "DSCN"           NUMBER,
    "ENQ_TIME" TIMESTAMP (6),
    "ENQ_UID" NUMBER,
    "ENQ_TID" VARCHAR2(30 BYTE),
    "DEQ_TIME" TIMESTAMP (6),
    "DEQ_UID"           NUMBER,
    "DEQ_TID"           VARCHAR2(30 BYTE),
    "RETRY_COUNT"       NUMBER,
    "EXCEPTION_QSCHEMA" VARCHAR2(30 BYTE),
    "EXCEPTION_QUEUE"   VARCHAR2(30 BYTE),
    "STEP_NO"           NUMBER,
    "RECIPIENT_KEY"     NUMBER,
    "DEQUEUE_MSGID" RAW(16),
    "SENDER_NAME"     VARCHAR2(30 BYTE),
    "SENDER_ADDRESS"  VARCHAR2(1024 BYTE),
    "SENDER_PROTOCOL" NUMBER,
    "USER_DATA" BLOB,
    "USER_PROP" "SYS"."ANYDATA" ,
    PRIMARY KEY ("MSGID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_INDEX" ENABLE
  )
  USAGE QUEUE PCTFREE 10 PCTUSED 10 INITRANS 20 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  )
  TABLESPACE "USER_INDEX" LOB
  (
    "USER_DATA"
  )
  STORE AS
  (
    TABLESPACE "USER_INDEX" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  )
  OPAQUE TYPE "USER_PROP" STORE AS LOB
  (
    ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 CACHE STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  ) ;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 18 2010
Added on Feb 18 2010
2 comments
1,189 views