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
insert, and everything else.
The database is the thing that 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, once you’ve installed the Oracle software stack. But using
dbca (database creation assistant) is easier to get started.
To begin to understand the difference I think we must start saying that this nomenclature was a mistake from Oracle.
I assume that if you have an ERP called TRITON you want your database to be named TRITON, or if my company it’s called JENNY EXPORT I might decide my database to be called JENNYEXP or something like that, no need for any user with the same name.
In Oracle, you need to create a USER before creating the tables and weird enough this collection of TABLES is called SCHEMA.
Then you might create the actual users and grant proper permissions to work with such database as TRITON as per this example, by the way, if you connect thru SQL DEVELOPER you must NAME A CONNECTION.
I know I sound kind of frustrated but it makes more sense to me the MS SQL SERVER naming convention and I could add that I worked with Oracle earlier than SQL SERVER.
According to the way the terminology is sometimes used, 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