July 29, 2009 at 9:34 am
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.
July 29, 2009 at 1:19 pm
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.August 11, 2009 at 10:53 am
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
August 11, 2009 at 12:03 pm
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.August 11, 2009 at 12:22 pm
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 😎
August 12, 2009 at 11:06 am
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.
August 13, 2009 at 8:11 am
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.
August 13, 2009 at 10:41 am
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.
August 13, 2009 at 4:33 pm
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.August 13, 2009 at 4:43 pm
[font="Verdana"]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.
[/font]
August 13, 2009 at 5:02 pm
Bruce W Cassidy (8/13/2009)
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.
Unfortunately statement above does not reflects reality.
Multiple instances DO add a lot of overhead no matter Ora version, anything in between 17 to 30 daemons running per instance plus all SGA/PGA instance specific memory segments.
_____________________________________
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.August 13, 2009 at 5:18 pm
PaulB (8/13/2009)
Bruce W Cassidy (8/13/2009)
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.Unfortunately statement above does not reflects reality.
Multiple instances DO add a lot of overhead no matter Ora version, anything in between 17 to 30 daemons running per instance plus all SGA/PGA instance specific memory segments.
[font="Verdana"]Yes and no. It's true you get a different SGA/PGA per instance, just as you do with the caches for SQL Server across different instances. However, Oracle uses shared code for the daemons (or Services in Windows parlance), so the overhead for the shared code isn't as big.
If you're creating Oracle instances on Windows, you can share one Oracle listener across all of the instances, so you just end up with a couple of services per instance.
Yes, there is overhead. But it's not as large an overhead as it used to be (thinking back to earlier versions of Oracle.)
[/font]
August 14, 2009 at 4:48 pm
Bruce W Cassidy (8/13/2009)
Oracle uses shared code for the daemons (or Services in Windows parlance), so the overhead for the shared code isn't as big.
Sorry, on this one you are dead wrong man 😀
Providing all your instances point to the same ORACLE_HOME it's true that you are going to end up with a single set of binaries BUT -notice the uppercase but? - Oracle starts one PMON per instance, one SMON per instance, etc, etc, etc. therefore you end with as many as 30 daemons per instance.
Bruce W Cassidy (8/13/2009)
If you're creating Oracle instances on Windows, you can share one Oracle listener across all of the instances
You CAN... but you don't want to do it so pretty please allow at least one listener per instance.
_____________________________________
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.July 28, 2011 at 11:53 am
I know this topic is fairly old, but came upon it as we are having similar issue. Our issue is that we have 6 different plants running 6 different databases with identical database architecture. The difference is the data that resides in the database at each plant. We also use OS authentication for database logons and some users may have access to more than 1 database. I was tasked with coming up with a plan to consolidate the 6 databases into a single database.
The database and software we use were developed by a software vendor which uses synonyms for each user to access the schema. There are synonyms created for every object that exists for the schema owner (developed by software vendor). This caused additional maintenance for us, as we had to run a script to create synonyms each time a new user is created. Additionally, this way of accessing objects would prevent us from consolidating the 6 databases because of the "duplicate" synonym names that would be required to move each plant's objects into its own schema.
After researching for the past couple days (it took me awhile coming from SQL Server and still learning Oracle), I am thinking of dropping all the synonyms that are used to access the software vendor's objects and making use of a logon trigger to alter a user's session to another schema (permissions do not change). For example:
IF ... EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=PLANT1';
IF ... EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=PLANT2';
etc...
Here are some addtl articles:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:588045400346317188
http://www.oracle-base.com/articles/misc/SchemaOwnersAndApplicationUsers.php
July 29, 2011 at 8:39 am
Little confused... there are six Oracle "instances" each one running a "database" or there is one Oracle instance with six operational "schemas" each one of them equating one "database"?
_____________________________________
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.Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply