Re: Multiple database in one instance

From: Howard J. Rogers <howardjr_at_iprimus.com>
Date: Wed, 6 Sep 2000 19:20:45 +1100
Message-ID: <39b5fe1d@news.iprimus.com.au>

"Peter Luxmore" <compsync_at_optusnet.com.au> wrote in message news:39B4D652.D6DC385D_at_optusnet.com.au... Howard
Answers to your questions. See below.
Regards
Peter J. Luxmore
"Howard J. Rogers" wrote:
"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)...

We are already doing it this way and so doesn't require any sort of challenge. We develop Websites specialising in ColdFusion, that access SQL Server and Oracle databases. We have 6 websites that use Oracle for different clients, so we have to separate each pragmatic application. This makes it easier later when we have to install on the clients site using either EXP and IMP utilities or Embarcadero ER/Studio, RapidSQL or DBArtisan. I suggest you try out these three development tools. You will probably learn a lot about Oracle from them, especially DBArtisan. Note: Personal Oracle does not support transportable tablespaces.

>>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.

I am the Mad Hatter actually and you did intend this as a personal comment. The fact is that it is you who has actually misconstrued what I have written (or you might have a comprehension problem) and making them mean what you want for your purpose. Some things you are saying below indicate this as well as the width and depth of your experience.

>>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?

In abstract terms, a schema is a logical collection of related database objects, such as tables, indexes, and views. A schema is a developer's-eye view of the database objects of interest such as shown on an Entity-Relationship Diagram for example.

In an Oracle database, a schema is a logical collection of objects stored within one or more tablespaces. Because it is a logical collection, several applications can share schemas, or cross schema boundaries into other schemas. A tablespace, is a logical storage area that maps to one or more physical file(s).

Beautiful definitions, but you rather ignored my original question. Since tablespaces are logical wrappers for physical files, and since a schema is a collection of objects created by a User, they are clearly two entirely different things. So why originally write tablespace/schema as is there was some identity between the two of them? In your specific case, you have one tablespace per schema, but that is not generally true, and that was my point.

>>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.

Have to correct you again. Typically, a schema has exactly one owner (that is, an Oracle user account) responsible for creating, altering and dropping these objects. This owner grants access privileges to other users, either directly or using roles. If you are not the owner of these objects, you qualify references to the objects by specifying which tablespace they are in using dot notation. I suggest you look this one up, as obviously you are not aware of dot notation and where or how it is used.

                     eg: SELECT CUST_NAME FROM MKG.LEADS

For my purposes, and for simplicity, the default owner of the tablespace is also the schema owner.

You have rather again missed my point. You wrote that table "a" is called "X" when it is in one tablespace, but "Y" when it is in another. Are you claiming that you have a single segment (ie, a table in this case) straddling tablespaces? Because unless you use partitioning, that can't be the case. So you have two entirely different segments.

Now, about that correction?

>>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.

Total nonsense in this case! Logical constructs DO EXIST, and they are there for a very good reason. Security! Once again the schema's objects are created in its own tablespace to separate the application relevant objects. In addition temporary tablespace as well.

There is nothing secure about tablespaces. Not even in your database. Give me the DBA role, and I guarantee to create a bunch of tables all over the place, with zero regard for the artifical separation between applications you have errected. I shall particularly enjoy using the "move tablespace" command (assuming you have 8i) to completely stuff up your carefully-planned but utterly security-less arrangements.

>>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?

As above!

>>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).

So, you have done a one week only training course!

Peter, I hate to correct you, but I am actually a DBA Instructor for Oracle, and the gentleman to whom I referred was one of my recent students.

I could pull the other stuff you wrote to bits, but you seem not to be interested in the simple fact that your 'six applications in one database' works because you make it work. It is not how Oracle works 'naturally' -by which I mean, tablespaces are not *intended* to store all the tables for a single application. They do *not* have owners. And scott.emp is an entirely different segment from system.emp, regardless of what tablespace you house them in.

