Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm really confused with the terms database, user and schema. Can anyone explain how they are different from each other (if they are)?

If they are same then, what are the similarities between them? How do we use them? And how do we create them?

share|improve this question
some doubt cleared, but not all,i got little idea about the user and schema, but what about database ?? I mean, how do we create them and how do they differ from other (user and schema) –  jWeaver Mar 19 '13 at 14:34

4 Answers 4

up vote 20 down vote accepted

In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.

See this post on Stack Overflow: difference between a User and a Schema in Oracle? for more details and extra links.

You create users with the create user statement. This also "creates" the schema (initially empty) - you cannot create a schema as such, it is tied to the user. Once the user is created, an administrator can grant privileges to the user, which will enable it to create tables, execute select queries, insert, and everything else.

The database is the thing contains all the users you've created, and their data (and a bunch of predefined system users, tables, views, etc. that make the whole thing work). You should look at the Oracle Database Architecture documentation in the Concepts Guide (actually, that whole page is worth a read - there's a section about users and schemas higher up in that page) to get an introduction to what a database is, and what a database instance is - two important concepts.

You can create a database with the create database statement (see also here), once you've installed the Oracle software stack. But using dbca (database creation assistant) is easier to get started.

share|improve this answer
even i didn't read whole things, but,it seems helpful specially that diagram. Thanks !! I will accept your answer, once, i'm done. –  jWeaver Mar 19 '13 at 16:26
So what if you want to create an "HR" schema (HR is not a person, but a group of related tables, or a department within a company), do you have to create a dummy "HR" user first? –  NealWalters Jan 7 at 18:51
@NealWalters: yes, although it's not "first". You just create the user, then create your tables/views/whatever. There's a create schema but... that doesn't create a schema :-) –  Mat Jan 7 at 19:00
@Mat - Thanks, that's what I've been reading. (I guess by "first", I meant before adding the tables, etc...) So there no way to create a schema outside of creating a user? Why would I want to create "dummy" users? Wouldn't that make security a pain? Would you then create an 'HR' Profile, put John, Sue, Linda in that profile, and then don't grant any privileges to the HR user? –  NealWalters Jan 7 at 19:10
@NealWalters: no, you can't create a schema without creating a user. Security's orthogonal, that user doesn't need to have any privilege at all. That's just how it works, get used to it. –  Mat Jan 7 at 19:16

I am telling you according to concepts not based on any type of database management system you are using.

Conceptually: Database : is Just a pile of data, mostly un-managed related or unrelated data.

Schema: Schema refers to formally structuring the unmanaged related/unrelated data so that it can be managed by some management system which understands the formal definition provided by the schema. Schema is basically a scheme to to provide overview. For example you have XSD which defines the scheme/schema to create XML. You have RDBMS which defines its own schemes which are based on Codd Rules which basically defines schemes to create RDBMS.

Now if you want to know more about is refer this Link.

User's use the formal language specified by the scheme/schema to access the Database

share|improve this answer

User!= Schema , User and Schema are not same but they are used interchangeably

share|improve this answer
Please add details regarding how they're different. –  Erik Sep 10 at 14:55

Based on my little knowledge of Oracle, a USER and a SCHEMA are somewhat similar. But there is also a major difference. A USER can be called a SCHEMA if the "USER" owns any object, otherwise it will only remain a "USER". Once the USER owns at least one object then by virtue of all of your definitions above... the USER can now be called a SCHEMA.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.