If you have to deal with both Oracle and PostgreSQL databases in your daily work, it can be confusing to deal with terms such as users, schemas and instances, since they mean different things in each software installation.
After discussing the matter with an experienced coworker, I have made the following matrix which may clarify the differences and the similarities between the two:
| Oracle | PostgreSQL |
|---|---|
| One database may contain many schemas | One database may contain many schemas |
| A user equals a schema | A user equals a role (with login) |
| One instance supports one database * | One instance may support many databases |
| One user belongs to one database | One user belongs to the entire instance |
| Users belong in a global namespace together with roles and public synonyms | Users does not belong in any namespace |
| By default, users can only access objects in their own schema | By default, users can only access objects in the schemas that they own |
| For a user to access objects in a different schema, object privileges or the ANY system privilege is required | For a user to access objects in a different schema, in must be granted USAGE on the schema |
| A schema has a password | A schema does not have a password |
| A schema cannot be owned by a (another) user | A schema is owned by a user, by default the user that created the schema |
| A schema may contain up until 3 namespaces: one for indexes, another one for constraints and a final one for tables, views, sequences etc. | A schema is in itself a namespace. This namespace may in turn contain tables, views, indexes etc |
* Exceptions to this rule are 1) Oracle RAC, where multiple instances support one database and 2) Oracle Multitenant, where one instance supports many pluggable databasess
A good tutorial regarding schemas can be found here
No comments:
Post a Comment