Menu
What is the difference between a user and a schema in Oracle?
819
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.
A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are logical structures created by users. Objects such as tables or indexes hold data, or can consist of a definition only, such as a view or synonym.
In Oracle terminology, a user is someone who can connect to a database (if granted enough privileges) and optionally (again, if granted the appropriate privileges) can own objects (such as tables) in the database. The objects a user owns are collectively called >schema.
A schema in a SQL database is a collection of logical structures of data. The schema is owned by a database user and has the same name as the database user. From SQL Server 2005, a schema is an independent entity (container of objects) different from the user who creates that object.
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.
174
I believe the problem is that Oracle uses the term schema slightly differently from what it generally means.
Schema in sense 2. is similar, but not the same as schema in sense 1. E.g. for an application that uses several DB accounts, a schema in sense 2 might consist of several Oracle schemas :-).
Plus schema can also mean a bunch of other, fairly unrelated things in other contexts (e.g. in mathematics).
Oracle should just have used a term like "userarea" or "accountobjects", instead of overloadin "schema"...
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
