What is the difference between Schema and Database in Oracle?
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 reached in the very same way as in Unix: Just precede the object name with SCHEMA.
eg: USER1.TABLE1
, USER2.TABLE2
would be an example. After 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 part of a tablespace) and one or more (RAC) instances.
Things get more complicated with 12c multitenant option because you can have multiple pluggable databases (PDB) in a single container database (CDB). But it is just another story.
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.