What is a Schema in Oracle ?

It may sound like an easy question, but the answer isn’t always clear to everyone, so I intend to clarify it in this post.

Most users think that, when they are developing an application, what they need is a database. Wrong. What they are looking for is a schema (or maybe a set of schemas), but almost no database needs a schema for itself.

A schema is a collection of database objets, owned by a single database user. The name of the schema is the same as the name of the user. The objets in this schemas can be in different tablespaces, because there is no direct relationship between a tablespace and a schema.

On the other hand, we can have a user that doesn’t owns a schema, but has grants to work with it. That’s a clear example of the differences between a schema and an user: a schema is always owned by an user, but an user doesn’t have to own a schema.

Or better said: a user that doesn’t owns a schema, what really owns is an empty schema. Because he can eventually have some object owned by him, so the schema is still there.

However, remember that a schema is a set of database objects, so… ¿a null collection of objects is still considered a collection of objects? That depends on the point of view, but anyway, an empty schema doesn’t have any interest for us.

I hope that this post has made a clearer perspective of what a schema is, and to know the differences between a schema and a database.