OraFAQ Forum: Text & interMedia » dbms

Home » Server Options » Text & interMedia » dbms_redefinition (Oracle 10g OC: CENT OS 5)

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

dbms_redefinition [message #524049]

Wed, 21 September 2011 09:19

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

Hi All,

I am getting below mentioned error while doing online redefinition.

ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-11000: invalid keyword REPLACE
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1173
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1712
ORA-06512: at line 52

Any Idea?

Thanks
Deepak

Report message to a moderator

Re: dbms_redefinition [message #524054 is a reply to message #524049]

Wed, 21 September 2011 09:44

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

I am getting this error for a table with column of CLOB datatype. I m not able to build index on that.

Thanks
Deepak

Report message to a moderator

Re: dbms_redefinition [message #524068 is a reply to message #524054]

Wed, 21 September 2011 12:04

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

You need to provide the original table structure, using describe, and the code that you ran using dbms_redefinition.

Report message to a moderator

Re: dbms_redefinition [message #524193 is a reply to message #524068]

Thu, 22 September 2011 03:25

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Combining this with your other recent post about converting varchar2 to nvarchar2, I gather that you are trying to use dbms_redefinition to convert a table that contains a varchar2 to a table that contains nvarchar2 and that this same table has a context index and you are getting the error that you posted during redefinition. I am unable to reproduce the error. The error referes to "REPLACE". I don't see where that would be used, unless you have used it as a table or column name and that causes confusion. I have provided as simple an example as I could below. You need to provide a similar copy and paste that demonstrates how you are getting the error.

SCOTT@orcl_11gR2> -- original table
SCOTT@orcl_11gR2> -- including varchar2 to be converted to nvarchar2
SCOTT@orcl_11gR2> -- and clob column for text index:
SCOTT@orcl_11gR2> CREATE TABLE T_TEST
  2    (COL1  NUMBER PRIMARY KEY,
  3  	COL2  VARCHAR2 (10),
  4  	COL3  CLOB)
  5  /

Table created.

SCOTT@orcl_11gR2> -- test data:
SCOTT@orcl_11gR2> INSERT INTO T_TEST VALUES (1, 'A', 'TEST DATA')
  2  /

1 row created.

SCOTT@orcl_11gR2> -- text index:
SCOTT@orcl_11gR2> CREATE INDEX T_IDX ON T_TEST (COL3) INDEXTYPE IS CTXSYS.CONTEXT
  2  /

Index created.

SCOTT@orcl_11gR2> SELECT * FROM T_TEST WHERE CONTAINS (COL3, 'TEST DATA') > 0
  2  /

      COL1 COL2       COL3
---------- ---------- ----------
         1 A          TEST DATA

1 row selected.

SCOTT@orcl_11gR2> -- create interim table:
SCOTT@orcl_11gR2> CREATE TABLE tmp_1_t_test
  2    (col1  NUMBER,
  3  	col2  NVARCHAR2(10),
  4  	col3  CLOB)
  5  /

Table created.

SCOTT@orcl_11gR2> -- redefinition:
SCOTT@orcl_11gR2> DECLARE
  2    num_errors  NUMBER;
  3  BEGIN
  4    DBMS_REDEFINITION.CAN_REDEF_TABLE
  5  	 (USER, 'T_TEST', DBMS_REDEFINITION.CONS_USE_PK);
  6    DBMS_REDEFINITION.START_REDEF_TABLE
  7  	 (USER, 'T_TEST', 'TMP_1_T_TEST',
  8  	  'COL1 COL1,TO_NCHAR(COL2) COL2,COL3 COL3',
  9  	   DBMS_REDEFINITION.CONS_USE_PK);
 10    DBMS_REDEFINITION.SYNC_INTERIM_TABLE
 11  	 (USER, 'T_TEST', 'TMP_1_T_TEST');
 12    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
 13  	 (USER, 'T_TEST', 'TMP_1_T_TEST',
 14  	  DBMS_REDEFINITION.CONS_ORIG_PARAMS,
 15  	  TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
 16    DBMS_REDEFINITION.FINISH_REDEF_TABLE
 17  	 (USER, 'T_TEST', 'TMP_1_T_TEST');
 18  END;
 19  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> -- drop interim table:
SCOTT@orcl_11gR2> DROP TABLE tmp_1_t_test CASCADE CONSTRAINTS
  2  /

Table dropped.

SCOTT@orcl_11gR2> -- results are new table with nvarchar2 and working text index:
SCOTT@orcl_11gR2> DESC T_TEST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               NVARCHAR2(10)
 COL3                                               CLOB

SCOTT@orcl_11gR2> COLUMN col3 FORMAT A10
SCOTT@orcl_11gR2> SELECT * FROM T_TEST WHERE CONTAINS (COL3, 'TEST DATA') > 0
  2  /

      COL1 COL2       COL3
---------- ---------- ----------
         1 A          TEST DATA

1 row selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: dbms_redefinition [message #524226 is a reply to message #524193]

Thu, 22 September 2011 05:26

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

Thanks mam,

I am mentioning some of the commands that are being used in the create index script while creating the database.

alter index INTERMEDIA1 REBUILD parameters('replace sync(on commit) lexer hyphen_lexer') ;
alter index INTERMEDIA13 REBUILD parameters('replace sync(on commit) lexer hyphen_lexer') ;
alter index INTERMEDIA14 REBUILD parameters('replace sync(on commit) filter ctxsys.INSO_FILTER lexer hyphen_lexer') ;
alter index tce_subject REBUILD parameters('replace sync(on commit) lexer hyphen_lexer') ;
alter index tce_body REBUILD parameters('replace sync(on commit) lexer hyphen_lexer') ;

These are the commands that are being used in the database scripts for the indexes on tables.

If they are helpful for you to understand that what exactly is causing the problem.

Thanks
Deepak

Report message to a moderator

Re: dbms_redefinition [message #524227 is a reply to message #524226]

Thu, 22 September 2011 05:31

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

I have searched all over internet to check if ever someone else also got this error.
Quote:

The error referes to "REPLACE".

but unfortunately did not find anything.

Thanks
Deepak

Report message to a moderator

Re: dbms_redefinition [message #524297 is a reply to message #524227]

Thu, 22 September 2011 15:01

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

I think you may have encountered a bug. It appears that if you use dbms_redefinition on a table with a text index that has been altered using replace, it does not recognize that syntax. The only workaround that I can think of is to drop the index, recreate the index with all of the parameters, without using any alter index statements, then run the redefinition. Please see the reproduction of the problem followed by the workaround below.

-- reproduction of problem:

SCOTT@orcl_11gR2> -- table, data, altered index:
SCOTT@orcl_11gR2> CREATE TABLE T_TEST
  2    (COL1  NUMBER PRIMARY KEY,
  3  	COL2  VARCHAR2 (10),
  4  	COL3  CLOB)
  5  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO T_TEST VALUES (1, 'A', 'TEST DATA')
  2  /

1 row created.

SCOTT@orcl_11gR2> CREATE INDEX I1 ON T_TEST (COL3) INDEXTYPE IS CTXSYS.CONTEXT
  2  /

Index created.

SCOTT@orcl_11gR2> alter index I1 REBUILD parameters('REPLACE sync(on commit)')
  2  /

Index altered.

SCOTT@orcl_11gR2> SELECT * FROM T_TEST WHERE CONTAINS (COL3, 'TEST DATA') > 0
  2  /

      COL1 COL2       COL3
---------- ---------- ----------
         1 A          TEST DATA

1 row selected.

SCOTT@orcl_11gR2> -- redefinition that fails:
SCOTT@orcl_11gR2> CREATE TABLE tmp_1_t_test
  2    (col1  NUMBER,
  3  	col2  NVARCHAR2(10),
  4  	col3  CLOB)
  5  /

Table created.

SCOTT@orcl_11gR2> DECLARE
  2    num_errors  NUMBER;
  3  BEGIN
  4    DBMS_REDEFINITION.CAN_REDEF_TABLE
  5  	 (USER, 'T_TEST', DBMS_REDEFINITION.CONS_USE_PK);
  6    DBMS_REDEFINITION.START_REDEF_TABLE
  7  	 (USER, 'T_TEST', 'TMP_1_T_TEST',
  8  	  'COL1 COL1,TO_NCHAR(COL2) COL2,COL3 COL3',
  9  	   DBMS_REDEFINITION.CONS_USE_PK);
 10    DBMS_REDEFINITION.SYNC_INTERIM_TABLE
 11  	 (USER, 'T_TEST', 'TMP_1_T_TEST');
 12    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
 13  	 (USER, 'T_TEST', 'TMP_1_T_TEST',
 14  	  DBMS_REDEFINITION.CONS_ORIG_PARAMS,
 15  	  TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
 16    DBMS_REDEFINITION.FINISH_REDEF_TABLE
 17  	 (USER, 'T_TEST', 'TMP_1_T_TEST');
 18  END;
 19  /
DECLARE
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-11000: invalid keyword REPLACE
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1364
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2025
ORA-06512: at line 12

-- clean up the mess:

SCOTT@orcl_11gR2> DROP MATERIALIZED VIEW tmp_1_t_test
  2  /

Materialized view dropped.

SCOTT@orcl_11gR2> DROP TABLE tmp_1_t_test CASCADE CONSTRAINTS
  2  /

Table dropped.

SCOTT@orcl_11gR2> BEGIN
  2    DBMS_REDEFINITION.ABORT_REDEF_TABLE
  3  	 (USER, 'T_TEST', 'TMP_1_T_TEST');
  4  END;
  5  /

PL/SQL procedure successfully completed.

-- workaround:

SCOTT@orcl_11gR2> -- drop and recreate index with all parameters without altering:
SCOTT@orcl_11gR2> DROP INDEX I1
  2  /

Index dropped.

SCOTT@orcl_11gR2> CREATE INDEX I1 ON T_TEST (COL3) INDEXTYPE IS CTXSYS.CONTEXT
  2  parameters('sync(on commit)')
  3  /

Index created.

SCOTT@orcl_11gR2> -- redo redefinition:
SCOTT@orcl_11gR2> CREATE TABLE tmp_1_t_test
  2    (col1  NUMBER,
  3  	col2  NVARCHAR2(10),
  4  	col3  CLOB)
  5  /

Table created.

SCOTT@orcl_11gR2> DECLARE
  2    num_errors  NUMBER;
  3  BEGIN
  4    DBMS_REDEFINITION.CAN_REDEF_TABLE
  5  	 (USER, 'T_TEST', DBMS_REDEFINITION.CONS_USE_PK);
  6    DBMS_REDEFINITION.START_REDEF_TABLE
  7  	 (USER, 'T_TEST', 'TMP_1_T_TEST',
  8  	  'COL1 COL1,TO_NCHAR(COL2) COL2,COL3 COL3',
  9  	   DBMS_REDEFINITION.CONS_USE_PK);
 10    DBMS_REDEFINITION.SYNC_INTERIM_TABLE
 11  	 (USER, 'T_TEST', 'TMP_1_T_TEST');
 12    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
 13  	 (USER, 'T_TEST', 'TMP_1_T_TEST',
 14  	  DBMS_REDEFINITION.CONS_ORIG_PARAMS,
 15  	  TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
 16    DBMS_REDEFINITION.FINISH_REDEF_TABLE
 17  	 (USER, 'T_TEST', 'TMP_1_T_TEST');
 18  END;
 19  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> DROP TABLE tmp_1_t_test CASCADE CONSTRAINTS
  2  /

Table dropped.

SCOTT@orcl_11gR2> -- results:
SCOTT@orcl_11gR2> DESC T_TEST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               NVARCHAR2(10)
 COL3                                               CLOB

SCOTT@orcl_11gR2> COLUMN col3 FORMAT A10
SCOTT@orcl_11gR2> SELECT * FROM T_TEST WHERE CONTAINS (COL3, 'TEST DATA') > 0
  2  /

      COL1 COL2       COL3
---------- ---------- ----------
         1 A          TEST DATA

1 row selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: dbms_redefinition [message #524301 is a reply to message #524297]

Thu, 22 September 2011 15:46

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

I posted this as a possible bug on the OTN Text forum:

https://forums.oracle.com/forums/thread.jspa?threadID=2288541&tstart=0

Report message to a moderator

Re: dbms_redefinition [message #524323 is a reply to message #524301]

Thu, 22 September 2011 23:52

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

Thanks alot Mam, I really appreciate your help.

Regards
Deepak

Report message to a moderator

Re: dbms_redefinition [message #524330 is a reply to message #524301]

Fri, 23 September 2011 00:55

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

Hello Mam,

I followed your test case but when are dropping the index and re-creating it second time followed the redefinition of the column in executed the script and came across this error.

DECLARE
*
ERROR at line 1:
ORA-42011: error occurred while completing the redefinition
ORA-12008: error in materialized view refresh path
ORA-29886: feature not supported for domain indexes
ORA-06512: at "SYS.DBMS_REDEFINITION", line 76
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1376
ORA-06512: at line 31

Now what is wrong with the code?

Thanks n Regards
Deepak

Report message to a moderator

Re: dbms_redefinition [message #524440 is a reply to message #524330]

Fri, 23 September 2011 12:28

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Please post a copy and paste of a run of what you did from SQL*Plus, complete with line numbers, as I did.

Report message to a moderator

Re: dbms_redefinition [message #524442 is a reply to message #524301]

Fri, 23 September 2011 12:49

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

I received the following response to my post on the OTN forums, so if you have Oracle support, you can look up the bugs for any patches or workarounds.

"yes, we have bug 10355282 and bug 8660944 for the dictionary metadata inconsistency after "ALTER INDEX ... REBUILD REPLACE" command"

Report message to a moderator

Previous Topic:	enable synchronization of full-text index
Next Topic:	Full-Text Search [intermedia]

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 22:06:52 CST 2024