Difference between database and schema

[vishalaksha]

Please explain the difference between database and schema.

[amirtai]

Hi there

Database is a collection of data that can be accessed through different methods/models. Like network, relational model etc.

An Oracle database system comprises at least one instance of the application, along with data storage. An instance comprises a set of operating-system processes and memory-structures that interact with the storage. Typical processes include PMON (the process monitor) and SMON (the system monitor).

The Oracle RDBMS stores data logically in the form of tablespaces and physically in the form of data files. Tablespaces can contain various types of memory segments; for example, Data Segments, Index Segments etc. Segments in turn comprise one or more extents. Extents comprise groups of contiguous data blocks. Data blocks form the basic units of data storage. At the physical level, data-files comprise one or more data blocks, where the block size can vary between data-files.

Oracle database conventions refer to defined groups of ownership (generally associated with a "username") as schemas.

Most Oracle database installations traditionally come with a default schema called SCOTT. After the installation process has set up the sample tables, the user can log into the database with the username scott and the password tiger. (The name of the SCOTT schema originated with Bruce Scott, one of the first employees at Oracle (then Software Development Laboratories), who had a cat named Tiger.)

Other default schemas include:

* SYS (essential core database structures and utilities)
* SYSTEM (additional core database structures and utilities, and privileged account)
* OUTLN (utilized to store metadata for stored outlines for query optimizer plan stability.
* BI, IX, HR, OE, PM, and SH (expanded sample schemas containing more data and structures than the older SCOTT schema)

Hope that would be helpful to understand the difference.

Amir