SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple databases in ORACLE - How?


Multiple databases in ORACLE - How?

Author
Message
Martin Wills
Martin Wills
Right there with Babe
Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)

Group: General Forum Members
Points: 716 Visits: 893
The scientific application for which I'm the developer uses a SQL Server database to hold data and results.

The data and results for a single 'catchment' are stored in a single database.

So any one user can have many databases (one per catchment). And a single server instance can hold the databases for several users.

Each database has the same set of tables, stored procedures etc.

This works well - users can easily create and drop their databases and not affect other people's databases.

BUT

I now need to implement the application using ORACLE and the same concepts just don't seem to apply.

You seem to be encouraged to have just one database instance and therefore one database per server.

So how do I achieve the multiple databases concept?

At first, I thought that the 'schema' would be the key. In other words each 'database' would be in a separate schema.

This seems to have the following snags

(a) One-to-one correspondence between user and schema.
(b) The schema contains tables and views but not stored procedures. So stored procedures must handle 'variable' schema.

Any suggestions?

I'm asking this question here rather than an ORACLE-style forum because I have more hope of an expert, helpful, good-humoured reply here.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5493 Visits: 4639
dmw (7/29/2009)
At first, I thought that the 'schema' would be the key. In other words each 'database' would be in a separate schema.

This seems to have the following snags

(a) One-to-one correspondence between user and schema.
(b) The schema contains tables and views but not stored procedures. So stored procedures must handle 'variable' schema.


Your solution would actually be to consider each schema a database.

Contrary to statement (b) code e.g. storedprocs, packages and functions do are stored at schema level.

In regards to statement (a) each Oracle account actually defines a schema but you can grant privileges to a specific account so to have rights on different schemas - in case a single "user" owns several "databases".

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
doug.williams
doug.williams
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 127
I agree with Paul. The Schema will act a a pseudo database, much like you experience in MSSQL. One thing that concerns me is your comment about the user adding and dropping a new db in mssql. This flexibility in Oracle is not as easy to achieve. You could drop the user that owns a schema and as a result drop (DELETE) the schema. the users can be given permission to create new user accounts (Schema) and script a initial configuration. but the management is way too loose for most DBAs.

In Oracle all Schemas for the DB Instance are held in the a single logical file. Logical because the DB could span multiple files depending on the size and resource configurations.

Don't mean to confuse the issue, but the management of Oracle is NOTHING like MSSQL. For better or for worse, it is what it is.

Doug
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5493 Visits: 4639
I'm in agreement but I have a problem with statement below...
doug.williams (8/11/2009)
In Oracle all Schemas for the DB Instance are held in the a single logical file. Logical because the DB could span multiple files depending on the size and resource configurations.


Oracle storage is organized in tablespaces which are nothing but logical collections of physical datafiles. You can have as many tablespaces as you want.

Moreover, one of the attributes of an Oracle user account is default_tablespace meaning, the place where objects created by that account would go in case the user doesn't bother to specify a particular tablespace.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
doug.williams
doug.williams
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 127
Yes that is true, where a Tablespace is a collection of database objects. M Most Oracle Database DBAs will set up a default Tablespace "USERS" this tablespace is usually the default that you speak of. The DBA will also set up additional tablespaces depending on the design and utilization of the DB. For example a large block tablespace may be initialized for objects that will most frequently be large in size for each instance. There by tuning the tablespace to provide faster response, Versus a tablespace for indexes, which would be relatively small per instance. and so on....

Without getting into semantics about management of Oracle tablespaces, I was just trying to point out that the management requirements for one greatly out weigh the other.

Doug Cool
Martin Wills
Martin Wills
Right there with Babe
Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)

Group: General Forum Members
Points: 716 Visits: 893
Thank you Paul and Doug for your responses.

As Paul said, I was wrong when I stated that schemas do not contain stored procedures.

I suspect tablespaces are irrelevant to my immediate problem. They're just concerned with where the data is stored (i.e. controlling the physical files) and not how it is organised. True?

So schemas may be the answer but we are constrained by the schema=user identity.

If we are using OS authentication, a user has only one identity. So, if I want to 'create another schema' for a user, I must generate a pseudo user/schema and then grant access to my original user.

My alternatives based on further research seem to be

(1) Virtual Personal Databases. This ORACLE feature comes pretty close to meeting my needs BUT is only available with the top-end Enterprise Edition.

