Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In oracle, Datafile are system files where actual data is stored.The collection to datafile make Tablespace and atlast database is collection of tablespaces

Correct me if I am wrong on the concepts of Datafile,tablespace and database.

I would like to understand the difference betweeen Schema and Database is details .As online resources are helpful but seemed confusing to regarding this difference.

share|improve this question
up vote 5 down vote accepted

If all else fails read the documentation. Try here for starters (v. good diagram). In Oracle, a schema is a database. Also see here - particularly this:

One characteristic of an RDBMS is the independence of physical data storage from logical data structures. In Oracle Database, a database schema is a collection of logical data structures, or schema objects [i.e. related tables]. A database schema is owned by a database user and has the same name as the user name.

A further distinction is between the physical database files "the database" and the "instance" - the running software that makes up the fully working system.

In one sense, an Oracle "database" is useless - it's just a bunch of bits on a disk - it's the software which turns it into a system (i.e. a database in the sense that information is retrievable/modifiable).

An Oracle database is a running instance plus data (+ control + redo &c.) files which itself may contain 1 or many schemas which are themselves "databases" - i.e. as the layman would understand them - i.e. a HR, CRM or ERP (&...) schemas/systems.

share|improve this answer
If course, this has all [optionally] changed slightly in 12c, what with global users, pdbs, cdbs etc – Phil Jun 12 '14 at 14:36
Tags are 11g and 10g. Anyway, the OP has got to walk before he can run! :-) – Vérace Jun 12 '14 at 14:46
I disagree with the statement 'a schema is a database'. One instance can host at most one database. That one database can have many schema. – Phil Sumner Jun 19 '14 at 23:04
My understanding of Oracle is that transactions between different schemas (even on the same instance) require XA transactions (2 phase commit) and are fundamentally distinct from normal intra-schema (i.e. within the same schema) transactions. This is the same protocol used for databases on different machines, so as far as Oracle is concerned, a schema is a distinct database - i.e. a schema is a database in the normally understood sense of the word(guaranteed ACID transactions between tables in the same database/schema). Oracle is known for playing fast and loose with standards and nomenclature – Vérace Jun 20 '14 at 0:04
There's that SYS schema, which contains some procs, packages and whatnot. Theres XDB schema, which contains XML packages. An application can create it's own schema within the same database and use packages from SYS, XDB and manipulate data in it's own schema. In light of this - no, schema != database. I myself magane a database that consists of at least 9 schemas interacting with one another. – Kitet Jul 8 '14 at 20:55

You can think about the schema as a user home directory in Unix. A schema and oracle user are strongly tight together. One user maps to a single schema. You can temporarily change the current schema in a similar way as you do in Unix by ALTER SESSION SET CURRENT_SCHEMA=USER1.

Objects in other schemas can be reach in the very same way as in Unix: Just precede the object name with SCHEMA. eg: USER1.TABLE1, USER2.TABLE2 would be an example. When you change the current schema to USER1, you don't need to append the USER1 prefix anymore.

A database is the most wide container, it collects schemas-users, tablespaces (datafiles are parts of a tablespace) and one or more (RAC) instances.

share|improve this answer
this is much more correct than the statement "a user is a database". – a_horse_with_no_name Jul 17 at 15:31

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.