Schema and Database

[Phillipe Rodrigues]

What is the difference between Schema and Database?

[Goutham Pallipati]

Hi,

a database can have multiple logical partitions called Schemas and you can manage the size,access,creation and deletion of schemas without effecting the other parts of the Database.

each schemas can be mapped to one or more DB Files and can be associated with a user.

Database should contain at least one schema and one or more schemas contribute to a database.

[Phillipe Rodrigues]

Please clear my doubt with reference to the below example:

I login in to oracle using
useranme:abc
password:abcxyz

Create a database named "test" with 3 tables in it: table 1,table 2,table 3

Now for the above what does schema refer to?

[Campbell Ritchie]

I Googled and found a tutorial, and there are bound to be lots more.

[Jason Pirkey]

You didn't reference a schema in the example above.

If you created a database named "test_database". In the "test_database" database you can create schemas "test_database_schema_1", "test_database_schema_2", and "test_database_schema_3". In each of these schemas you can create tables "table_1", "table_2", and "table_3".

Now if user1 logged into the "test_database" database there would be three schemas each containing three tables.

Does this help you?

[Phillipe Rodrigues]

From the above details does it mean that the all 3 schemas tables:
contain different data,update or modification in particular schema's table will not reflect to other schemas.

Is it that use of schemas is to distinguish users for the tables?

Is it that a database has atleast one schema as default?

Do we create schema before or after database?

[Rene Larsen]

Originally posted by Phillipe Rodrigues:
From the above details does it mean that the all 3 schemas tables:
contain different data,update or modification in particular schema's table will not reflect to other schemas.

Is it that use of schemas is to distinguish users for the tables?

Is it that a database has atleast one schema as default?

Do we create schema before or after database?

If you will have a correct answer, you need to tell us which database you want information about.

Oracle have e.g. databases (SIDs) which then have a number of schema (database users).

MySQL don't use the term schema the same way.

[Jason Pirkey]

Phillipe,

In regards to an Oracle database you are correct. Each of the table_1 tables in the different schemas could have the exact same table structure and contain different data. Also an insert, update, or deletion of data in one table will have no effect on the data in a table with the same name found in other schema. There is also nothing that says each of the table_1 tables have to have the same structure in the different schemas. One table could have 5 columns and another 8 columns.

If I am not mistaken every user has a schema, but I don�t think you have to have a user for each schema. Also just because a user has a schema doesn�t mean they can create a table in it.

Yes there are some default schemas and accounts. All schemas are created after the database except those default schemas of course. There are also schemas and accounts like scott you want to delete as soon as possible.

[Jason Pirkey]

I asked around and it appears I was mistaken. Rene's correct in that one could characterize a schema as being synonymous with a user. The best way I can explain it from the people I asked about it is a schema is created when a user is created and the objects a user owns reside in their schema.