Re: Query from Hell! - ora-01555

Are you saying the ORA-01555 is a result of delayed block cleanout? I say this because I see the commit before the query. If so, analyze the table or indexes that were changed before running the query.


Mike
----- Original Message ----- From: Paula Stankus
To: oracle-l@xxxxxxxxxxxxx
Sent: Wednesday, November 15, 2006 3:34 PM
Subject: Query from Hell! - ora-01555


Guys,

I have a query and even though I set transaction to a specific rollback segment, it gives me an error on another rollback segment.

I cannot cycle the instance at all to set rollback segment only to the larger one.

We are using Version 8.1.7. I have been thinking of using temporary tables. Here is what I am doing, any suggestions?:

commit;
set transaction use rollback segment large_rbs;
SELECT l.licensee_id
    , li.license_id
    , alis.fnc_get_licensee_name(l.licensee_id) "Licensee Name"
    , l.licensee_license_num "License Number"
, alis.fnc_get_address(CASE WHEN l.person_id IS NOT NULL THEN l.person_id
                                WHEN l.firm_id IS NOT NULL THEN l.firm_id
                                ELSE l.company_id
                                END -- Linked to ID
                         , 2 -- Address Type (Business)
, CASE WHEN l.person_id IS NOT NULL THEN 1 -- Person
                                WHEN l.firm_id IS NOT NULL THEN 2 -- Firm
                                ELSE 3 -- Core Company
                                END -- Link Table ID
, DECODE(l.company_id, NULL, 1, 0)) "Business Address" -- Is Person/Firm (1 yes person/firm, 0 CORE)
    , tc.tycl_cd "License TYCL"
    , tc2.tycl_cd "Appointment TYCL"
    , a.appointing_entity_id
--     , ae.appointing_entity_num "AE Number"
, (SELECT ae.appointing_entity_num FROM alis.appointing_entity ae WHERE ae.appointing_entity_id = a.appointing_entity_id) "AE Number"
    , alis.fnc_get_appt_entity_name(a.appointing_entity_id) "AE Name"
FROM alis.license li
  , alis.licensee l
  , alis.type_class_lk tc -- license tycl
  , alis.type_class_lk tc2 -- appointment tycl
  , alis.appointment a
--   , appointing_entity ae
WHERE li.licensee_id = l.licensee_id
AND   li.type_class_lk_id = tc.type_class_lk_id
AND tc.type_class_lk_id IN (SELECT tci.type_class_lk_id FROM type_class_lk tci WHERE tci.type_class_group_lk_id NOT IN (1, 5) AND tci.profession_lk_id = 100 AND tci.tycl_ds NOT LIKE '%NONRES%' AND tci.tycl_ds NOT LIKE '%SURPLUS%' AND tci.type_class_lk_id NOT IN (999) AND tci.tycl_ds NOT LIKE '%NON-RES%') -- All Insurance TYCls but adjuster, non-resident, surplus and agency
AND   li.profession_lk_id = 100 -- Insurance
AND   l.profession_lk_id = 100 -- Insurance
AND   a.profession_lk_id = 100 -- Insurance
--AND   ae.profession_lk_id = 100 -- Insurance
AND   li.license_id = a.license_id
--AND   a.appointing_entity_id = ae.appointing_entity_id
AND   a.type_class_lk_id = tc2.type_class_lk_id
AND
(
 -- Valid today, issued before 9/1/2006, and hasn't changed since 9/1/2006
 (       li.status_reason_lk_id IN (1, 2) -- VALID, VALID - PROBATION
   AND   li.license_orig_issue_dt <= TO_DATE('9/2/2006', 'MM/DD/YYYY')
   AND   li.license_status_dt <= TO_DATE('9/2/2006', 'MM/DD/YYYY')
 )
 OR
 (
   -- Check history table to see if valid on 9/1/2006
   li.license_id IN
   (
     SELECT t.history_linked_to_id
     FROM  history t
     WHERE t.history_category_lk_id = 4 -- License Status
     AND   t.link_table_lk_id = 6 -- License
     AND   t.former_value IN ('VALID', 'VALID - PROBATION')
     AND   t.former_effective_dt < TO_DATE('9/2/2006', 'MM/DD/YYYY')
     AND   t.new_effective_dt >= TO_DATE('9/2/2006', 'MM/DD/YYYY')
   )
 )
)

Thanks,
Paula


Sponsored Link

Mortgage rates near 39yr lows. $510,000 Mortgage for $1,698/mo - Calculate new house payment
--
http://www.freelists.org/webpage/oracle-l


Other related posts: