Toad World® Forums

ORA-29275: partial multibyte character

Earlier I used to be able to query the below table. Not able to do so anymore.

select * from searchsys.eq$statistic

gives the error ORA-29275: partial multibyte character

ALTER TABLE SEARCHSYS.EQ$STATISTIC
DROP PRIMARY KEY CASCADE;

DROP TABLE SEARCHSYS.EQ$STATISTIC CASCADE CONSTRAINTS;

CREATE TABLE SEARCHSYS.EQ$STATISTIC
(
SEQ NUMBER NOT NULL,
QUERY CLOB,
USER_QUERY CLOB,
QCHECKSUM VARCHAR2(4000 BYTE),
USERNAME VARCHAR2(4000 BYTE),
CALL_TYPE NUMBER DEFAULT 1,
QSEQ NUMBER,
QDATE DATE,
QTIME NUMBER,
NUMHITS NUMBER,
HITS VARCHAR2(4000 BYTE),
LANG VARCHAR2(2 BYTE)
)
LOB (QUERY) STORE AS BASICFILE (
TABLESPACE OES_ASSM
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING)
LOB (USER_QUERY) STORE AS BASICFILE (
TABLESPACE OES_ASSM
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING)
TABLESPACE OES_ASSM
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING
ENABLE ROW MOVEMENT;

CREATE UNIQUE INDEX SEARCHSYS.EQ$STATISTIC_PK ON SEARCHSYS.EQ$STATISTIC
(SEQ, CALL_TYPE)
LOGGING
TABLESPACE OES_ASSM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

CREATE INDEX SEARCHSYS.EQ$STATISTIC_QCHECKSUM ON SEARCHSYS.EQ$STATISTIC
(QCHECKSUM)
LOGGING
TABLESPACE OES_ASSM
PCTFREE 10
INITRANS 2
MAXTRANS 167
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

CREATE INDEX SEARCHSYS.EQ$STATISTIC_QDATE ON SEARCHSYS.EQ$STATISTIC
(QDATE)
LOGGING
TABLESPACE OES_ASSM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

CREATE INDEX SEARCHSYS.XX_STATISTIC_N1 ON SEARCHSYS.EQ$STATISTIC
(LOWER(“SYS”.“DBMS_LOB”.“SUBSTR”(“USER_QUERY”,1000,1)))
NOLOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 167
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );

ALTER TABLE SEARCHSYS.EQ$STATISTIC ADD (
CONSTRAINT EQ$STATISTIC_PK
PRIMARY KEY
(SEQ, CALL_TYPE)
USING INDEX SEARCHSYS.EQ$STATISTIC_PK
ENABLE VALIDATE);

Earlier with F4, I was able to see the script and data without any issues.

Now I have to create queries and put in dbms_lob.substr( for the CLOB and LOB objects for it to work.

This error doesn’t really have anything to do with Toad.

Read here, we’ve discussed this problem before:

www.toadworld.com/…/20735