Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Multiple databases in ORACLE - How? Expand / Collapse
Author
Message
Posted Wednesday, July 29, 2009 9:34 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, January 20, 2014 9:43 AM
Points: 670, Visits: 891
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.
Post #761703
Posted Wednesday, July 29, 2009 1:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #761924
Posted Tuesday, August 11, 2009 10:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 5, 2013 2:53 PM
Points: 24, Visits: 123
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
Post #768813
Posted Tuesday, August 11, 2009 12:03 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #768856
Posted Tuesday, August 11, 2009 12:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 5, 2013 2:53 PM
Points: 24, Visits: 123
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
Post #768872
Posted Wednesday, August 12, 2009 11:06 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, January 20, 2014 9:43 AM
Points: 670, Visits: 891
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, http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_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.




Post #769516
Posted Thursday, August 13, 2009 8:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:35 AM
Points: 142, Visits: 1,684
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.
Post #770185
Posted Thursday, August 13, 2009 10:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:03 AM
Points: 1,182, Visits: 1,970
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, http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_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.
Post #770329
Posted Thursday, August 13, 2009 4:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #770553
Posted Thursday, August 13, 2009 4:43 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 958, Visits: 1,031
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.
Post #770558
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse