Database Reference
In-Depth Information
This SQL, executed on your behalf in the background to get the additional space, is referred to as recursive SQL.
Your SQL
INSERT
statement caused other recursive SQL to be executed to get more space. This recursive SQL can be
quite expensive if it is done frequently. Such updates to the data dictionary must be serialized; they can't be done
simultaneously. They are something to be avoided.
In earlier releases of Oracle, we would see this space management issue—this recursive SQL overhead—most
often in temporary tablespaces (this was before the introduction of “real” temporary tablespaces created via the
CREATE
TEMPORARY TABLESPACE
command). Space would frequently be allocated (we would have to delete from one dictionary
table and insert into another) and deallocated (we would put the rows we just moved back where they were initially).
These operations would tend to serialize, dramatically decreasing concurrency and increasing wait times. In version 7.3
(way back in 1995), Oracle introduced the concept of a true temporary tablespace, a new tablespace type dedicated
to just storing temporary data, to help alleviate this issue. Prior to this special tablespace type, temporary data was
managed in the same tablespaces as persistent data and treated in much the same way as permanent data was.
A temporary tablespace was one in which you could create no permanent objects of your own. This was
fundamentally the only difference; the space was still managed in the data dictionary tables. However, once an extent
was allocated in a temporary tablespace, the system would hold on to it (i.e., it would not give the space back). The
next time someone requested space in the temporary tablespace for any purpose, such as sorting, Oracle would look
for an already allocated extent in its internal list of allocated extents. If it found one there, it would simply reuse it, or
else it would allocate one the old-fashioned way. In this manner, once the database had been up and running for a
while, the temporary segment would appear full but would actually just be “allocated.” The free extents were all there;
they were just being managed differently. When someone needed temporary space, Oracle would look for that space
in an in-memory data structure, instead of executing expensive, recursive SQL.
In Oracle 8.1.5 and later, Oracle went a step further in reducing this space management overhead. It introduced
the concept of a locally-managed tablespace as opposed to a dictionary-managed one. Local management of space
effectively did for all tablespaces what Oracle 7.3 did for temporary tablespaces: it removed the need to use the data
dictionary to manage space in a tablespace. With a locally-managed tablespace, a bitmap stored in each data file is
used to manage the extents. To get an extent, all the system needs to do is set a bit to 1 in the bitmap. To free up some
space, the system sets a bit back to 0. Compared with using dictionary-managed tablespaces, this is incredibly fast. We
no longer serialize for a long-running operation at the database level for space requests across all tablespaces. Rather,
we serialize at the tablespace level for a very fast operation. Locally-managed tablespaces have other nice attributes as
well, such as the enforcement of a uniform extent size, but that is starting to get heavily into the role of the DBA.
Going forward, the only storage management method you should be using is a locally-managed tablespace. In
fact, in Oracle9
i
and above, if you create a database using the database configuration assistant (DBCA), it will create
the
SYSTEM
tablespace as a locally-managed tablespace, and if the
SYSTEM
tablespace is locally managed, all other
tablespaces in that database will be locally managed as well, and the legacy dictionary-managed method will not
work. It's not that dictionary-managed tablespaces are not supported in a database where the
SYSTEM
tablespace is
locally managed, it's that they simply can't be created:
EODA@ORA12CR1> create tablespace dmt
2 datafile '/tmp/dmt.dbf' size 2m
3 extent management dictionary;
create tablespace dmt
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace
EODA@ORA12CR1> !oerr ora 12913
12913, 00000, "Cannot create dictionary managed tablespace"
// *Cause: Attemp to create dictionary managed tablespace in database
// which has system tablespace as locally managed
// *Action: Create a locally managed tablespace.