(2) Give up, go for a single database and add a CatchmentID column to every table. This seems to be the approach favoured by Tom Kyte of ORACLE (see, for example, 11_QUESTION_ID:31263903816550" target=_"blank" class="SmlLinks">http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::Tongue11_QUESTION_ID:31263903816550).

(As an aside, you couldn't exactly describe that guy as good-humoured!).

It does seem very strange that a top-end database, such as ORACLE, puts so many barriers in the way of implementing a solution which comes naturally to MSSQL. Maybe, their approach is the most efficient one but it would be nice to have a choice.
Ewan Hampson
Ewan Hampson
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 1826
dmw (7/29/2009)
You seem to be encouraged to have just one database instance and therefore one database per server.

Irrespective of the solutions being suggested, and with only occasional exposure to Oracle, I have never thought that one database per instance implies one database per server. You just create an instance for each database. It's one of the most visible conceptual differences between SQL Server and Oracle.

That said, I have a SQL Server comfortably hosting 130 databases. Under Windows that would require 130 Oracle service instances, which sounds as if it could be demanding of resources.
Mauve
Mauve
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1766 Visits: 2054
dmw (8/12/2009)
Thank you Paul and Doug for your responses.

As Paul said, I was wrong when I stated that schemas do not contain stored procedures.

I suspect tablespaces are irrelevant to my immediate problem. They're just concerned with where the data is stored (i.e. controlling the physical files) and not how it is organised. True?

So schemas may be the answer but we are constrained by the schema=user identity.

If we are using OS authentication, a user has only one identity. So, if I want to 'create another schema' for a user, I must generate a pseudo user/schema and then grant access to my original user.

My alternatives based on further research seem to be

(1) Virtual Personal Databases. This ORACLE feature comes pretty close to meeting my needs BUT is only available with the top-end Enterprise Edition.

(2) Give up, go for a single database and add a CatchmentID column to every table. This seems to be the approach favoured by Tom Kyte of ORACLE (see, for example, 11_QUESTION_ID:31263903816550" target=_"blank" class="SmlLinks">http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::Tongue11_QUESTION_ID:31263903816550).

(As an aside, you couldn't exactly describe that guy as good-humoured!).

It does seem very strange that a top-end database, such as ORACLE, puts so many barriers in the way of implementing a solution which comes naturally to MSSQL. Maybe, their approach is the most efficient one but it would be nice to have a choice.


I've done it before. In Oracle, each "database" would be a separate schema. Each schema sees only its own stuff.

I would totally disagree with Tom Kyte's solution of adding a CatchmentID key to each table. If you do, your schema is now "multi-tenant".

As a side note SQL Server is the odd animal with it's multiple database component to separate schemas. The original relational model done by IBM, Oracle, and others in the 1980s did not have it. Everything was based upon schema owner. SQL Server started real support for multiple schemas within a single database in SQL Server 2005.


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5493 Visits: 4639
dmw (8/12/2009)
So, if I want to 'create another schema' for a user, I must generate a pseudo user/schema and then grant access to my original user.


The beauty of it is that there is not such a thing as CREATE SCHEMA MySchema statement.

You create Oracle accounts a.k.a. users, then if you create an object "owned" by such Oracle account you get a schema with the same name.

I would think of it like your app will require two "kinds" of Oracle accounts.

Operational accounts, which own objects inside a schema.
User accounts, which own no objects but have access granted to objects in one or more -if needed, schemas.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Bruce W Cassidy
Bruce W Cassidy
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1317 Visits: 1033
Oracle has fairly comprehensive support for shared schemas. These are implemented (last time I looked at Oracle) as "application users" where the user has rights to own objects, but no rights to log in. You then grant rights to that user's objects to all of the other people who need to use it.

You can, of course, create multiple Oracle instances. There used to be a great deal of overhead in doing this, but with modern versions of Oracle that's far lower. The only real overhead is in the amount of system database area per instance.

I'd recommend that you try the shared schema approach first. You can emulate the dynamic creating/dropping databases by creating/dropping application users.

I have a client who has multiple concurrent versions of the same application within the same Oracle database, and they are maintained under different schemas ("application users"). The application knows to prefix all object names with the appropriate schema name. It works pretty well.

Interestingly, there's nothing stopping you from doing the same in SQL Server now. And in fact, as SQL Server supports multiple and default schemas, it would be somewhat easier. However, it's not an approach I would recommend.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search