Home » Server Options » Text & interMedia » ORA-29849 exception (Oracle 11g R2)
| ORA-29849 exception [message #641034] |
Mon, 10 August 2015 01:38  |
 |
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 #641041 is a reply to message #641034] |
Mon, 10 August 2015 02:19  |
 |
Barbara Boehmer
Messages: 9101
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 #641130 is a reply to message #641040] |
Tue, 11 August 2015 00:34 |
|
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'
|
|
|
|
Goto Forum:
Current Time: Thu Dec 26 09:13:23 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
. 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