Home » Server Options » Replication » ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES (windows2003 + oracle 10g)
ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #376066] Mon, 15 December 2008 22:05 Go to next message
darren_tao
Messages: 10
Registered: December 2008
Location: Tokyo
Junior Member
hi, all

In our system ,we have two db server which are main and sub. and use advance replication to keep the two db same. but some times there are errors in the procedure of advance repliaction. so we try to use DBMS_RECTIFIER_DIFF to handle the errors.

but when we run DBMS_RECTIFIER_DIFF.DIFFERENCES procedure on a table there are always ORA-06502 exceptions. the others is no problem. the only difference is the table is a little big, about 200 columns. and some columns length is a little large(420 bytes).

what should i do. how to solve the problem?
is there any useful informations, thanks very much.

environment: windows2003 + oracle 10g

information:
BEGIN
DBMS_RECTIFIER_DIFF.DIFFERENCES (
sname1 => 'admin',
oname1 => 'tb1',
reference_site => 'main',
sname2 => 'admin',
oname2 => tb1',
comparison_site => 'sub',
where_clause => '',
column_list => NULL,
missing_rows_sname => 'DIFF',
missing_rows_oname1 => 'Md_tb1',
missing_rows_oname2 => 'Mr_tb1',
missing_rows_site => 'main',
max_missing => 10000000,
commit_rows => 5);
END;

Error report:
ORA-06502: PL/SQL: numeric or value error: character string buffer too smallORA-06512:
"SYS.DBMS_RECTIFIER_DIFF", 行1631
ORA-06512: "SYS.DBMS_RECTIFIER_DIFF", 行1222
ORA-06512: 行2
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:

[Updated on: Mon, 15 December 2008 22:06]

Report message to a moderator

Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #376084 is a reply to message #376066] Tue, 16 December 2008 00:20 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know anything about the package you are using; however, just in case you didn't find the error, here it is:
Oracle
ORA-06502 PL/SQL: numeric or value error string

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

Now, is the "character string buffer too small" message related to your table or use of the package itself, I wouldn't know.

Sorry for not being able to help you any better.
Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #376100 is a reply to message #376084] Tue, 16 December 2008 01:02 Go to previous messageGo to next message
darren_tao
Messages: 10
Registered: December 2008
Location: Tokyo
Junior Member
anyway thanks very much.
the procedure is provided by oracle.
it is used to find the difference between two tables and save the differences to a table for rectify.

and i wonder wether there is a way to get some runtime information which maybe can make me find some details. such as which column.
Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #376238 is a reply to message #376100] Tue, 16 December 2008 09:57 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Hello,

Provide the below details.

1/ database version (Need 4 decimal)

2/ Trace File details

I also faced same problem. Find here for more detalis

Babi
Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #376298 is a reply to message #376238] Tue, 16 December 2008 20:02 Go to previous messageGo to next message
darren_tao
Messages: 10
Registered: December 2008
Location: Tokyo
Junior Member
thanks very much, gentlebabu

database version:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

about trace file, when i execute the procedure,i can not get any trace file. i don't know why.

some one tells me that it is a bug, but have not provide more detail information.

please see
http://dbaforums.org/oracle/index.php?s=af6a25b1f89eaa680d95883145748e96&showtopic=17064&st=0&#entry46870

Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #376498 is a reply to message #376298] Wed, 17 December 2008 10:38 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Thanks for your details.

