Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Novice question: Tablespaces and Users
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
-- Niall Litchfield Oracle DBA Audit Commission UK ***************************************** Please include version and platform and SQL where applicable It makes life easier and increases the likelihood of a good answer ****************************************** "Dave Pylatuk" <davep_at_centurysystems.net> wrote in message news:pWCEb.21192$CK3.2018162_at_news20.bellglobal.com...Received on Sun Dec 21 2003 - 15:59:27 CST
> Got it, thanks for the response.
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3fe2cec8$0$13355$ed9e5944_at_reading.news.pipex.net...
> > "Dave Pylatuk" <davep_at_centurysystems.net> wrote in message
> > news:rfoEb.18539$CK3.1618737_at_news20.bellglobal.com...
> > > Hello all.
> > >
> > > I am running oracle 8.1.7 on a server with the following tablespace:
> > >
> > > Client1_DB
> > >
> > > This tablespace has many tables all owned by a user named 'John' for
> > example
> > > John.table1
> > > John.table2
> > > ....
> > >
> > > I would like to have the following tablespaces on the same physical
> > server:
> > >
> > > Client1_DB
> > > John.table1
> > > John.table2
> > > ....
> > > Client2_DB
> > > John.table1
> > > John.table2
> > > ....
> > > Client9_DB
> > > John.table1
> > > John.table2
> > > ....
> > >
> > > As indicated above, all tables in each tablespace must be owned by the
> > same
> > > user.
> > > Each tablespace will contain exactly the same set of tables.
> > >
> > > I not an Oracle expert and am sure this can be done, I just do not
know
> > how.
> >
> > What you ask cannot be done. A user cannot own multiple objects of the
> same
> > type with the same name. What on earth would the correct result for
> >
> > select * from john.table1 be?
> >
> > However it looks like you wish to store the same application multiple
> times
> > in the same instance. In this case you would create 9 users
u01,u02...u09
> > etc These can use the same or different tablespaces, which are just
> storage
> > areas.
> >
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
> >
> >
>
>