Files - Expert Oracle Database Architecture - page 109

Note that oerr is a UNIX/Linux-only utility; on non-UNIX/Linux platforms, you'll need to refer to the Oracle
Database Error Messages manual for the details on the error you receive.

■ You might wonder why i wrote “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.” if they can't be created, why
would we need to support them? the answer lies in the transportable tablespace feature. You can transport a dictionary-
managed tablespace into a database with a SYSTEM tablespace that is locally managed. You can plug that tablespace in
and have a dictionary-managed tablespace in your database, but you can't create one from scratch in that database.

Note

The inability to create dictionary-managed tablespaces is a positive side effect, as it prohibits you from using
the legacy storage mechanism, which was less efficient and dangerously prone to space fragmentation. Locally-
managed tablespaces, in addition to being more efficient in space allocation and deallocation, also prevent tablespace
fragmentation. We'll take an in-depth look at this in Chapter 10.

Temp Files
Temporary data files (temp files) in Oracle are a special type of data file. Oracle will use temporary files to store
the intermediate results of large sort operations and hash operations, as well as to store global temporary table
data, or resultset data, when there is insufficient memory to hold it all in RAM. In Oracle 12 c and above, temporary
tablespaces can also hold the UNDO generated by operations performed on global temporary tables. In earlier releases,
the UNDO generated by global temporary tables was routed to the UNDO tablespace and hence would cause REDO to
be generated; this is no longer the case. Permanent data objects, such as a table or an index, will never be stored in
a temp file, but the contents of a temporary table and its indexes would be. So, you'll never create your application
tables in a temp file, but you might store data there when you use a temporary table.
Temp files are treated in a special way by Oracle. Normally, every change you make to an object will be recorded
in the redo logs; these transaction logs can be replayed at a later date to “redo a transaction,” which you might do
during recovery from failure. Temp files are excluded from this process. Specifically, transactions in global temporary
tables (located in temp files) never have REDO generated for them, although they can have UNDO generated. Thus, there
may be REDO generated working with temporary tables since UNDO is always protected by REDO , as you will see in detail
in Chapter 9. The UNDO generated for global temporary tables is to support rolling back work you've done in your
session, either due to an error processing data or because of some general transaction failure. A DBA never needs to
back up a temporary data file, and, in fact, attempting to do so would be a waste of time, as you can never recover a
temporary data file.

■ in Oracle 12 c and above, the UNDO generated for global temporary tables may be stored in the temporary
tablespace. By default, UNDO will be generated into the permanent UNDO tablespace, just like prior releases. an init.ora
system-level setting, or a TEMP_UNDO_ENABLED session-level settable parameter, may be set to TRUE to enable the UNDO
generated for global temporary tables to be stored in a temp file. in this manner, no REDO will be generated for these
operations. We will investigate this further in Chapter 9.

Note

It is recommended that your database be configured with locally-managed temporary tablespaces. You'll want
to make sure that as a DBA, you use a CREATE TEMPORARY TABLESPACE command. You don't want to just alter a
permanent tablespace to a temporary one, as you do not get the benefits of temp files that way.