Junior DBA not understanding the intent of your structure could very happily create any table in any tablespace, or move a table from tablespace to another. The only thing stopping him would be the lack of the relevant privileges: in other words, there is nothing "structurally" in Oracle that keeps your arrangements intact.

I bet you when your instructor first came face to face with Oracle (and other technologies), he pulled on his prior skills and knowledge. Oracle is really not that unique, most of the rules are much the same and many RDBMS (easy to reengineer from one RDBMS platform to another, using the right tools), network operating systems, SQL and computer languages are very similar in concept and logic and in reality only differs mainly in syntax

Like I said, he wasn't my instructor, but my student. And yes, from our out-of-class discussions, it becomes apparent that there is really little new under the sun. However, his mindset wasn't cramped by making Oracle features match one-for-one with anything he'd previously worked with. I sense that the same cannot necessarily be said for your own good self.

And yes, its the only way to keep several separate applications separate and is a method recommended in many books on Oracle. When the job has been completed and in production all I have to do is drop ONE tablespace without endangering our other projects underway. Planned and well organised. Also I have to extract the schema for the user to transport it to the clients site. As I have mentioned above; EXP or IMP utilities or I get Embarcadero to generate a DDL for all objects in the required schema. Statements like yours, above and the one below, is very saddening for me. In my opinion, it is a contributing factor to the so-called 'World-Wide Skills Crisis" that the industry itself has creates through this hanging on to this stupid philosophy.
Through time, technology progresses and evolves. In many other industries and professions, encouragement is given and rewarded, to keep professional skills up-to-date to keep up with this progressive evolution in order for that industry to survive. When it is realised in the IT industry that prior skills in database design, modelling and application development actually apply to current technologies and acts accordingly, the crisis would be well on the way to being resolved as it is in other industries and professions. The technology sharemarket would probably do better as well as there would be better productivity.
There are many good books available in regard to database management principles that do not apply to any one platform (generic), and provide basic concepts on how things are organised in most RDBMS.

Agreed. What's more I don't think you'll get very far in Oracle or Sybase or SQL Server or any other RDBMS unless you understand the concepts and general principles. That is not in dispute. What I think is most *un*helpful is to publish information which is misleading, and whilst your setup works for you, it is not the case that a schema and tablespace are the same thing, or that a tablespace has an owner. That is very specific, factual information, and to claim otherwise is simply wrong.

>>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.

If you read it again; to quote "The Instance is sort of like the Server Manager.........". Don't know how you misconstrued this one?

You'll have to point out the misconstruction. I missed out the words "sort of", but that doesn't make a material difference.

If you were developing many applications, for various clients perhaps you can tell us ALL how would YOU organise it all?

Well, let's start with the basics, shall we? A database is a "self-describing collection of integrated records" according to David Kroenke (Database Processing, Fundamentals, Design and Implementation", Fifth Edition, Page 13. The "integrated records" bit is what gets me: if you have 6 very different applicaitons, I'd create 6 separate databases, 6 separate Instances. And I can then do full database exports and imports with the greatest of ease, whereas I presume you are having to do User-mode exports if you want to keep each application separate. In my case, I can do cumulative and incremental exports. In your case -not.

Tablespaces are supposed to be used to separate segments which have different life-spans, different or conflicting access patterns, different storage and growth needs. The fact that you *can* make a tablespace effectively acts as a sub-database doesn't mean that is good practice, and the entire reason for my original post is that a lot of people come to this group for advice and help on best practice -and yours is definitely not recommended, nor best practice.

I don't see much in your posts and this reply that suggests anything constructive or practical that directly answers the original question in this thread. I was also perhaps merely expanding on Barbara's post. "Is it possible to have more than one database per instance. If so, how?"

Well, whether you see anything constructive in my posts or not is not really the issue (though I suspect you haven't really looked too hard). The issue is mis-use of words, which in Oracle have a specific meaning, on an Oracle newsgroup. Newcomers and people in search of help should not go away thinking that a tablespace=schema=application.

Incidentally, it is also usually considered not best practice to post in HTML format. Many people don't have readers that can cope.

Regards
HJR
[Snip] Received on Wed Sep 06 2000 - 03:20:45 CDT