Oracle: TEMPORARY Tablespaces : Tempfiles or Datafiles ? [ID 160426.1]
TEMPORARY Tablespaces : Tempfiles or Datafiles ? [ID 160426.1]
PURPOSE
-------
This bulletin explains the difference between tempfiles and datafiles associated
to TEMPORARY tablespaces and tablespaces TEMPORARY.
SCOPE & APPLICATION
-------------------
For all DBAs who need to maintain these 2 categories of files, manage space
allocation and requirement within the database itself and/or under the OS.
Restrictions Matrix
-------------------
+----------------------------+
| TEMPORARY | Tablespace |
| tablespace | TEMPORARY |
+----------------------------+
| Locally | Dictionary |
| managed | managed |
-------------+----------------------------+
| Datafiles | impossible | Y |
-------------------------------------------
| Tempfiles | Y | impossible |
------------------------------------------+
Other combinations are illicit.
*** **************************************************************************
*** TEMPORARY Tablespaces / Tablespaces TEMPORARY versus PERMANENT tablespaces
*** **************************************************************************
o The tablespace TEMPORARY exists since 7.3 :
=> You create a tablespace TEMPORARY with the following syntax:
CREATE TABLESPACE .. TEMPORARY
It uses datafiles only.
o The TEMPORARY tablespace is introduced in 8i :
=> You create a TEMPORARY tablespace with the following syntax:
CREATE TEMPORARY TABLESPACE .. TEMPFILE
It uses tempfiles only.
o You identify a tablespace TEMPORARY/TEMPORARY tablespace in DBA_TABLESPACES
for its CONTENTS of TEMPORARY type.
o A tablespace TEMPORARY/TEMPORARY tablespace differs from a PERMANENT
tablespace (DBA_TABLESPACES.CONTENTS=PERMANENT) in that it does not allow
permanent segments to be created, such as permanent tables, indexes,
clusters, rollback segments.
o A tablespace TEMPORARY/TEMPORARY tablespace provides a single temporary
segment shared by all users requiring
=> sort operations and therefore sorts extents
=> temporary extents for GLOBAL TEMPORARY TABLEs
This unique temporary segment improves the concurrence of multiple sort
operations, multiple transactions on the same temporary table using the
same temporary segment, reduces their overhead, and avoids Oracle space
management operations altogether.
o The unique temporary segment is automatically created after instance
startup as soon as a user requires a sort extent for a sort run or a
temporary extent for a global temporary table.
o The unique temporary segment is automatically dropped at instance shutdown.
o You can view the allocation and deallocation of space in a segment (sort or
temporary) in a tablespace of temporary contents using V$SORT_SEGMENT and
V$SORT_USAGE views identifying the current sort users in those segments.
o To know the advantages of choosing a tablespace TEMPORARY rather than a
PERMANENT tablespace, refer to
Note:102339.1 Temporary Segments: What Happens When a Sort Occurs
o In 8i, the default temporary tablespace of a user can be of any category;
but be aware that PERMANENT locally managed will raise errors in any case:
SQL> alter user x temporary tablespace PERM_LOCAL;
User altered.
SQL> select * from dba_tables order by 3,2,6,4,7,9,1,5;
select * from dba_tables order by 3,2,6,4,7,9,1,5
*
ERROR at line 1:
ORA-03212: Temporary Segment cannot be created in locally-managed tablespace
In 9i, the default temporary tablespace of a user can be of any TEMPORARY
type :
=> TEMPORARY tablespace locally managed
=> tablespace TEMPORARY dictionary managed
If you try to define a tablespace of PERMANENT locally-managed type as
TEMPORARY tablespace for a user, you will get the following error:
SQL> alter user x temporary tablespace PERM_LOCAL;
alter user x temporary tablespace PERM_LOCAL
*
ERROR at line 1:
ORA-12911: permanent tablespace cannot be temporary tablespace
In 10g, dictionary managed tablespaces are deprecated in favor of
locally-managed tablespaces. Temporary tablespaces should be created
as locally managed.
If you try to define a tablespace of PERMANENT locally-managed type as
TEMPORARY tablespace for a user, you will get the following error:
SQL> alter user x temporary tablespace PERM_LOCAL;
alter user x temporary tablespace PERM_LOCAL
*
ERROR at line 1:
ORA-10615: Invalid tablespace type for temporary tablespace
NOTE: Temporary tablespaces should appear "full" after a while in a normally
running database. Extents are allocated once and then managed by the system.
Rather than doing the an expensive operation of "space management"
(data dictionary updates), the system will allocate an extent in TEMP
and then keep it and manage it itself. This is normal and to be expected
and is not an indication that you do not have any temporary space.
*** ********************************************************************
*** TEMPORARY Tablespace/Tempfiles versus Tablespace TEMPORARY/Datafiles
*** ********************************************************************
1/ Space Management
----------------
Because space management is much simpler and more efficient in locally
managed tablespaces, use TEMPORARY tablespaces since they are the only
temporary tablespaces compatible with local management.
SQL> create tablespace temp1
2 DATAFILE '/ora/ora817/32/oradata/V817/temp1.dbf' size 100M
3 TEMPORARY
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
create tablespace temp1
*
ERROR at line 1:
ORA-25144: invalid option for CREATE TABLESPACE with TEMPORARY contents
To know the advantages of choosing a locally-managed tablespace rather than
a dictionary-managed tablespace, refer to
Note:105120.1 Advantages of Using Locally Managed vs Dictionary Managed
Tablespaces
In 8i, since sort segments cannot be created on locally managed permanent
tablespaces, use locally managed TEMPORARY tablespaces.
Refer Note:131769.1 ORA-03212 at Instance Startup
Locally managed temporary tablespaces use tempfiles, which do not modify
data outside of the temporary tablespace or generate any redo for temporary
tablespace data. They can be used in standby or read-only databases.
In 10g, for additional details, refer to Oracle� Database SQL Reference
10g Release 2 (10.2) Create Tablespace PERMANENT | TEMPORARY Clauses.
2/ Views
-----
Use V$TEMPFILE and DBA_TEMP_FILES views to list the files associated to a
TEMPORARY tablespace:
SQL> select * from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN
------------------------ --------- ----------
TEMP_TEMPFILE_LOCAL TEMPORARY LOCAL
TEMP_DATAFILE_DICT TEMPORARY DICTIONARY
SQL> select STATUS, ENABLED, NAME from v$tempfile;
STATUS ENABLED NAME
------- ---------- ----------------------------------
ONLINE READ WRITE /tcase/oradata/V901/temp_temp01.dbf
SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files;
FILE_NAME TABLESPACE_NAME
------------------------------------ -------------------
/tcase/oradata/V901/temp_temp01.dbf TEMP_TEMPFILE_LOCAL
They are analogous to the V$DATAFILE and DBA_DATA_FILES views that list the
files associated to tablespaces TEMPORARY.
SQL> select STATUS, ENABLED, NAME from v$datafile;
STATUS ENABLED NAME
------- ---------- ----------------------------------
ONLINE READ WRITE /tcase/oradata/V901/temp_data01.dbf
SQL> select FILE_NAME, TABLESPACE_NAME from dba_data_files;
FILE_NAME TABLESPACE_NAME
------------------------------------ -------------------
/tcase/oradata/V901/temp_data01.dbf TEMP_DATAFILE_DICT
This query will give the size of the temporary tablespace:
select tablespace_name, sum(bytes)/1024/1024 mb
from dba_temp_files
group by tablespace_name;
This query will give the "high water mark" (= max used at one time)
of the temporary tablespace:
select tablespace_name, sum(bytes_cached)/1024/1024 mb
from v$temp_extent_pool
group by tablespace_name;
This query will give current usage
select ss.tablespace_name,sum((ss.used_blocks*ts.blocksize))/1024/1024 mb
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;
3/ Privilege
---------
To create a temporary tablespace or tablespace temporary, get the
CREATE TABLESPACE system privilege.
4/ Creation of a TEMPORARY Tablespace
----------------------------------
SQL> create TEMPORARY tablespace temp_tempfile_local
2 TEMPFILE '/ora/V817/temp_temp.dbf' size 100M
3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
Note: On some operating systems, Oracle does not allocate space for the tempfile
until the tempfile blocks are actually accessed. This delay in space
allocation results in faster creation and resizing of tempfiles, but it
requires that sufficient disk space is available when the tempfiles are later
used. Refer to your operating system documentation to determine whether
Oracle allocates tempfile space in this way on your system.
Creation of a Tablespace TEMPORARY
----------------------------------
SQL> create tablespace TEMP_DATAFILE_DICT
2 datafile '/tcase/oradata/V901/temp_data.dbf' size 100M
3 TEMPORARY;
Tablespace created.
5/ Tempfiles/Datafiles Removal
---------------------------
a. In 8i and 9i, you cannot remove datafiles from a tablespace until you drop the
tablespace.
In 9i and 10g, the new clause INCLUDING CONTENTS AND DATAFILES in DROP TABLESPACE
command allows the automatic removal of all datafiles from the OS.
In 10g, specify DROP to drop from the tablespace an empty datafile
or tempfile specified by filename or file_number. This clause causes
the datafile or tempfile to be removed from the data dictionary and
deleted from the operating system. The database must be open at the
time this clause is specified.
For restrictions on the use of this clause, refer to
Oracle� Database SQL Reference 10g Release 2 (10.2)
ALTER TABLESPACE DROP Clause
b. You can remove tempfiles from temporary tablespaces and keep the logical
structure empty.
=> in 8i:
SQL> alter tablespace TEMP_TEMPFILE_LOCAL
2 add tempfile '/oradata/V817/temp_temp01.dbf';
Tablespace altered.
SQL> alter database tempfile '/oradata/V817/temp_temp01.dbf'
2 drop;
Database altered.
SQL> !ls /oradata/V817/temp_temp01.dbf
/oradata/V817/temp_temp01.dbf
You must now remove the temp file on the system and add it
again.
SQL> alter tablespace TEMP_TEMPFILE_LOCAL
2 add tempfile '/oradata/V817/temp_temp01.dbf';
Tablespace altered.
=> In 9i and 10g: you can use the new clause INCLUDING DATAFILES to remove OS files
SQL> alter database tempfile '/oradata/V901/temp_temp01.dbf'
2 drop including datafiles;
Database altered.
SQL> !ls /oradata/V901/temp_temp01.dbf
/oradata/V901/temp_temp01.dbf not found
In all cases if you remove all tempfiles from a temporary tablespace, you
may encounter the following error:
SQL> alter table olap.test add primary key (c);
alter table olap.test add primary key (c)
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty
ORA-25153
25153, 00000, "Temporary Tablespace is Empty"
// *Cause: An attempt was made to use space in a temporary tablespace with
// no files.
// *Action: Add files to the tablespace using ADD TEMPFILE command.
This error message is only returned when current operation needs
have an access on the disk.
RELATED DOCUMENTS
-----------------
Note:132663.1 ORA-03296 Resizing Temporary Locally Managed Tablespace
Note:131769.1 ORA-03212 at Instance Startup
Note:102339.1 Temporary Segments: What Happens When a Sort Occurs
Comments