DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: ORA-22813: operand value exceeds system limits

  1. #1
    Join Date
    Dec 2001
    Posts
    337

    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

  2. #2
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    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"

  3. #3
    Join Date
    Dec 2001
    Posts
    337
    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

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

  5. #5
    Join Date
    Dec 2001
    Posts
    337
    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
  •  


Click Here to Expand Forum to Full Width