| CTXSYS ERROR [message #494095] |
Fri, 11 February 2011 06:30  |
 |
Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA
|
Senior Member |
|
|
Hi,
I'm facing the following error.. in logs when i run a shell script
and i dont have access to CTXSYS schema.
BEGIN ctx_ddl.sync_index(IDX_NAME => 'TKT_INDEX', MEMORY=> '50M', PARALLEL_DEGREE => 4); END;
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10502: index TXT_INDEX does not exist
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 544
ORA-06512: at line 1
What exactly this is, and how to fix.
|
|
|
|
| Re: CTXSYS ERROR [message #494155 is a reply to message #494095] |
Fri, 11 February 2011 10:34  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
What you have posted is an attempted synchronization of an Oracle Text index that has failed, due to either the wrong index name being used or a lack of privileges so that it does not know that the index exists. However, what you posted does not match. The index name in the code that supposedly produced the error does not match the index name in the error message. In order to synchronize the index, you must provide the correct index name and run the code with the privileges of the owner of the index. When posting you should provide a copy and paste of an actual run from SQL*Plus, not a shell script. If you can get the code to run without error from SQL*Plus, but not from a shell script, then you know your problem is due to privileges that you lack when running the shell script that you have in SQL*Plus, and you need to fix that. I have provided a brief reproduction of the error and correction below.
-- test environment:
-- table:
SCOTT@orcl_11gR2> CREATE TABLE txt_table
2 (txt_column VARCHAR2 (30))
3 /
Table created.
-- Oracle Text index:
SCOTT@orcl_11gR2> CREATE INDEX txt_index
2 ON txt_table (txt_column)
3 INDEXTYPE IS CTXSYS.CONTEXT
4 /
Index created.
-- reproduction of error
-- due to wrong index name:
SCOTT@orcl_11gR2> BEGIN
2 ctx_ddl.sync_index
3 (IDX_NAME => 'TKT_INDEX',
4 MEMORY => '50M',
5 PARALLEL_DEGREE => 4);
6 END;
7 /
BEGIN
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10502: index TKT_INDEX does not exist
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 847
ORA-06512: at line 2
-- elimination of error
-- by using correct index name:
SCOTT@orcl_11gR2> BEGIN
2 ctx_ddl.sync_index
3 (IDX_NAME => 'TXT_INDEX',
4 MEMORY => '50M',
5 PARALLEL_DEGREE => 4);
6 END;
7 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
|
|
| Re: CTXSYS ERROR [message #494201 is a reply to message #494170] |
Fri, 11 February 2011 14:25 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In order to synchronize the index, you must be the owner of the index or accessing it through something that uses the owner's privileges and either have the ctxapp role granted to you or have execute on ctxsys.ctx_ddl granted to you. Roles do not apply to procedures, so if running it through a procedure, you need execute on ctxsys.ctx_ddl. The same may apply to some tools. You need to indicate what user owns the index, and what user is trying to synchronize the index. Also, as I said before, you need to test from SQL*Plus and post a copy and paste, changing the names if you like. You need to get a simple anonymous pl/sql block run from SQL*Plus in the owner's schema to work first, then only if that works should you progress to trying to run it from another schema via procedure or some tool or some script. Most of these other problems that I have mentioned will produce errors, but not the one that you are getting. The error that you are getting happens when there is no such index. This could be due to differences in case (upper, lower, mixed). When you search in ctx_user_indexes, if the index is not in all upper case, then you need to put it in whatever case it is with double quotes around it, because that is apparently how it was created.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
