Re: Multiple database in one instance

"Peter Luxmore" <compsync_at_optusnet.com.au> wrote in message news:39ACF5F2.D892C0B8_at_optusnet.com.au...
> Barbara.
>
> This is correct. I am running Personal Oracle 8i for Windows 98, so I have
 a
> problem with developing multiple applications each with its own schema or
> application table relational structure of you like. I create a separate
> TABLESPACE for each schema (application) which has its own unique OWNER
> (user). This solves the problem if a table in one schema has the same name
 for
> a table in another, as it is prefixed by the OWNER/SCHEMA name.
>
> For example the EMPLOYEE table in the IWP_DATA Tablespace/Schema is called
> IWP_OWNER.EMPLOYEE whereas it is called PAYROLL.EMPLOYEE in the PAYROLL
> Tablespace/Schema.

This arrangement obviously works for you, but I hate to see this sort of stuff pushed out into an unsuspecting world unchallenged, so here goes (and none of this is intended personally)...

I always respect 23 years in any profession. But hyphenating and slashing away doesn't obscure the fact that you are doing an Alice in Wonderland job here, and making words mean what you want (or need) them to mean.

The meaning of "tablespace" in Oracle is clear. The meaning of "schema" in Oracle is clear. Not once, ever, do they ever come close to meaning the same or nearly the same thing. So what's with the 'tablespace/schema' elision?

So we take your last sentence: "the X table in tablespace aaa is called onething, whereas it is called somethingelse in tablespace bbb'.

Total rot. You have two completely separate tables, that bear not one scintilla of relation with each other, that happen to be owned by two completely different users and stored in two completely different tablespaces (we think, though it's hard to be sure from this description). So "it" isn't "called" something else: it is *indeed* entirely something else. A completely different segment.

As for the statement that each tablespace "has a unique owner (user)"... oddly, tablespaces in Oracle have no owners, because they don't literally exist. They are logical constructs, and if they have an owner, it's the database itself, though you won't find that declared anywhere, because even that's not really true.

The truth lies in your brackets below ("default tablespace"). In other words, in your particular situation, you have arranged for there to be an "application user" who happens to have a corresponding tablespace as its default. The problem is that this is only so because you make it so. There's nothing in Oracle itself to prevent another User *also* acquiring that tablespace as its default -so who is the owner then?

I have just spent a remarkable week on a training course with a venerable gentleman who knew the ins and outs of "adibas" (sp??) and rackf (??) and assorted stuff I've neveer even heard of, and how he keeps his coffee warm on the mainframe during winter, and he kept me enthralled during an entire week of lunch breaks. The man's expertise was utterly beyond question, and I learned heaps. And he made not *one* attempt to squeeze Oracle into the scape of his prior knowledge. Which I regret to say, from this jumble of words above, is what I think you might be doing, conceptually if not practically (as I say -this stuff obviously works for you).

Addressed therefore not particularly to you, but to anyone who is tempted to say -for example- that an Oracle Instance is "like" something else in another product... it ain't so. Hold on to what you can to get the navigational ropes, by all means. But be prepared to let go before you get caught in the knots of knowledge that doesn't actually apply. Regards
HJR
>
> TableSpace Owner Schema
> (default tablespace)
> --------------------------------------------------------------------------
> TRAVEL TRAVEL TRAVEL
> IWP_DATA IWP_OWNER IWP_OWNER
> ACCOUNTS ACCT ACCT
> DEMO DEMO DEMO
> PAYROLL PAYROLL PAYROLL
>
> I have been working with various RDBMS and application development for 23
> years and there is a little confusion if you are coming into Oracle from
 other
> platforms as I am currently attempting. There is an additional level in
 the
> concept structure to identify.
>
> The "Instance" is the complex set of memory structures, background and
> operating system processes. The instance structure is based on the
> multitasking implementation in UNIX. Processes have specific tasks within
 the
> RDBMS that work together, and each process has its own memory block to
 store
> variables address stacks etc. The Instance is sort of like the Server
 Manager
> in MS SQL Server.
>
> The "Database" pertains to the database files, control files and redo logs
> part of the RDBMS.
>
> The "Schema" describes the properties of the database files. There is also
 of
> course the schema owner and users that have permission to access that
 schema.
>
> Regards
> Peter Luxmore
>
> Barbara Kennedy wrote:
>
> > Just have multiple schemas. For all intents and purposes it is a
 multiple
> > database to you.
> > Jim
> > <woods_john_at_crane.navy.mil> wrote in message
> > news:8nrnpb$q6o$1_at_nnrp1.deja.com...
> > > Is it possible to have more than one database per instance. If so,
 how?
> > >
> > > Thanks,
> > >
> > > John Woods
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
>
>
Received on Sun Sep 03 2000 - 07:12:28 CDT