What is the difference between a user and a schema in Oracle?
What is the difference between a user and a schema in Oracle?
From Ask Tom
You should consider a schema to be the user account and collection of all objects therein as a schema for all intents and purposes.
SCOTT is a schema that includes the EMP, DEPT and BONUS tables with various grants, and other stuff.
SYS is a schema that includes tons of tables, views, grants, etc etc etc.
SYSTEM is a schema.....
Technically -- A schema is the set of metadata (data dictionary) used by the database, typically generated using DDL. A schema defines attributes of the database, such as tables, columns, and properties. A database schema is a description of the data in a database.
From WikiAnswers:
Furthermore, a user can access objects in schemas other than their own, if they have permission to do so.
Think of a user as you normally do (username/password with access to log in and access some objects in the system) and a schema as the database version of a user's home directory. User "foo" generally creates things under schema "foo" for example, if user "foo" creates or refers to table "bar" then Oracle will assume that the user means "foo.bar".
For most of the people who are more familiar with MariaDB or MySQL this seems little confusing because in MariaDB or MySQL they have different schemas (which includes different tables, view , PLSQL blocks and DB objects etc) and USERS are the accounts which can access those schema. Therefore no specific user can belong to any particular schema. The permission has be to given to that Schema then the user can access it. The Users and Schema is separated in databases like MySQL and MariaDB.
In Oracle schema and users are almost treated as same. To work with that schema you need to have the permission which is where you will feel that the schema name is nothing but user name. Permissions can be given across schemas to access different database objects from different schema. In oracle we can say that a user owns a schema because when you create a user you create DB objects for it and vice a versa.