Oracle Database Discussions

Hi,

The below query taking too much temp space and getting the error "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP. Please help


select pbg.name business_group_name
,papf.employee_number oracle_hr_id
,papf.full_name person_full_name
,ppos.date_start date_of_joining
,pgd.segment3 band
,odb.tdb_information3 Status
,haou.name organization_name
,papf_mgr.employee_number manager
,papf_mgr.full_name manager_name
-- ,(select ocu1.category
-- from apps.ota_category_usages ocu1
-- where type = 'C'
-- and ocu1.parent_cat_usage_id=28
-- start with ocu1.category = ocu.category
-- and ocu1.category_usage_id = ocu.category_usage_id
-- connect by prior ocu1.parent_cat_usage_id =
-- ocu1.category_usage_id
-- and rownum < 2) category
,ocut.category sub_category
-- ,NVL(DECODE(PAAF.ASS_ATTRIBUTE_CATEGORY,81,PAAF.ASS_ATTRIBUTE28,SUBSTR(PAAF.ASS_ATTRIBUTE24,1,8)),'NA') PROCESS_ID
,oavt.version_name course_name
,oav.start_date course_start_date
,oav.end_date course_end_date
,oet.title class_name
,oe.course_start_date class_start_date
,oe.course_end_date class_end_date
,oot.name offering_name
,ocut_dm.category delivery_mode
,odb.booking_id booking_id
,hla.town_or_city location
,fcl_cou.meaning country
,oe.duration||' '||fcl_odu.meaning class_duration
,obstt.name program_status
-- ,decode(obstt.name,'Enroll',odb.date_booking_placed,
-- (select max(obsh.start_date)
-- from apps.ota_booking_status_histories obsh
-- ,apps.ota_booking_status_types obst1
-- where 1=1
-- and obsh.booking_id=odb.booking_id
-- and obst1.booking_status_type_id=obsh.booking_status_type_id
-- and obst1.name='Enroll'
-- )
-- )enrollment_date
,fcl_odb.meaning Failure_Reason
from apps.per_all_assignments_f paaf
,apps.per_periods_of_service ppos
,apps.per_all_people_f papf_mgr
,apps.per_all_people_f papf
,apps.per_grades pg
,apps.per_grade_definitions pgd
,apps.ota_delegate_bookings odb
,apps.ota_events_tl oet
,apps.ota_events oe
,apps.ota_offerings_tl oot
,apps.ota_offerings oo
,apps.ota_act_cat_inclusions oaci
,apps.ota_activity_versions_tl oavt
,apps.ota_activity_versions oav
,apps.ota_category_usages_tl ocut_dm
,apps.ota_category_usages ocu_dm
,apps.ota_category_usages_tl ocut
,apps.ota_category_usages ocu
,apps.ota_booking_status_types obst
,apps.ota_booking_status_types_tl obstt
,apps.hr_all_organization_units haou
,apps.hr_locations_all hla
,apps.per_business_groups pbg
,apps.fnd_common_lookups fcl_cou
,apps.fnd_common_lookups fcl_odb
,apps.fnd_common_lookups fcl_odu
where 1=1
and fcl_odu.lookup_type(+)='OTA_DURATION_UNITS'
and fcl_odu.lookup_code(+)=oe.duration_units
and fcl_odb.lookup_type (+)= 'DELEGATE_FAILURE_REASON'
and fcl_odb.lookup_code(+) = odb.failure_reason
and fcl_cou.lookup_type(+)='PER_US_COUNTRY_CODE'
and fcl_cou.lookup_code(+)=hla.country
and pbg.business_group_id=ocu.business_group_id
and hla.location_id=paaf.location_id
and haou.organization_id=paaf.organization_id
and obst.booking_status_type_id=obstt.booking_status_type_id
and obstt.booking_status_type_id=odb.booking_status_type_id
and obstt.language=USERENV('LANG')
and ocu.category_usage_id=ocut.category_usage_id
and ocu.category_usage_id=oaci.category_usage_id
and ocut.language=USERENV('LANG')
and ocu_dm.category_usage_id=ocut_dm.category_usage_id
and ocu_dm.category_usage_id=oo.delivery_mode_id
and ocut_dm.language=USERENV('LANG')
and oav.activity_version_id=oavt.activity_version_id
and oav.activity_version_id=oaci.activity_version_id
and oav.activity_version_id=oo.activity_version_id
and oavt.language=USERENV('LANG')
and oo.offering_id=oot.offering_id
and oot.language=USERENV('LANG')
and oo.offering_id=oe.parent_offering_id
and oe.event_id=oet.event_id
and oe.event_id=odb.event_id
and oet.language=USERENV('LANG')
and pgd.grade_definition_id=pg.grade_definition_id
and pg.grade_id=paaf.grade_id
and papf.person_id=odb.delegate_person_id
and papf.person_id=paaf.person_id
and papf.effective_end_date=(select max(papf11.effective_end_date)
from apps.per_all_people_f papf11
where 1 = 1
and papf.person_id = papf11.person_id
)
and papf_mgr.person_id=paaf.supervisor_id
and papf_mgr.effective_end_date=(select max(papf1_mgr.effective_end_date)
from apps.per_all_people_f papf1_mgr
where 1 = 1
and papf1_mgr.person_id = papf_mgr.person_id
)
and ppos.period_of_service_id=paaf.period_of_service_id
and paaf.primary_flag='Y'
and paaf.effective_end_date=(select max(paaf1.effective_end_date)
from apps.per_all_assignments_f paaf1
where 1 = 1
and paaf.assignment_id = paaf1.assignment_id
)
AND ocu.category_usage_id = (select ocu1.category_usage_id --ocu1.category
from apps.ota_category_usages ocu1
where type = 'C'
and ocu1.parent_cat_usage_id = 325296
start with ocu1.category = ocu.category
and ocu1.category_usage_id = ocu.category_usage_id
connect by prior ocu1.parent_cat_usage_id = ocu1.category_usage_id
and rownum < 2
)


Regards,
Bhatia