1/ Please confirm me SYS objects everything valid (Replication related & also replication option enabled in your database.

2/ Also the above PL/SQL Script running from SYS or RepAdmin user??

Babu

[Updated on: Wed, 17 December 2008 10:40]

Report message to a moderator

Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #376561 is a reply to message #376498] Wed, 17 December 2008 19:17 Go to previous messageGo to next message
darren_tao
Messages: 10
Registered: December 2008
Location: Tokyo
Junior Member
Thanks Babu

>>1/ Please confirm me SYS objects everything valid
I don't think that there is something wrong with sys object as it works correctly on some other tables.


>>Also the above PL/SQL Script running from SYS or RepAdmin user??
I ran the script in repadmin user
and I tried the system user, the same wrong.

Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #376728 is a reply to message #376561] Thu, 18 December 2008 06:44 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


ASAIK; ORA-06502 bugs fixed in 10.2.0.4


>>I don't think that there is something wrong with sys object as it works correctly on some other tables.


As I checked metalink check any replication objects become invalid.

Babu
Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #376840 is a reply to message #376728] Thu, 18 December 2008 18:08 Go to previous messageGo to next message
darren_tao
Messages: 10
Registered: December 2008
Location: Tokyo
Junior Member
Thank you very much, Babu

>>ASAIK; ORA-06502 bugs fixed in 10.2.0.4
i guess ORA-06502 error must happen in different situations. is the error i met fixed? the problem is that i can not diff it is my fault(config error .etc) or oracle's bug.

>> As I checked metalink check any replication objects become invalid.

sorry, I am not very clear about "check any replication objects become invalid.". what does it mean?

in fact i think that the procedure can not only run in replication environment, when there is no replication, i also can use it to get the difference between two tables. i will test it with out a replication environment later.

thanks again for the help

[Updated on: Thu, 18 December 2008 18:18]

Report message to a moderator

Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #376994 is a reply to message #376840] Fri, 19 December 2008 06:26 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Please refer: 106206.1

Babu
Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #377080 is a reply to message #376994] Fri, 19 December 2008 23:51 Go to previous messageGo to next message
darren_tao
Messages: 10
Registered: December 2008
Location: Tokyo
Junior Member
I test the procedure on two tables that with out replication, and succeed.

it seems that some thing wrong with my repliaction object.

by the way, as the coustomer request, the procedure is ran with replication enabled. and it is against the oracle's suggestion. maybe it leads to the error.

but when i tried to suspend the replication and then run the procedure, the same error.

by the way, to the parameter comparison_site of the procedure, what should i use? i mean db_name, service_name or some else?
description in the document is 'Name of the comparison database site. ' . not very clear about 'database site name'.

pfile as bleow:

compatible=10.2.0.1.0
db_unique_name=mitaka_test
db_domain=""
db_name=mitaka_test

[Updated on: Sat, 20 December 2008 00:55]

Report message to a moderator

Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #377086 is a reply to message #376994] Sat, 20 December 2008 01:06 Go to previous messageGo to next message
darren_tao
Messages: 10
Registered: December 2008
Location: Tokyo
Junior Member
comment:
sorry i made a mistake in the last reply
db_name=mitaka_t

and
i tried "mitaka_test" and "mitaka_t", but always got the error "mitaka_t(est) is not exist". i don't know why, the two db are on the same server.

[Updated on: Sat, 20 December 2008 01:10]

Report message to a moderator

Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #377090 is a reply to message #377086] Sat, 20 December 2008 01:37 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Hello,

Quote:
database version:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod



Quote:
compatible=10.2.0.1.0
db_unique_name=mitaka_test
db_domain=""
db_name=mitaka_test


Requesting to you. Confirm me your database version.

Babu
Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #377092 is a reply to message #377090] Sat, 20 December 2008 02:01 Go to previous messageGo to next message
darren_tao
Messages: 10
Registered: December 2008
Location: Tokyo
Junior Member
sorry, i did not tell it clear. as i can not change the test database environment because many others use it.

so i used a old db server just to take a try just now.

thanks!

[Updated on: Sat, 20 December 2008 02:01]

Report message to a moderator

Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #377104 is a reply to message #377092] Sat, 20 December 2008 03:35 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
as i can not change the test database environment because many others use it.


I'm NOT requesting to you change database environment.

Please post below details from your database

1/ select * from v$version;

2/ show parameter compatible

Babu
Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #377107 is a reply to message #377104] Sat, 20 December 2008 03:46 Go to previous messageGo to next message
darren_tao
Messages: 10
Registered: December 2008
Location: Tokyo
Junior Member
>>1/ select * from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
"CORE 10.2.0.1.0 Production"
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

>>2/ show parameter compatible
compatible string 10.2.0.1.0


thanks.
Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #377119 is a reply to message #377107] Sat, 20 December 2008 08:24 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Requesting to you; Install latest patch/cpu patch..

Babu
Re: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES [message #377212 is a reply to message #377119] Sun, 21 December 2008 19:21 Go to previous message
darren_tao
Messages: 10
Registered: December 2008
Location: Tokyo
Junior Member
hi, babu

thanks, how about the weekend?

i will try to update it later.

but as to the original question, about the ORA-06502, it occurs on the version 10.2.0.4.0.

maybe i will ask my leader to open oracle service and get support from oracle.

Darren
Previous Topic: What does "WITH SEQUENCE" clause in "CREATE SNAPSHOT LOG" statement do?
Next Topic: Refresh snapshot data without logging (merged 3)
Goto Forum:
  


Current Time: Fri Mar 29 05:44:46 CDT 2024