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

Popular Posts