-
ORA-22813: operand value exceeds system limits
Hi all,
I have a query which runs fine on our dev db, but when the query is run on the live box i am getting the following error:
ORA-22813: operand value exceeds system limits
The query is as follows:
SELECT ap.HOUSENUMBERNAME, ap.POSTCODE, a.BLOCKPROMO, a.BUSINESSIND from (
SELECT
PKG_OSAP_SUPPORT.getAddressString(a.organisation_name, a.department,
a.po_box_number, a.sub_building_name, a.building_name, a.building_number,
a.dep_thoroughfare, a.thoroughfare, a.dbl_dep_locality, a.dep_locality) || ',' |
| a.post_town as housenumbername,
a.postcode
FROM TARGETAREA@EAAPLink t0 , ADDRESS_POINT_001 a
WHERE t0.TARGETAREAID = 'c84a7c650a051e2200624da79f1bcb99'
AND sdo_relate(a.GEOMETRY, t0.SHAPE, 'mask=anyinteract querytype=window') = 'TRU
E') ap
LEFT OUTER JOIN ADDRESS@EAAPLink a ON AP.HOUSENUMBERNAME = A.HOUSENUMBERNAME AND
AP.POSTCODE = A.POSTCODE
The error i get is:
SQL> @prob.sql
11 ;
FROM TARGETAREA@EAAPLink t0 , ADDRESS_POINT_001 a
*
ERROR at line 7:
ORA-22813: operand value exceeds system limits
I checked all config parameters on both dbs. Both are the same. The only difference is TARGETAREA@EAAPLink (which is a table in a different db and is used as a db link) has ~1000 rows in the dev db and ~5000 rows in the live db. Can this be the cause of the failure? and if so what can i do to rectify the error?
Thanks in advance,
Chucks
-
The Collection value from your inner sub query has exceeded the system limits.
Try with lesser value and re-execute the query.
Also you can match the records on both tables to sort the issues.
"What is past is PROLOGUE"
-
I cannot change the query as the data is required. Are there any init.ora parameters i need to look at?..As i said before, the targetareaid table has 4000 rows and the same table on the dev db has 1000 rows. I increased sort_area_size and hash_area_size for my session to no avail!.
Thanks in advance,
Chucks
-
You may be hitting bug 2385206 or bug 2508513.
Contact Oracle Support.
Are you using PGA ?
Try with DBMS PIPE. I am not sure this will solve the problem.
Tamil
Last edited by tamilselvan; 02-02-2006 at 04:28 PM.
-
Hi Tamil,
Thanks for your response. We are using PGA and have set the pga_aggregate_target to 25165824. I will have the bug ids you have given. Thank You for that. How would you use the DBMS_PIPE in conjuction with the query?.
Thanks in advance,
Chucks
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|