Re: I have a black hole in my database

I remember once a designer package compiled forever, it just hang up ...

I looked in the previous release of Designer and found that the code has been changed although the meaning was really the same. So I took some 5-10 lines from previous version and voila! - it worked. Later this was registered as a bug :-)

Gints Plivna
IT Sistēmas, Merķeļa 13, LV1050 Rīga
http://www.itsystems.lv/gints/

                                                                                                                                        
                      Simon.Anderson_at_sc                                                                                                 
                      isys.co.uk               To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                    
                      Sent by:                 cc:                                                                                      
                      root_at_fatcity.com         Subject:  Re: I have a black hole in my database                                         
                                                                                                                                        
                                                                                                                                        
                      2002.05.10 13:53                                                                                                  
                      Please respond to                                                                                                 
                      ORACLE-L                                                                                                          
                                                                                                                                        
                                                                                                                                        

You have my sympathy, as I've been in a similar situation after getting a designer install thrown at me.
I can only offer general advice, as we hit a series of different bugs that eventually led to abandoning designer, you've probably heard it all from Oracle
support already...

Make sure you're using the latest patch release of 6i, you didn't specify which
one you're on. Half of our problems were in finding the right release to work
with our 8.1.7.0 database.

It really does need all those separate tablespaces, on separate disks if you've
got them (I hadn't appreciated quite how big the product was until it was too
late to turn down the job).

Keep chasing Oracle support without mercy - don't let them park the call if you
can avoid it, keep their clock ticking. I don't know how responsive their support is where you are, but it takes a while to get past the front line in the
UK...

You could try creating an explain plan of the Select that the view uses, and
running the statements themselves without making them into a view might get you
something traceable, but it's a long shot.

Hope This Helps
Simon Anderson.

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Simon Anderson/SSplc)

Oracle Version 8.1.7.3 64-bit
OS Solaris 8 10/1
Server platform Sunfire 38000
Client Platform WIN2k

I have somehow created a black hole in my database. While installing designer 6i my process freezes while creating the RM_ELEMENTS view. I do not
have any errors in my alert logs. If I create a new SQL*PLUS session it hangs before logging in. If I bring up DBA Studios it hangs. My only recover
so far has been to shutdown abort the instance.

If I run the 'cdapirvw.sql' script (this creates the views) it goes to end of job successfully. No errors. If I then run something simple like 'select count(*) from rm_elements' the instance hangs. If I attempt to grant rm_elements to a role the instance hangs. I can descibe rm_elements successfully. I can drop it.

The create view statement for rm_elements is incredibly long. here is an example
CREATE OR REPLACE FORCE VIEW rm_elements AS

SELECT sdd_a1.changed_by


      ,sdd_a1.created_by

      ,sdd_a1.date_changed

      ,sdd_a1.date_created

      ,sdd_a1.irid id

      ,sdd_a1.notm number_of_times_modified

      ,sdd_a1.types

FROM   sdd_a1 sdd_a1
  UNION ALL
SELECT sdd_a2.changed_by


      ,sdd_a2.created_by

      ,sdd_a2.date_changed

      ,sdd_a2.date_created

      ,sdd_a2.irid id

      ,sdd_a2.notm number_of_times_modified

      ,sdd_a2.types

FROM   sdd_a10 sdd_a2
  UNION ALL
SELECT sdd_a3.changed_by


      ,sdd_a3.created_by

      ,sdd_a3.date_changed

      ,sdd_a3.date_created

      ,sdd_a3.irid id

      ,sdd_a3.notm number_of_times_modified

      ,sdd_a3.types

FROM   sdd_a11 sdd_a3

There are about 30 or 40 unions at least in this view. The command is a couple of 100 lines long. Has anyone had a problem like this or a suggestion
I could pursue? I am working with Oracle support but have not had a resolution yet.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: Simon.Anderson_at_scisys.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: G.Plivna_at_itsystems.lv

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).