I have a black hole in my database

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: Alec Macdonell
  INET: amacdonell_at_usscript.com

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