6 Replies Latest reply on Aug 11, 2015 2:51 PM by Bashar.

    Getting ORA-01652-unable to extend temp segment by 128 error while running the report from application

    user13424229

      Dear All,

       

      I have custom XML publisher report when i am running the report as a concurrent program it is giving below error.

       

      ORA-01652-unable to extend temp segment by 128 in tablespace TEMP.

       

      Note:

      This report is working fine across all instance it is only failing in production. i tried to run the report query from backend in production it is running fine but giving the error from application.

      I tried to check the allocated table space but it is having enough space but not sure why this error is coming please help me out.

       

       

      Thnaks

        • 1. Re: Getting ORA-01652-unable to extend temp segment by 128 error while running the report from application
          Azar

          ask your admin to add temp datafile to temporary tablespace group

          • 2. Re: Getting ORA-01652-unable to extend temp segment by 128 error while running the report from application
            rioman

            I would also review the custom report. It sounds like a possible bug in the main query (or any other query) of the report, like intermediate operations generating a big amount of data. Review the query (or post it here). Check also the explain plan, to see what operations could be generating TEMP data.

            • 4. Re: Getting ORA-01652-unable to extend temp segment by 128 error while running the report from application
              Bashar.

              Hi,

               

              Maybe there is a contention on the temp tablespace on the production environment. This could be the reason why you only face it on production.

               

              Regards,

              Bashar

              • 5. Re: Getting ORA-01652-unable to extend temp segment by 128 error while running the report from application
                user13424229


                Thanks all for the reply,

                 

                Please find below my query

                 

                SELECT

                  mcb.description item_desc,

                  pas.status asl_item_status,

                  asp.vendor_name,

                  asp.segment1 supplier_number,

                  INITCAP (hpc.person_pre_name_adjunct)

                  || hpc.person_first_name

                  || ' '

                  || hpc.person_middle_name

                  || ' '

                  || hpc.person_last_name contact_name,

                  hoc.job_title job_title,

                  hoc.department department,

                  hcpe.email_address,

                  hcpp.phone_number,

                  hcpf.phone_number fax_number,

                  hr.end_date,

                  hcppa.phone_number alt_phone_number,

                  (SELECT pos_profile_change_request_pkg.format_address (hl.address1, hl.address2, hl.address3, hl.address4, hl.city, hl.postal_code, hl.state, hl.province, hl.county, hl.country ) address_details

                  FROM hz_party_sites hps,

                    hz_locations hl,

                    hz_relationships hzr

                  WHERE hps.location_id                   = hl.location_id

                  AND NVL (hps.end_date_active, SYSDATE) >= SYSDATE

                  AND hzr.relationship_type               = 'CONTACT'

                  AND hzr.relationship_code               = 'CONTACT_OF'

                  AND hzr.subject_type                    = 'PERSON'

                  AND hzr.subject_table_name              = 'HZ_PARTIES'

                  AND hzr.object_type                     = 'ORGANIZATION'

                  AND hzr.object_table_name               = 'HZ_PARTIES'

                  AND hzr.status                          = 'A'

                  AND hps.party_id                        = hzr.object_id

                  AND EXISTS

                    (SELECT 1

                    FROM ap_supplier_contacts ascs

                    WHERE ( ascs.inactive_date IS NULL

                    OR ascs.inactive_date       > SYSDATE )

                    AND hzr.relationship_id     = ascs.relationship_id

                    AND hzr.party_id            = ascs.rel_party_id

                    AND hps.party_site_id       = ascs.org_party_site_id

                    AND hzr.subject_id          = ascs.per_party_id

                    )

                  AND hzr.subject_id = hpc.party_id --20830

                  AND hps.party_id   = asp.party_id

                  ) address_details,

                  MCB.segment1 major,

                  MCB.segment2 minor

                FROM hz_parties hpc,

                  hz_contact_points hcpp,

                  hz_contact_points hcpf,

                  hz_contact_points hcpe,

                  hz_relationships hr,

                  hz_org_contacts hoc,

                  hz_contact_points hcppa,

                  hz_contact_points hcppw,

                  hz_parties hpr,

                  ap_suppliers asp,

                  po_approved_supplier_list pasl,

                  po_asl_statuses pas,

                  mtl_system_items_b msib,

                  MTL_CATEGORY_SETS_TL CST,

                  MTL_ITEM_CATEGORIES MIC,

                MTL_CATEGORIES  MCB

                WHERE hcpp.owner_table_name(+)   = 'HZ_PARTIES'

                AND hcpp.owner_table_id(+)       = hr.party_id

                AND hcpp.phone_line_type(+)      = 'GEN'

                AND hcpp.contact_point_type(+)   = 'PHONE'

                AND hcpf.owner_table_name(+)     = 'HZ_PARTIES'

                AND hcpf.owner_table_id(+)       = hr.party_id

                AND hcpf.phone_line_type(+)      = 'FAX'

                AND hcpf.contact_point_type(+)   = 'PHONE'

                AND hcpe.owner_table_name(+)     = 'HZ_PARTIES'

                AND hcpe.owner_table_id(+)       = hr.party_id

                AND hcpe.contact_point_type(+)   = 'EMAIL'

                AND hcppw.owner_table_id(+)      = hr.party_id

                AND hcppw.owner_table_name(+)    = 'HZ_PARTIES'

                AND hcppw.status(+)              = 'A'

                AND hcppw.contact_point_type(+)  = 'WEB'

                AND hcppa.owner_table_id(+)      = hr.party_id

                AND hcppa.owner_table_name(+)    = 'HZ_PARTIES'

                AND hcppa.status(+)              = 'A'

                AND hcppa.contact_point_type(+)  = 'PHONE'

                AND hcppa.phone_line_type(+)     = 'GEN'

                AND hcppa.primary_flag(+)        = 'N'

                AND hr.object_id                 = hpc.party_id

                AND hr.subject_type              = 'ORGANIZATION'

                AND hr.subject_table_name        = 'HZ_PARTIES'

                AND hr.object_table_name         = 'HZ_PARTIES'

                AND hr.object_type               = 'PERSON'

                AND hr.relationship_code         = 'CONTACT'

                AND hr.directional_flag          = 'B'

                AND hr.relationship_type         = 'CONTACT'

                AND hr.party_id                  = hpr.party_id

                AND hcpe.status(+)               = 'A'

                AND hcpe.primary_flag(+)         = 'Y'

                AND hcpp.status(+)               = 'A'

                AND hcpp.primary_flag(+)         = 'Y'

                AND hcpf.status(+)               = 'A'

                AND hoc.party_relationship_id(+) = hr.relationship_id

                AND asp.party_id                 = hr.subject_id

                AND asp.vendor_id                = pasl.vendor_id

                AND pasl.asl_status_id           = pas.status_id

                AND msib.inventory_item_id       = pasl.item_id

                AND msib.organization_id         = pasl.owning_organization_id

                AND MIC.CATEGORY_ID              = MCB.CATEGORY_ID

                AND mic.category_set_id          = cst.category_set_id

                AND mic.organization_id          = msib.organization_id

                AND MIC.INVENTORY_ITEM_ID        = msib.inventory_item_id

                AND asp.vendor_id                = NVL (:PVENDOR, asp.vendor_id)

                AND to_char(mcb.segment1)        = NVL(to_char(:PMAJOR), to_char(mcb.segment1))

                AND to_char(mcb.segment2)        = nvl(to_char(:PMINOR),to_char(mcb.segment2))

                 

                 

                 

                Note: While investigating I got the below details when I am adding the where condition

                 

                AND to_char(mcb.segment1)        = NVL(to_char(:PMAJOR), to_char(mcb.segment1))

                AND to_char(mcb.segment2)        = nvl(to_char(:PMINOR),to_char(mcb.segment2))

                 

                It is giving me the issue when I am pasing parameter as 'NULL' without that query is running fine please help me to identify what is the significance of of the condition 'to_char(mcb.segment1)        = NVL(to_char(:PMAJOR), to_char(mcb.segment1))' adding to_char function before to that,please help me out.

                 

                Thanks

                • 6. Re: Getting ORA-01652-unable to extend temp segment by 128 error while running the report from application
                  Bashar.

                  Hi,

                   

                  Replace those two lines with the following:

                   

                  AND (to_char(mcb.segment1)        = to_char(:PMAJOR) or :PMAJOR is null)

                  AND (to_char(mcb.segment2)        = to_char(:PMINOR) or :PMINOR is null)

                   

                  Regards,

                  Bashar