Home » Server Options » Text & interMedia » ORA-29849 exception (Oracle 11g R2)
ORA-29849 exception [message #641034] Mon, 10 August 2015 01:38 Go to next message
sherlocksher
Messages: 14
Registered: June 2013
Junior Member
Hi,

While doing a split partition operation, an exception with error code ORA-29489 ( error occurred in the execution of ODCIINDEXSPLITPARTITION routine) was logged. The sequence of steps is given below. As seen from the below sequence, in Step 4, an ORA-29489 error was logged in our exception table. Unfortunately there is no real time monitoring and we noticed this error only after a couple of days. Googling for more info about this error states that the action item is to verify if the routine has been coded properly and there is no additional info. Has any one encountered this error ? Is there a way to find out why this happened and what needs to be done to prevent it from happening again. Any pointers would be really helpful.

Note: However when the same step i.e Step 4 was re-run, it ran successfully.


--Step 1 Create table
CREATE TABLE test
(project_id number, project_name varchar2(1000), event_text clob, accum_text clob)
PARTITION BY RANGE (project_id)
(PARTITION pmax VALUES LESS THAN (MAXVALUE));

--Step 2 create preferences for context search
begin
ctx_ddl.create_preference ('skipJoinLexer1', 'BASIC_LEXER');
ctx_ddl.set_attribute ('skipJoinLexer1', 'skipjoins', ';:.,()<>*"''#-');
ctx_ddl.create_preference ('TextDS', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute ('TextDS', 'COLUMNS', 'EVENT_TEXT, ACCUM_TEXT');

END;

--Step 3 create local domain indexe

create index TEST_IDX on test (event_text) indextype is ctxsys.context parameters ('lexer skipJoinLexer1 datastore TextDS') LOCAL;


--Step 4 - Failed Step
ALTER TABLE test split PARTITION pmax AT (2) INTO ( PARTITION p_1, PARTITION pmax)


Thanks,
Vijay

[Updated on: Mon, 10 August 2015 01:55]

Report message to a moderator

Re: ORA-29849 exception [message #641040 is a reply to message #641034] Mon, 10 August 2015 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know why you have this error but it seems logical to me to create the index AFTER splitting the partition or drop/make unusable the index (partition), split the partition and recreate/rebuild the index (partitions).

Re: ORA-29849 exception [message #641041 is a reply to message #641034] Mon, 10 August 2015 02:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
I am unable to reproduce the problem, even with adding some data, as shown below. Can you post a reproducible test case or a run of the code that you posted that shows that it errors on your system?

SCOTT@orcl> SELECT banner FROM v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.

SCOTT@orcl> CREATE TABLE test
  2    (project_id    number,
  3  	project_name  varchar2(1000),
  4  	event_text    clob,
  5  	accum_text    clob)
  6  PARTITION BY RANGE (project_id)
  7    (PARTITION pmax VALUES LESS THAN (MAXVALUE))
  8  /

Table created.

SCOTT@orcl> insert all
  2  into test values (1, 'name1', 'event1', 'accum1')
  3  into test values (3, 'name3', 'event3', 'accum3')
  4  select * from dual
  5  /

2 rows created.

SCOTT@orcl> commit
  2  /

Commit complete.

SCOTT@orcl> COLUMN high_value FORMAT A30
SCOTT@orcl> SELECT partition_name, high_value
  2  FROM   user_tab_partitions
  3  WHERE  table_name = 'TEST'
  4  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
PMAX                           MAXVALUE

1 row selected.

SCOTT@orcl> begin
  2    ctx_ddl.create_preference ('skipJoinLexer1', 'BASIC_LEXER');
  3    ctx_ddl.set_attribute ('skipJoinLexer1', 'skipjoins', ';:.,()<>*"''#-');
  4    ctx_ddl.create_preference ('TextDS', 'MULTI_COLUMN_DATASTORE');
  5    ctx_ddl.set_attribute ('TextDS', 'COLUMNS', 'EVENT_TEXT, ACCUM_TEXT');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl> create index TEST_IDX on test (event_text) indextype is ctxsys.context
  2  parameters ('lexer skipJoinLexer1 datastore TextDS') LOCAL
  3  /

Index created.

SCOTT@orcl> insert all
  2  into test values (2, 'name2', 'event2', 'accum2')
  3  into test values (4, 'name4', 'event4', 'accum4')
  4  select * from dual
  5  /

2 rows created.

SCOTT@orcl> ALTER TABLE test split PARTITION pmax AT (2) INTO ( PARTITION p_1, PARTITION pmax)
  2  /

Table altered.

SCOTT@orcl> SELECT partition_name, high_value
  2  FROM   user_tab_partitions
  3  WHERE  table_name = 'TEST'
  4  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
P_1                            2
PMAX                           MAXVALUE

2 rows selected.

SCOTT@orcl> ALTER INDEX test_idx REBUILD PARTITION p_1
  2  /

Index altered.

SCOTT@orcl> ALTER INDEX test_idx REBUILD PARTITION pmax
  2  /

Index altered.

SCOTT@orcl> SELECT event_text FROM test WHERE CONTAINS (event_text, 'event%') > 0
  2  /

EVENT_TEXT
--------------------------------------------------------------------------------
event1
event3
event2
event4

4 rows selected.

[Updated on: Mon, 10 August 2015 02:24]

Report message to a moderator

Re: ORA-29849 exception [message #641042 is a reply to message #641041] Mon, 10 August 2015 02:41 Go to previous messageGo to next message
sherlocksher
Messages: 14
Registered: June 2013
Junior Member
Thanks for your replies. The issue is we are unable to reproduce the error again in spite of multiple attempts Sad. As I mentioned in the original post, the same split partition operation that failed worked when it was done the next time. That is why was trying to get some "generic" pointers to see what could have caused this error
Re: ORA-29849 exception [message #641130 is a reply to message #641040] Tue, 11 August 2015 00:34 Go to previous message
sherlocksher
Messages: 14
Registered: June 2013
Junior Member
@Michael Cadot


"I don't know why you have this error but it seems logical to me to create the index AFTER splitting the partition or drop/make unusable the index (partition), split the partition and recreate/rebuild the index (partitions)."

a. We cannot create the index after splitting the partition because creation of partitions is not a one step process in our application. For every record that is put into a Master table, we have to creation partitions in our Test table.

b. I tried the way you suggested . I made the main partition to be unusable and then split it . After split I noticed both the partitioned indexes i.e the main partition and the local index have been marked as usable even though I did not explicitly rebuild local indexes !!.

c. Also, can you please explain on why this is logical i.e we have to make the partition unusable, split it and then rebuild it.

--Step 1 - Create the table

CREATE TABLE test
(project_id number, project_name varchar2(1000), event_text clob, accum_text clob)
PARTITION BY RANGE (project_id)
(PARTITION pmax VALUES LESS THAN (MAXVALUE));

-- Step 2 create index ( Am skipping the lexer creation and data store steps)
create index TEST_IDX on test (event_text) indextype is ctxsys.context parameters ('lexer skipJoinLexer1 datastore TextDS') LOCAL;

--Step 3 - Made the main partition unusable

alter index test_idx modify partition pmax unusable;


--Step 4 - Splitting the partition

ALTER TABLE test split PARTITION pmax AT (2) INTO ( PARTITION p_1, PARTITION pmax)

--Step 5 -- Both indexes have been marked as usable!!!
select * from user_ind_partitions where index_name = 'TEST_IDX'
Previous Topic: Searching Synonyms
Next Topic: Oracle Text tuning
Goto Forum:
  


Current Time: Tue Nov 21 06:10:45 CST 2017

Total time taken to generate the page: 0.10485 seconds