Today I wanted to talk about some terminology that applies to multiple database platforms, however, I want to talk specifically to how it relates to the Oracle RDBMS. These four words seem to cause a lot of confusion and are often misused. The specific terminology or definitions I would like to cover today are database vs. instance vs. schema vs. user. I’m going to go over what they mean to me as an Oracle DBA and more specifically to an Oracle database.
Database:
A database is made up of the physical files that contain the data and metadata that makeup said database. These include the datafiles, controlfiles, and redo log files.
Instance:
An instance is the memory that is shared and accessed by all the threads and background processes. This includes the SGA, PGA, and background processes such as PMON, MMON, SMON, DBWn, LGWR, CKPT, ARCn, etc.
The below picture is a representation of the difference of the two. Keep in mind that there are many more background processes. I listed some as examples.
Schema:
A schema is a collection of database objects owned by a user. Essentially it is nothing more than a user that owns objects such as tables, views, etc. These database objects are what make the schema.
User:
A user in an Oracle database is an account that is created in the database and used for a certain purpose. This could be merely to select data or create/update/delete objects in the database. With the proper permissions, the user could do these tasks with their own objects or with another user’s/schema’s.