Re: How to deal with objects of type UNDEFINED

From: Mark D Powell <mark.powell_at_eds.com>
Date: 12 Jul 2002 11:07:25 -0700
Message-ID: <178d2795.0207121007.1ed2bc63@posting.google.com>

Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3D2E0EAE.9EA1E132_at_exesolutions.com>...
> Rick Denoire wrote:
>
> > Hello
> >
> > Trying to glance at the consistency of the DB I found objects having
> > the same name in the same schema. One of these (always pairwise
> > present) objects was of type UNDEFINED (Version 8.1.7, Solaris).
> >
> > It turns out that the type# of these objects is 42, and it seems like
> > the DB is not able to deal with these objects. A number of questions
> > arise at this point:
> >
> > 1) Is it possible to have more than one object with the same name in
> > the same schema but of different type? One should say YES, since that
> > is what I have found, but this case seems bogous to me.
> >
> > 2) Could it be that these UNDEFINED objects are being unintentionally
> > addressed by SQL statements, when actually the other object with the
> > same name is meant?
> >
> > 3) Type# 42 means MATERIALIZED VIEW. Should I dare to teach this to
> > Oracle? There should be a file somewhere containing the correspondence
> > of type# to type name.
> >
> > 4) Since I myself do no development with the DB, only its pure
> > administration, I have no clue as to how these UNDEFINED objects could
> > be created, but I suspect that it could be possible if using the DBA
> > Studio and applying a "Create similar..." to the Materialized Views
> > branch. Could anyone confirm that?
> >
> > 5) How could I inspect these UNDEFINED objects? Specially: How could I
> > assess its dependencies to other objects in the DB?
> >
> > 6) Specially: How could I drop them? Since the syntax of the drop
> > command demands naming the type of object (like "DROP TABLE" or "DROP
> > INDEX" etc.) I see no chance of doing it.
> >
> > By the way, these UNDEFINED objects cannot be managed by the DBA
> > Studio, and they are only listed if choosing the "Display by objects"
> > (or was it "Display by schemas" ?- don't remember now).
> > Of course, one can always:
> >
> > select object_name,owner from dba_objects where
> > object_type='UNDEFINED';
> >
> > and get a complete list of these strange animals.
> >
> > Your turn!
> >
> > Rick
>
> You have not discovered anything that has not been discussed numerous
> times in numerous forums, including this one.
>
> And in answer to your question about multiple objects having the same name
> ... no it is not possible. Unless, of course, the object consists of
> multiple parts such as a package and package body, or as you have seen.
>
> Check the google archives for more and get a good nights sleep.
>
> Daniel Morgan

I think Daniel misspoke when he said " And in answer to your question about multiple objects having the same name ... no it is not possible." as yes, under one owner multiple objects can have the same name providing they are of different types as in a table and an index on that table may have the same name. Look in the (I think) Concepts manual at Namespaces.

UT1> col owner format a12
UT1> col object_name format a30
UT1> select owner, object_name, object_type

  2 from dba_objects
  3 where object_name = 'MARKTEST';

OWNER        OBJECT_NAME                    OBJECT_TYPE
------------ ------------------------------ ------------------
MPOWEL01     MARKTEST                       TABLE
MPOWEL01     MARKTEST                       INDEX

HTH -- Mark D Powell -- Received on Fri Jul 12 2002 - 13:07:25 CDT