Oracle database USER and SCHEMA [SOLVED]
-
In Oracle DB, what are the terms USER and SCHEMA? From what I've read, schema refers to all the content a database has (tables, sequences, views ...) which belongs to a USER. But what is a USER? Is it like an "internal database"? I mean, let say that I have 2 programs (Planet Help | City industries) that each need 2 separate databases. So, can I create a USER with the name planetDatabase, and another with the name cityDatabase, which would mean I have 2 "internal databases"? And in each "internal database" I can create specific users that have restricted access to certain functions in their respective "internal database"? Or I am way off what a USER actually is?
-
In Oracle DB, what are the terms USER and SCHEMA? From what I've read, schema refers to all the content a database has (tables, sequences, views ...) which belongs to a USER. But what is a USER? Is it like an "internal database"? I mean, let say that I have 2 programs (Planet Help | City industries) that each need 2 separate databases. So, can I create a USER with the name planetDatabase, and another with the name cityDatabase, which would mean I have 2 "internal databases"? And in each "internal database" I can create specific users that have restricted access to certain functions in their respective "internal database"? Or I am way off what a USER actually is?
USER in Oracle is a function that returns the name of the User for the current session - Oracle / PLSQL: USER function[^] - an analogy for that would be the "person" logged on (but a user is not necessarily a person) Schemas can be User specific - within a database. They are not databases in themselves. Think of them as the "user account". Well I made that as clear as mud! The best explanations of the differences that I've found are on this link[^] which also includes a link to this post[^] . General consensus of opinion seems to be that Oracle made a mess of the terminology!
-
In Oracle DB, what are the terms USER and SCHEMA? From what I've read, schema refers to all the content a database has (tables, sequences, views ...) which belongs to a USER. But what is a USER? Is it like an "internal database"? I mean, let say that I have 2 programs (Planet Help | City industries) that each need 2 separate databases. So, can I create a USER with the name planetDatabase, and another with the name cityDatabase, which would mean I have 2 "internal databases"? And in each "internal database" I can create specific users that have restricted access to certain functions in their respective "internal database"? Or I am way off what a USER actually is?
As an example, let's see what Oracle says about "schema";
docs.Oracle.com[^] wrote:
A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects: (list of stuff)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
USER in Oracle is a function that returns the name of the User for the current session - Oracle / PLSQL: USER function[^] - an analogy for that would be the "person" logged on (but a user is not necessarily a person) Schemas can be User specific - within a database. They are not databases in themselves. Think of them as the "user account". Well I made that as clear as mud! The best explanations of the differences that I've found are on this link[^] which also includes a link to this post[^] . General consensus of opinion seems to be that Oracle made a mess of the terminology!
So, using my example, if I have 2 programs, and if I need a DB for each of them "Planet Help DB" and "City industries DB" (random names, no connection to real ones), can I create two Database users, using Application Express, one with the name "PlanetHelpDB" and the other "CityIndustriesDB", and use those as a DB for each app? I see that in both of them I can have tables with the same names as in the other one, and also for each I can create new users using "Administration" tools (from Application Express), which will be linked/restricted to a default schema (PlanetHelpDB or CityIndustriesDB, in my example). Is this a good or a bad way to do it? In case it is bad, what is the recommended way to do it (to have 1 database for each app)?
-
So, using my example, if I have 2 programs, and if I need a DB for each of them "Planet Help DB" and "City industries DB" (random names, no connection to real ones), can I create two Database users, using Application Express, one with the name "PlanetHelpDB" and the other "CityIndustriesDB", and use those as a DB for each app? I see that in both of them I can have tables with the same names as in the other one, and also for each I can create new users using "Administration" tools (from Application Express), which will be linked/restricted to a default schema (PlanetHelpDB or CityIndustriesDB, in my example). Is this a good or a bad way to do it? In case it is bad, what is the recommended way to do it (to have 1 database for each app)?
In my own experience, having tables with the same name in two different schemas on the same database has led to issues (when people writing queries have omitted the schema name when referring to the table). If there is absolutely nothing in common between the databases then I would probably have two separate databases and manage them separately. It rather depends on how things like backups, transaction logs, etc will be handled and if there are benefits to having the schemas held on the same db. However, if these apps are for the same corporation and use different tables then I would put them on the same database, no need for differentiating schemas and users unless you want to use them to control access, or might want to in the future. We are currently using schemas in a corporate database to indicate which "area" (application if you like) "owns" the data in the table(s) and using the dbo schema for common data (i.e. no tables with the same name in different schema). I'm yet to discover if this will cause us problems. As you can probably gather .. "good" or "bad" is quite subjective and depends greatly on what the overall infrastructure is going to look like
-
In my own experience, having tables with the same name in two different schemas on the same database has led to issues (when people writing queries have omitted the schema name when referring to the table). If there is absolutely nothing in common between the databases then I would probably have two separate databases and manage them separately. It rather depends on how things like backups, transaction logs, etc will be handled and if there are benefits to having the schemas held on the same db. However, if these apps are for the same corporation and use different tables then I would put them on the same database, no need for differentiating schemas and users unless you want to use them to control access, or might want to in the future. We are currently using schemas in a corporate database to indicate which "area" (application if you like) "owns" the data in the table(s) and using the dbo schema for common data (i.e. no tables with the same name in different schema). I'm yet to discover if this will cause us problems. As you can probably gather .. "good" or "bad" is quite subjective and depends greatly on what the overall infrastructure is going to look like
Quote:
If there is absolutely nothing in common between the databases then I would probably have two separate databases and manage them separately.
They will have some stuff in common, like the user data (username, email, password ...) and other stuff too, but they will also have some data that will be needed only for them separatly. So I'm goin to use something like you said.
Quote:
We are currently using schemas in a corporate database to indicate which "area" (application if you like) "owns" the data in the table(s) and using the dbo schema for common data (i.e. no tables with the same name in different schema).
I'm new to this whole area of DB, so comments/suggestions like this one are really useful (practical not theoretical). Thanks!