Re: Question: Database vs TableSpace (please help)

[This followup was posted to comp.databases.oracle.server and a copy was sent to the cited author.]

In article <5gtvga$jk6_at_chronicle.concentric.net>, Nnoor_at_cris.com says...
>
> In MS SQL Server, we used the service manager (database manager in Oracle)
> to startup the server and then all databases on that server would be
> accessible. On Oracle, it seems that you startup a specific database.
> So if we have multiple databases on a server, do we start all of them
> individually? Following is an example...
>
> We have two projects going, X and Y. Both are unrelated and need there
> own databases. Under MS SQL Server, we went into Enterprise Manager
> and simply created two DB's (as simple as right clicking and choosing
> a menu item). Then we only started the server and all of the DB's on
> it were accessible. On ORACLE, I think things work different way. Is
> TABLE SPACE equivalent to "databases" on SQL Server? In our example, are
> we better off creating two DATABSES on the Oracle server and startup
> both individually or create two TABLE SPACES, one for X and one for Y,
> under the same database (e.g. default ORCL database). If later is the
> case (i.e. using table spaces), does Oracle complain about having exact
> same table names under two different table spaces in the same database?
> How do we resolve conflicts? We looked at the documentation and and it
> seems that creating a database is quite long and complex process.

You are clear on some of the issues.

In Oracle, the following holds true:

	An Oracle database is also known as a database, sid or
	instance.

	Within an Oracle sid, you have multiple tablespaces.  A
	tablespace is the logical breakdown of the database.

	Within a tablespace, you have N number of physical 
	datafiles.

So, you can have multiple tablespaces.

Within the database, you can have N number of users. These are also known as schemas. NOW, HERE IS HOW YOU SUPPORT BOTH PROJECTS IN THE SAME DATABSE. You could create one user to manage one of the projects and a second user to own the objects for the secound project. You know have two schemas. If these are large projects, you can tie your users to tablespaces, by saying this user can create objects in these tablespaces.

There are a number of performance and managment considerations to think about here based on the size of your projects and development performance required. Send me some more information and I can recommend more.

In theory, there is nothing wrong with developing in the same database for multiple projects. UNLESS, you need both projects to be patch, operating system independant. (i.e. - apply a patch for one project and it effects the other) Think of these issues as well. THIS WILL ANSWER YOUR QUESTION OF CREATING TWO DATABASES. Also think of this from the physicall level. You may want multiple or seperate ORACLE_HOMES and/or application homes.

In Oracle, you can only have one unique table name per schema. So, if I have user ngreene, I can only have one table name junk. (i.e. ngreene.junk) But, there can be another user agreene with a table called junk. The phsyical or logical location of where this table is stored in the database does not matter. (i.e. - you can have ngreene.junk and agreene.junk in the same tablspace)

-- 
Neil Greene
Senior System Engineer
MCI Systemhouse, Inc.
email: <mailto: ngreene_at_laoc.SHL.com>