Table 8-1 summarizes the basic characteristics of these databases.
Feature |
Local system temporary database |
Local user temporary database |
Global user temporary database |
Global system temporary database |
---|---|---|---|---|
Supported temporary database objects |
Both session-specific temporary objects and regular database objects. |
Both session-specific temporary objects and regular database objects. |
Regular database objects only, no session specific temporary objects. |
Regular database objects only, no session specific temporary objects* |
Recovery |
Re-created when owning instance restarts. |
Re-created when owning instance restarts. |
Re-created when the cluster restarts, and transactionally recovered when an instance fails. |
Re-created when the cluster restarts, and transactionally recovered when an instance fails |
Accessibility |
Accessible from owning instance only, but it must be created on a shared device so that it can be created and dropped from other instances. |
Accessible from owning instance only. |
Accessible from any instance |
Accessible from any instance |
Creation |
User-created, one (required) for each instance. |
User-created, zero or more for each instance. |
User-created, zero or more for the cluster. |
System-generated (dbid = 2), one for the cluster |
Binding allowed? |
No |
Yes |
No |
No |
Storage (shared or private devices) |
Shared storage only |
Both shared and local storage |
Shared storage only |
Shared storage only |
* During tempdb configuration mode, the global system temporary database acts like a local system temporary database to the boot coordinator, and thus supports both session-specific temporary objects and regular database objects.