Re: I have a black hole in my database

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).