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. It only takes a minute to sign up.
Sign up to join this community
In Oracle, datafile are system files where actual data is stored. The collection of datafiles make tablespace and, at last, 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 between Schema and Database in details. Online resources are helpful but seemed confusing, regarding this difference.
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.
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.
