Database Instance

A Database Instance can be Specific to the Database

Colorful Pie Chart Consists of Paper Pages
MirageC / Getty Images

The term database instance is frequently misunderstood because it means different things to different vendors. It's most frequently used in connection with Oracle database implementations.

General Meaning of a Database Instance

In general, a database instance describes a complete database environment, including the RDBMS software, table structure, stored procedures and other functionality. Database administrators might create multiple instances of the same database for different purposes.

For example, an organization with an employees database might have three different instances: production (used to contain live data), pre-production (used to test new functionality prior to release into production) and development (used by database developers to create new functionality).

Oracle Database Instances

If you have an Oracle database, you know that a database instance means a very specific thing.

While the database itself includes all the application data and metadata stored in physical files on a server, an instance is a combination of the software and memory used to access that data.

For example, if you sign into an Oracle database, your login session is an instance. If you log off or shut down your computer, your instance disappears, but the database — and all your data — remain intact. An Oracle instance can access only one database at a time, while an Oracle database can be accessed by multiple instances.

SQL Server Instances

A SQL Server instance usually means a specific installation of SQL Server. It is not the database itself; rather, it is the software used to create the database. Maintaining multiple instances might be useful when managing server resources because each instance can be configured for memory and CPU usage—something you cannot do for individual databases within a SQL Server instance.

A Database Scheme vs. a Database Instance

It may also be useful to think of an instance in context with a database scheme. The scheme is the metadata that defines the database design and how the data will be organized. This includes its tables and their columns and any rules that govern the data. For instance, an employee table in a database might have columns for name, address, employee ID and job descriptions. This is the structure, or scheme, of the database.

An instance of the database is a snapshot of the actual content at any given time, including the data itself and its relationship to other data in the database.