Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query from Hell! - ora-01555

Re: Query from Hell! - ora-01555

From: Michael McMullen <ganstadba_at_hotmail.com>
Date: Wed, 15 Nov 2006 15:44:55 -0500
Message-ID: <BAY103-DAV1677A199FF382DE769B79AA6EA0@phx.gbl>


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_at_freelists.org
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
(

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
Received on Wed Nov 15 2006 - 14:44:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US