8 Managing Tablespaces

Guidelines for Managing Tablespaces

Before working with table spaces of an Oracle Database, familiarize yourself with the guidelines provided in the following sections:

• Using Multiple Tablespaces

• Assigning Tablespace Quotas to Users

  
  

  See Also: Oracle Database Concepts for a complete discussion of database structure, space management, tablespaces, and datafiles

  
  

  Assigning Tablespace Quotas to Users

  Grant to users who will be cr eating tables, clusters, materialized views, indexes, and other objects the privilege to create the object and a quota (space allowance or limit) in the tablespace intended to hold the object segment.

  
  < /tbody>

  See Also: Oracle Database Security Guide for information about creating users and assigning tablespace quotas.

  
  

  Creating Tablespaces

  Be fore you can create a tablespace, you must create a database to contain it. The primary tablespace in any database is the SYSTE M tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment. The SYSTEM tablespace is the first tablespace created at database creation. It is managed as an y other tablespace, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop t he SYSTEM tablespace or take it offline.

  The SYSAUX tablespace, which acts as an auxiliary tablespac e to the SYSTEM tablespace, is also always created when you create a database. It contains information about and the sch emas used by various Oracle products and features, so that those products do not require their own tablespaces. As for the SYST EM tablespace, management of the SYSAUX tablespace requires a higher level of security and you cannot rename or d rop it. The management of the SYSAUX tablespace is discussed separately in "Managing the SYSAUX Tabl espace".

  The steps for creating tablespaces vary by operating system, but the first step is always to use your operating s ystem to create a directory structure in which your datafiles will be allocated. On most operating systems, you specify the size and fully specified filenames of datafiles when you create a new tablespace or alter an existing tablespace by adding datafiles. Whether you are creating a new tablespace or modifying an existing one, the database automatically allocates and formats the datafiles as spe cified.

  To create a new tablespace, use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE< /code>. You must have the CREATE TABLESPACE system privilege to create a tablespace. Later, you can use the ALTER TABLESPACE or ALTER DATABASE statements to alter the tablespace. You must have the ALTER TABLESPACE or ALTER DATABASE system privilege, correspondingly.

  You can also use the CREATE UNDO TABLESPACE sta tement to create a special type of tablespace called an undo tablespace, which is specifically designed to contain u ndo records. These are records generated by the database that are used to roll back, or undo, changes to the database for recovery, r ead consistency, or as requested by a ROLLBACK statement. Creating and managing undo tablespaces is the subject of Chapter 10, " Managing the Undo Tablespace".

  The creation and maintenance of permanent and temporar y tablespaces are discussed in the following sections:

  • Locally Managed Tablespaces

  • Bigfile Tablespaces

  • Dictiona ry-Managed Tablespaces

  • Temporary Tablespaces

  • Multiple Temporary Tablespaces: Using Tablespace Groups

    See Also: Chapter 2 , " Creating an Oracle Database" and your Oracle Database installation documentation for your operating system for information ab out tablespaces that are created at database creation Oracle Database SQL Reference for more information about the syntax and semantics of the CREATE TAB LESPACE, CREATE TEMPORARY TABLESPACE, ALTER TABLESPACE, and ALTER DATABASE statements.< /p> "Specifying Database Block Sizes" for information about initialization parameters necessary to create tablespaces with nonstandard block sizes

    
    

  Locally Managed Tablespa< /a>ces

  Locally managed tablespaces track all extent information in the tables pace itself by using bitmaps, resulting in the following benefits:

  • Concurrency and speed of space operat ions is improved, because space allocations and deallocations modify locally managed resources (bitmaps stored in header files) rathe r than requiring centrally managed resources such as enqueues

  • Performance is improved, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated

  • Readab le standby databases are allowed, because locally managed temporary tablespaces (used, for example, for sorts) are locally managed an d thus do not generate any undo or redo.

  • Space allocation is simplified, because when the AUTOALLO CATE clause is specified, the database automatically selects the appropriate extent size.

  • User re liance on the data dictionary is reduced, because the necessary information is stored in file headers and bitmap blocks.

  • Coalescing free extents is unnecessary for locally managed tablespaces.

  All tablespaces, including the SYSTEM tablespace, can be locally managed.

  The DBMS_SPACE_ADMI N package provides maintenance procedures for locally managed tablespaces.

  
  

  See Also: "Crea ting a Locally Managed SYSTEM Tablespace", "Migrating the SYSTEM Tablespace to a Locally Managed Tablespace"< /a>, and "Diagnosing and Repairing Locally Managed Tablespace Problems" "Bigfile Tablespaces" for information about creating another type of locally managed tablespace that contains only a single datafile or tempfile. PL/SQL Packages and Types Reference for information on the DBMS_SPACE_ADMIN package

  
  

  Creating a Locally Managed Tablespace

  You create a l ocally managed tablespace by specifying LOCAL in the EXTENT MANAGEMENT clause of the CREATE TABLESPAC E statement. This is the default for new permanent tablespaces, but you must specify if it you want to specify the management of the locally managed tablespace. You can have the database manage extents for you automatically with the AUTOALLOCATE clause (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM ).

  If you expect the tablespace to contain objects of varying sizes requiring many extents with different extent sizes, then < code>AUTOALLOCATE is the best choice. AUTOALLOCATE is also a good choice if it is not important for you to have a lot of control over space allocation and deallocation, because it simplifies tablespace management. Some space may be wasted with th is setting, but the benefit of having Oracle Database manage your space most likely outweighs this drawback.

  If you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents, then UNIFORM is a good choice. This setting ensures that you will never have unusable space in your tablespace .

  When you do not explicitly specify the type of extent management, Oracle Database determines extent management as follows:

  • If the CREATE TABLESPACE statement omits the DEFAULT storage clause, then the d atabase creates a locally managed autoallocated tablespace.

  • If the CREATE TABLESPACE statem ent includes a DEFAULT storage clause, then the database considers the following:

    • If you specified the MINIMUM EXTENT clause, the database evaluates whether the values of MINIMUM EXTENT, INI TIAL, and NEXT are equal and the value of PCTINCREASE is 0. If so, the database creates a locally ma naged uniform tablespace with extent size = INITIAL. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCTINCREASE is not 0, the database ignores any extent storage parameters yo u may specify and creates a locally managed, autoallocated tablespace.

    • If you did not specify MI NIMUM EXTENT clause, the database evaluates only whether the storage values of INITIAL and NEXT are equal and PCTINCREASE is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally m anaged and autoallocated.

  The following statement creates a locally managed tablespace named lmtbsb< /code> and specifies AUTOALLOCATE:

  CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmt
  bsb01.dbf' SIZE 50M
      EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
  
  

  AUTOALLOCATE causes the tablespace to be syste m managed with a minimum extent size of 64K. In contrast, dictionary-managed tablespaces have a minimum extent size of two database b locks. Therefore, in systems with block size smaller than 32K, autoallocated locally managed tablespace will be larger initially.

  The alternative to AUTOALLOCATE is UNIFORM. which specifies that the tablespace is managed with extents of uniform size. You can specify that size in the SIZE clause of UNIFORM. If you omit SIZE, t hen the default size is 1M.

  The following example creates a tablespace with uniform 128K extents. (In a database with 2K block s, each extent would be equivalent to 64 database blocks). Each 128K extent is represented by a bit in the extent bitmap for this fil e.

  CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
      EXTENT MANAGEMENT LOC
  AL UNIFORM SIZE 128K;
  
  

  You cannot specify the DEFAULT storage clause, MINIMUM EXTENT, or T EMPORARY when you explicitly specify EXTENT MANAGEMENT LOCAL. If you want to create a temporary locally managed t ablespace, use the CREATE TEMPORARY TABLESPACE statement.

  
  

  Note: When you allocate a datafile for a locally managed tablespace, you should allow s pace for metadata used for space management (the extent bitmap or space header segment) which are part of user space. For example, if specify the UNIFORM clause in the extent management clause but you omit the SIZE parameter, then the defau lt extent size is 1MB. In that case, the size specified for the datafile must be larger (at least one block plus space for the bitmap ) than 1MB.

  
  

  Specifying Segment Space Management in L ocally Managed Tablespaces

  When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT cla use lets you specify how free and used space within a segment is to be managed. You can choose either manual or automatic segment-spa ce management.

  • MANUAL: Manual segment-space management uses free lists to manage free space within segments. Free lists are lists of data blocks that have space available for inserting rows. With this form of segment-space m anagement, you must specify and tune the PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for schema objects created in the tablespace. MANUAL is the default.

  • AUTO: Automat ic segment-space management uses bitmaps to manage the free space within segments. The bitmap describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes availa ble in a data block, its new state is reflected in the bitmap. These bitmaps allow the database to manage free space automatically.

  You can specify automatic segment-space management only for permanent, locally managed tablespaces. Automatic segment -space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specif y and tune the PCTUSED, FREELIST S, and FREELIST GROUPS storage parameters for schema objects created in the tablespace. If you specify these attributes, the database ignores them.

  Automatic segment-space management delivers better space utilization than manual segment-space management. It is also self-tuning, in that it scales with increasing number of users or instances. In a Real Application Clusters environment, automatic segment-space management allows for a dynamic affinity of space to instances, thus avoiding the hard partitioning of space inherent with using free list groups. In addition, for many standard workload s, application performance with automatic segment-space management is better than the performance of a well-tuned application using m anual segment-space management.

  The following statement creates tablespace lmtbsb with automatic segment-space ma nagement:

  CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
      EXTENT MANAGEM
  ENT LOCAL 
      SEGMENT SPACE MANAGEMENT AUTO;
  
  

  The segment-space management you specify at tablespace creation time applie s to all segments subsequently created in the tablespace. You cannot subsequently change the segment-space management mode of a table space.

  
  

  Notes: If you spec ify AUTO segment management, then: If you set extent management to LOCAL UN IFORM, then you must ensure that each extent contains at least 5 database blocks. If you set exte nt management to LOCAL AUTOALLOCATE, and if the database block size is 16K or greater, then Oracle manages segment space by creating extents with a minimum size of 5 blocks rounded up to 64K.

  
  

  Locally managed tablespaces using automatic segment-space management can be created as sing le-file, or bigfile, tablespaces, as described in "Bigfile Tablespaces".

  Altering a L ocally Managed Tablespace

  You cannot alter a locally managed tablespace to a locally managed temporary tablespace, nor can you change its method of segment-space management. Coalescing free extents is unnecessary for locally managed tablespaces. Howev er, you can use the ALTER TABLESPACE statement on locally managed tablespaces for some operations, including the followi ng:

  • Adding a datafile. For example:

    ALTER TABLESPACE lmtbsb
       ADD DATAFIL
    E '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;
    
    

  • Altering tablespace availability (ONLINE/OFFLINE). See "Altering Tablespace Availability ".

  • Making a tablespac e read-only or read/write. See "Using Read-Only Tablespaces".

  • Renaming a datafil e, or enabling or disabling the autoextension of the size of a datafile in the tablespace. See Chapter 9, " Managing Datafiles and Tempfiles".

  Bigfile Tablespaces

  A bi gfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile. Traditional smallfile tablespaces , in contrast, can contain multiple datafiles, but the files cannot be as large. The benefits of bigfile tablespaces are the followin g:

  • A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.

  • Bi gfile tablespaces can reduce the number of datafiles needed for a database. An additional benefit is that the DB_FILES i nitialization parameter and MAXDATAFILES parameter of the CREATE DATABASE and CREATE CONTROLFILE statements can be adjusted to reduce the amount of SGA space required for datafile information and the size of the control file.

  • Bigfile tablespaces simplify database management by providing datafile transparency. SQL syntax for the < code>ALTER TABLESPACE statement lets you perform operations on tablespaces, rather than the underlying individual datafiles.

  Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space mana gement, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace can be bigfile tablespaces even if their segments are manually managed.

  
  

  Notes: Bigfile tablespaces are intended to be used with Automatic S torage Management (ASM) or other logical volume managers that supports striping or RAID, and dynamically extensible logical volumes.< /p> Avoid creating bigfile tablespaces on a system that does not support striping because of negative implic ations for parallel query execution and RMAN backup parallelization. Using bigfile tablespaces on platfo rms that do not support large file sizes is not recommended and can limit tablespace capacity. Refer to your operating system specifi c documentation for information about maximum supported file sizes.

  
  

  Creating a Bigfile Tablespace

  To create a bigfile tablespace, specify the BIGFILE keyword of the C REATE TABLESPACE statement (CREATE BIGFILE TABLESPACE ...). Oracle Databas e automatically creates a locally managed tablespace with automatic segment-spec management. You can, but need not, specify EXT ENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO< /code> in this statement. However, the database returns an error if you specify EXTENT MANAGEMENT DIC TIONARY or SEGMENT SPACE MANAGEMENT MANUAL. The remaining syntax of the st atement is the same as for the CREATE TABLESPACE statement, but you can only specify one datafile. For example:

  CREATE BIGFILE TABLESPACE bigtbs 
      DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G
  ...
  
  

  You can specify SIZE in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).

  If the default tablespace type wa s set to BIGFILE at database creation, you need not specify the keyword BIGFILE in the CREATE TABLESP ACE statement. A bigfile tablespace is created by default.

  If the default tablespace type was set to BIGFILE at database creation, but you want to create a traditional (smallfile) tablespace, then specify a CREATE SMALLF ILE TABLESPACE statement to override the default tablespace type for the tablespace that you are creating.

  
  

  See Also: "Supporting Bigfile Tablespaces During Database Creation"

  
  

  Altering a Bigfile Tablespace

  Two clau ses of the ALTER TABLESPACE statement support datafile transparency when you are using bigfile tablespaces:

  • RESIZE: The RESIZE clause lets you resize the sin gle datafile in a bigfile tablespace to an absolute size, without referring to the datafile. For example:

    ALTER TABLESPACE bigtbs RESIZE 80G;
    
    

  • AUTOEXTEND (used outside of the ADD DATAFILE clause):

    With a bigfile tablespace, you can use the AUT OEXTEND clause outside of the ADD DATAFILE clause. For example:

    ALTER
    TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;
    
    

  An error is raised if you specify an ADD DATAFILE< /code> clause for a bigfile tablespace.

  Identifying a Bigfile Tables pace

  The following views contain a BIGFILE column that identifies a tablespace as a bigfile tablespace:

  • DBA_TABLESPACES

  • USER_TABLESPACES

  • V$TABLESPACE

  You can also identify a bigfile tablespace by the relative file number of its single datafile. That number is 1024 on most platforms, but 4096 on OS/390.

  Dictionary-Man aged Tablespaces

  The default for extent management when creating a tablespace is locally managed. However, you can exp licitly specify a dictionary-managed tablespace. For dictionary-managed tablespaces, the database updates the appropriate tables in t he data dictionary whenever an extent is allocated or freed for reuse.

  Creating a Dictionary-Managed Tablespace

  The following statement creates the dictionary-managed tablespace tbsa:

  CREATE TABLESPACE tbsa
      DATAFILE '/u02/oracle/data/tbsa01.dbf' SIZE 50M
      EXTENT MANAGEMENT DICTIONARY
      DEFAULT S
  TORAGE (
          INITIAL 50K
          NEXT 50K
          MINEXTENTS 2
          MAXEXTENTS 50
          PCTINCREASE 0);
  
  

  The tabl espace has the following characteristics:

  • The data of the new tablespace is contained in a single datafi le, 50M in size.

  • The tablespace is explicitly created as a dictionary-managed tablespace by specifying < code>EXTENT MANAGEMENT DICTIONARY.

  • The default storage parameters for any segments created in thi s tablespace are specified.

  The parameters specified in the preceding example determine segment storage allocation i n the tablespace. These parameters affect both how long it takes to access data stored in the database and how efficiently space in t he database is used. They are referred to as storage parameters, and are described in the following table:

  Storage Parameter	Description
  INITIAL	Defines the size in bytes of the first extent in the segment
  NEXT	Defines the size in by tes of the second and subsequent extents
  < code>PCTINCREASE	Specifies the percent by which each extent, after the secon d (NEXT) extent, grows
  MINEXTENTS	Specifies the number of extents allocated when a segment is first created in the tablespace
  MAXEXTENT S	Specifies the maximum number of extents that a segment can have. Can also be specified as UNLIMITED.

  
  

  The MINIMUM EX TENT parameter on the CREATE TABLESPACE statement also influences segment allocation. If specified, it ensures th at all free and allocated extents in the tablespace are at least as large as, and a multiple of, a specified number of bytes. This cl ause provides one way to control free space fragmentation in the tablespace.

  
  

  See Also: "Managing Storage Parameters" Oracle Database SQL Reference for a complete description of storage parameters

  
  

  Specifying Tablespace D efault Storage Parameters

  When you create a new dictionary-managed tablespace , you can specify default storage parameters for objects that will be created in the tablespace. Storage parameters specified when an object is created override the default storage parameters of the tablespace containing the object. If you do not specify storage par ameters when creating an object, the object segment automatically uses the default storage parameters for the tablespace.

  Set the default storage parameters for a tablespace to account for the size of a typical object that the tablespace will contain (you est imate this size). You can specify different storage parameters for an unusual or exceptional object when creating that object. You ca n also alter your default storage parameters at a later time.

  You cannot specify default storage parameters for tablespaces th at are specifically created as locally managed.

  
  

  Note: If you do not specify the default storage parameters for a new dictionary-managed tablespace, Oracle Dat abase chooses default storage parameters appropriate for your operating system.

  
  

  Altering a Dictionary-Managed Tablespace

  < p>One common change to a database is adding a datafile. The following statement creates a ne w datafile for the tbsa tablespace:

  ALTER TABLESPACE tbsa
     ADD DATAFILE '/u02/oracle/dat
  a/tbsa02.dbf' SIZE 1M;
  
  

  You can also cha nge the default storage parameters of a tablespace using the ALTER TABLESPACE statement, as illustrated in the following example:

  ALTER TABLESPACE users
      DEFAULT STORAGE (
         NEXT 100K
         MAXEXTENTS 20
         PCT
  INCREASE 0);
  
  

  New values for the default storage parameters of a tablespace affect only objects that are subsequently crea ted, or extents subsequently allocated for existing segments within the tablespace.

  Other reasons for issuing an ALTER T ABLESPACE statement include, but are not limited to:

  • Coalescing free space in a tablespace. See < a href="#i1006606">"Coalescing Free Space in Dictionary-Managed Tablespaces".

  • Altering tablespace av ailability (ONLINE/OFFLINE). See "Altering Tablespace Availability ".

  • Making a tablespace read-only or read/write. See "Using Read-Only Tablespaces".

  • Adding or renaming a datafile, or enabling/disabling the autoextension of the size of a datafile in the tablespace. S ee Chapter 9, " Managing Datafiles and Tempfiles".

  Coalescing Free Space in Dictionary-Managed Tablespaces

  Over time, the free space in a dictio nary-managed tablespace can become fragmented, making it difficult to allocate new extents. This section discusses how to defragment free space and includes the following topics:

  • How Oracle Database Coalesces Free Spa ce

  • Manually Coalescing Free Space

  • Monitoring Free Space

  How Oracle Database Coalesces Free Space

  A free extent in a dictionary-managed tablespace is made up of a collection of contiguous free blocks. When allocating new extents to a tablespace segment, the database uses the free e xtent closest in size to the required extent. In some cases, when segments are dropped, their extents are deallocated and marked as f ree, but adjacent free extents are not immediately recombined into larger free extents. The result is fragmentation that makes alloca tion of larger extents more difficult.

  Oracle Database addresses fragmentation in several ways:

  • W hen attempting to allocate a new extent for a segment, the database first tries to find a free extent large enough for the new extent . Whenever the database cannot find a free extent that is large enough for the new extent, it coalesces adjacent free extents in the tablespace and looks again.

  • The SMON background process periodically coalesces neighboring free extents when the PCTINCREASE value for a tablespace is not zero. If you set PCTINCREASE=0, no coalescing of free ex tents occurs. If you are concerned about the overhead of ongoing coalesce operations of SMON, an alternative is to set PCTINCRE ASE=0, and periodically coalesce free space manually.

  • When a segment is dropped or truncated, a l imited form of coalescing is performed if the PCTINCREASE value for the segment is not zero. This is done even if PCTINCREASE=0 for the tablespace containing the segment.

  • Yo u can use the ALTER TABLESPACE ... COALESCE statement to manually coalesce any adjacent free extents.

  T he process of coalescing free space is illustrated in the following figure.

  Figure 8-1 Coalescing Free Space

  
  Description of the illustration admin017.gif
  
  

  
  

  Note: Coalescing free space is not necessary for locally managed tab lespaces, because bitmaps automatically track adjacent free space.

  
  

  Manually Coalescing Free Space

  If you find that frag mentation of space in a tablespace is high (contiguous space on your disk appears as noncontiguous), you can coalesce any free space using the ALTER TABLESPACE ... COALESCE statement. You must have the ALTER TABLESPACE system privilege to c oalesce tablespaces.

  This statement is useful if PCTINCREASE=0, or you can use it to supplement SMON and extent a llocation coalescing. If all extents within the tablespace are of the same size, coalescing is not necessary. This is the case when t he default PCTINCREASE value for the tablespace is set to zero, all segments use the default storage parameters of the t ablespace, and INITIAL=NEXT=MINIMUM EXTENT.

  The following statement coalesces free space in the tablespace tabsp_4:

  ALTER TABLESPACE tabsp_4 COALESCE;
  
  

  The COALESCE clause of the ALTER TABLESPACE statement is exclusive. You cannot specify any other clause in the same ALTER TABLESPACE s tatement.

  
  

  Note: The ALTER TABLESPACE ... COALESCE statement does not coalesce free extents that are separated by data extents. If you observe man y free extents located between data extents, you must reorganize the tablespace (for example, by exporting and importing its data) to create useful free space extents.

  
  

  Monitoring Free Space

  The following views provide information on the free space in a tablespace:

  • DBA_FREE_SPACE

  • DBA_FREE_SPACE_COALESCED

  The following statement displays the free space in tablespace tabsp_4:

  SELECT  BLOCK_ID, BYTES, BLOCKS 
       FROM  DBA_FREE_SPACE
       WHERE TABLESPACE_NAME = 'TABSP_4'
       ORDER BY BLOCK_ID;
  
  BLOCK_ID
     BYTES      BLOCKS     
  ---------- ---------- ---------- 
           2      16384          2          
           4      16384
   2          
           6      81920         10          
          16      16384          2          
          27      16384          2
          
          29      16384          2          
          31      16384          2          
          33      16384          2
      
          35      16384          2          
          37      16384          2          
          39       8192          1
  
          40       8192          1          
          41     196608         24          
  13 rows selected.
  
  

  This view shows that there is adjacent free space in tabsp_4 (for example, blocks starting with BLOCK_IDs 2, 4, 6, 16) that has not been coalesced. After coalescing the tablespace using the ALTER TABLESPACE statement shown previously, the resu lts of this query would read:

  BLOCK_ID   BYTES      BLOCKS     
  ---------- ---------- ---------- 
      2     131072         16          
          27     311296         38          
  2 rows selected.
  
  

  The DBA_FREE_SPAC E_COALESCED view displays statistics for coalescing activity. It is also useful in determining if you need to coalesce space.< /p>

  
  

  See Also: Oracle Database Reference for more information about these views

  
  

  Temporary Tablespaces

  A temporary tablespace contains transient data that persists only for the duration of the session. Temporar y tablespaces can improve the concurrence of multiple sort operations, reduce their overhead, and avoid Oracle Database space managem ent operations. A temporary tablespace can be assigned to users with the CREATE USER or ALTER USER statemen t and can be shared by multiple users.

  Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. Sort segments exist for every instance that performs sort operations within a given tab lespace. The sort segment is created by the first statement that uses a temporary tablespace for sorting, after startup, and is relea sed only at shutdown. An extent cannot be shared by multiple transactions.

  You can view the allocation and deallocation of spa ce in a temporary tablespace sort segment using the V$SORT_SEGMENT view. The V$TEMPSEG_USAGE view identifie s the current sort users in those segments.

  You cannot explicitly create objects in a temporary tablespace.

  
  

  See Also: Oracle Database Security Guide for information about cre ating users and assigning temporary tablespaces Oracle Database Reference for more information about the V$SORT_SEGMENT and V$TEMPSEG_USAGE< /code> views Oracle Database Performance Tuning Guide for a discussion on tuning sorts

  
  

  Creating a Locally Managed Temporary Tablespace

  Because space management is much simpler and more efficient in locally managed tablespaces, they a re ideally suited for temporary tablespaces. Locall y managed temporary tablespaces use tempfiles, which do not modify data outside of the temporary tablespace or generate any re do for temporary tablespace data. Because of this, they enable you to perform on-disk sorting operations in a read-only or standby da tabase.

  You also use different views for viewing information about tempfiles than you would for datafiles. The V$TEMPFIL E and DBA_TEMP_FILES views are analogous to the V$DATAFILE and DBA_DATA_FILES views.

  To create a locally managed temporary tablespace, you use the CREATE TEMPORARY TA BLESPACE statement, which requires that you have the CREATE TABLESPACE system privilege.

  The following sta tement creates a temporary tablespace in which each extent is 16M. Each 16M extent (which is the equivalent of 8000 blocks when the s tandard block size is 2K) is represented by a bit in the bitmap for the file.

  CREATE TEMPORARY TABLESPA
  CE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf' 
       SIZE 20M REUSE
       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
  
  

  Th e extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed e xtents of a uniform size. The Oracle Database default for SIZE is 1M. But if you want to specify another value for SIZE, you can do so as shown in the preceding statement.

  The AUTOALLOCATE clause is not allowed for tempo rary tablespaces.

  
  

  Note: O n some operating systems, the database 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 availa ble when the tempfiles are later used. Please refer to your operating system documentation to determine whether the database allocate s tempfile space in this way on your system.

  
  

  Creating a Bigfile Temporary Tablespace

  Just as for regular tablespaces, you can create single-file (bigfile) tem porary tablespaces. Use the CREATE BIGFILE TEMPORARY TABLESPACE statement to create a sin gle-tempfile tablespace. See the sections "Creating a Bigfile Tablespace" and "Altering a Bigfile Tablespace" for information about bigfile tablespaces, but consider that you are creating temporary tablespaces that use tempfiles instead of datafiles.

  Altering a Locally Managed Temporary Tablespace

  Except for adding a tempfile, as illustrated in the following exam ple, you cannot use the ALTER TABLESPACE statement for a locally managed temporary tablespace.

  ALTER TABLESPACE lmtemp
     ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;
  

  
  

  < p>Note: You cannot use the ALTER TABLESPACE statem ent, with the TEMPORARY keyword, to change a locally managed permanent tablespace into a locally managed temporary table space. You must use the CREATE TEMPORARY TABLESPACE statement to create a locally managed temporary tablespace.

  
  

  However, the ALTER DATABASE statement can be used to alter tempfiles.

  The following statements take offline and bring online temporary files:

  ALTER
  DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
  

  ALTER DATABASE TEMPFILE '/u02/oracle/dat
  a/lmtemp02.dbf' ONLINE;
  
  

  The following statement resizes a temporary file:

  ALTER DATABASE TE
  MPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;
  
  

  The following statement drops a temporary file and deletes the operati ng system file:

  ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP
      INCLUDING DATAFILES;
  
  

  The tablespace to which this tempfile belonged remains. A message is written to the alert file for the datafile that was d eleted. If an operating system error prevents the deletion of the file, the statement still succeeds, but a message describing the er ror is written to the alert file.

  It is also possible, but not shown, to use the ALTER DATABASE statement to enab le or disable the automatic extension of an existing tempfile, and to rename (RENAME FILE) a tempfile.

  Creating a Dictionary-Managed Temporary Tablespace

  In earlier releases , you could create a dictionary-managed temporary tablespace by specifying the TEMPORARY keyword after specifying the ta blespace name in a CREATE TABLESPACE statement. This syntax has been deprecated. It is still supported in case you are u sing dictionary-managed tablespaces, which are not supported by the CREATE TEMPORARY TABLEPSPACE syntax. If you do use t his deprecated syntax, the extent cannot be locally managed, nor can you specify a nonstandard block size for the tablespace.

  Oracle strongly recommends that you create locally managed temporary tablespaces containing tempfiles, as described in the preceding sections. The creation of new dictionary-managed tablespaces is scheduled for desupport.

  
  

  See Also: Oracle Database SQL Reference for syntax and semantics of the CREATE TEMPO RARY TABLESPACE and CREATE TABLESPACE ... TEMPORARY statements

  
  

  Altering a Dictionary-Managed Temporary Tablespace

  You can issue the ALTER TABLESPACE statement against a dictionary-managed temporary tablespace using many of the same keywords and clauses a s for a permanent dictionary-managed tablespace. Restrictions are noted in the Oracle Database SQL Reference.

  < br />

  Note: When you take dictionary-managed temporary tablespaces offline with t he ALTER TABLESPACE ... OFFLINE statement, returning them online does not affect their temporary status.

  
  

  You can change an existing permanent dictionary-managed tablespace to a temporary tablespace, using the ALTER TABLESPACE statement. For example:

  ALTER TABLESPACE
  tbsa TEMPORARY;
  

  Multiple Temporary Tablespaces: Using Tablespace Groups

  A tablespace group enables a user to consume temporary space from multiple tablespaces. A tablespace group has the following characteristics:

  • It contains at least one tablespace. There is no explicit limit on the maximum number of tablespaces that are contained in a group.

  • It shares the namespace of tablespaces, so its name cannot be the same as any tablespace.

  • You can specify a table space group name wherever a tablespace name would appear when you assign a default temporary tablespace for the database or a tempora ry tablespace for a user.

  You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed f rom it.

  Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespac e is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

  The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces.

  
  

  See Also: Oracle Datab ase Security Guide for more information about assigning a temporary tablespace or tablespace group to a user

  
  

  Creating a Tablespace Group

  You create a tablespace group implici tly when you include the TABLESPACE GROUP clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPA CE statement and the specified tablespace group does not currently exist.

  For example, if neither group1 n or group2 exists, then the following statements create those groups, each of which has only the specified tablespace as a member:

  CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf'
       SIZE 50M
      TABLESPACE GROUP group1;
  
  ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;
  

  Changing Members of a Tabl espace Group

  You can add a tablespace to an existing tablespace group by specifying the existing group name in the TABLESPACE GROUP clause of the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement.

  The following statement adds a tablespace to an existing group. It creates and adds tablespace lmtemp3 to group1, so that group1 contains tablespaces lmtemp2 and lmtemp3.

  CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf'
       SIZE 25M
       TABLESPACE GROUP group1;
  
  

  The following statement also adds a tablespace to an existing group, but in this case because tablespace lmtemp2 alread y belongs to group1, it is in effect moved from group1 to group2:

  ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;
  
  

  Now group2 contains both lmtemp and l mtemp2, while group1 consists of only tmtemp3.

  You can remove a tablespace from a group as sh own in the following statement:

  ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';
  
  

  Tablespace lmtemp3 no longer belongs to any group. Further, since there are no longer any members of group1, this result s in the implicit deletion of group1.

  Assigning a Tablespace Group as the Default Temporary Tabl espace

  Use the ALTER DATABASE ...DEFAULT TEMPORARY TABLESPACE statement to assign a tablespace group as the default temporary tabl espace for the database. For example:

  ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;
  
  

  Any user who has not explicitly been assigned a temporary tablespace will now use tablespaces lmtemp and lmt emp2.

  If a tablespace group is specified as the default temporary tablespace, you cannot drop any of its member tablesp aces. You must first be remove from the tablespace from the tablespace group. Likewise, you cannot drop a single temporary as long as it is the default temporary tablespace.