"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:FpBFb.61770$aT.23137_at_news-server.bigpond.net.au...
> "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
> news:3fe6179d$0$28694$cc9e4d1f_at_news.dial.pipex.com...
> > Um
> >
> > See Daniel's answer. The idea behind his approach as I understand it is
to
> > partition tables on a client column and to store the partitions in
> separate
> > areas. This does do exactly as Daniel describes.
> >
> > It also proves my statement that " A user cannot own multiple objects of
> the
> > same type with the same name " to be woefully wrong.
> >
> > I ought to have known this
> >
> >
> > 1 select object_type,count(*) from all_objects
> > 2 where owner='NIALL' and object_name='SALES'
> > 3* group by object_type
> > SQL> /
> >
> > OBJECT_TYPE COUNT(*)
> > ------------------ ----------
> > TABLE 1
> > TABLE PARTITION 16
> >
>
> Hi Niall
>
> I think you're being a bit hard on yourself. Your statement "A user cannot
> own multiple objects of the
> same type with the same name" is quite valid. In your example, there is
> *only one* object that has a name called "SALES". However, SALES has 16
> subobjects (table partitions) that belong to it, each identified by their
> own subobject_name and object_id.
>
> With DBA_OBJECTS, Oracle tries to record the "parent" object that
> corresponds to all partitioned objects within the same view. Therefore
*the
> same object_name* (parent) can be stored multiple times, but each
occurrence
> corresponds to a different subobject_name (child).
>
> Therefore the above example doesn't invalidate your correct comment. You
> can't be blamed if DBA_OBJECTS decides to store the same object_name
> multiple times.
>
> Cheers
>
> Richard
Thanks for the kind words. I have (to the extent that I have ever thought of
partitioned objects i.e hardly ever) always thought of them as being 1
logical object with several subdivisions (sub-objects) as you say. However I
could also have done the equivalent as a select from obj$ with the same
results. ISTM that if I have 16 objects in obj$ all of the same type, owned
by the same user and all of the same name my original statement was just
wrong. It doesn't mean that you can have 16 *tables* all of the same name,
but Daniel's partitioning solution is still a perfectly feasible solution to
the original problem. You can do mine on std edition for half to a third of
the price though :(
--
Niall Litchfield
Oracle DBA
Audit Commission UK
Received on Mon Dec 22 2003 - 08:55:43 CST
