Re: How to deal with objects of type UNDEFINED

True
There are 4 namespaces unless my knowledge is obsolete you can have clusters, indexes, triggers and tables of the same name

Mark D Powell wrote:

> 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 Sun Jul 14 2002 - 04:44:58 CDT