Oracle 11g Shrink Temporary Tablespace and Temporary Table Enhancement(原创)

Shrinking Temporary Tablespaces
When a large job that uses a temporary tablespace finishes executing, the database doesn’t immediately release the space used by the job in the temporary tablespace, even after the job completes. You can get the free space back faster sometimes by dropping the temporary tablespace and creating a smaller one instead, but then it may not be easy to do this on a live database because users may require the temporary tablespace for various operations. Oracle Database 11g lets you shrink a locally managed temporary tablespace online, thus enabling you to control the space allocated to temporary operations in the database. You can shrink both temporary tablespaces and individual tempfiles.
Use the shrink space clause within an alter tablespace command to shrink a temporary tablespace, as shown here:
SQL> alter tablespace temp shrink space;
To shrink a tempfile, use the shrink tempfile clause, as shown here:
SQL> alter tablespace temp shrink
     tempfile '/u01/app/oracle/oradata/or11/temp01.dbf';
The shrink space command in the first example shrinks all tempfiles to a database-determined minimum size, 1 MB. The database takes into account the temporary tablespace storage requirements when determining the minimum size of the tempfiles. You can override this default behavior by specifying a minimum size for the temporary tablespace after a shrink operation, as shown here:
SQL> alter tablespace temp shrink space keep 100m;
The keep clause lets you specify the minimum value for the temporary tablespace named TEMP. The following examples illustrate how Oracle approaches a temporary tablespace shrinking operation. In this example, the temporary tablespace TEMP has two tempfiles, each sized at 1 GB, thus making the total size of the temporary tablespace 2 GB. You issue the following alter tablespace statement to shrink the temporary tablespace to 1 GB.
SQL> alter tablespace temp shrink space keep 1000m;
Tablespace altered.
Because the combined size of the two tempfiles in the TEMP tablespace is 2 GB, you’d assume that Oracle would shrink both tempfiles to about 500 MB each, to get a total of 1 GB, when you issue the alter tablespace statement shown here. However, this isn’t what happens, as you can see by issuing the following query:
SQL> select file#, name, bytes/1024/1024 MB from v$tempfile;
FILE#   NAME                                           MB
------  ------------------------------------        --------
1      /u01/app/oracle/temp/temp01.dbf              999.9375
2      /u01/app/oracle/temp/temp02.dbf
Oracle does shrink the TEMP tablespace from 2 GB to 1 GB, but not by shrinking both tempfiles by an equal amount. It shrinks the file temp01.dbf by less than 1 MB and the file temp02.dbf by over 999 MB. You can specify a minimum space that the database must retain in a specific tempfile, by specifying the keep clause in the alter tablespace . . . shrink statement, as follows:
SQL> alter tablespace temp shrink space
     tempfile '/u01/app/oracle/temp02.dbf'
     keep 500m;
Tablespace altered.
This statement will shrink just the datafile temp02 and leave the other tempfiles in the tablespace alone.
You can query the new DBA_TEMP_FREE_SPACE view to get information about temporary tablespace usage, as shown in this example:
SQL> select * from dba_temp_free_space;
TABLESPACE_NAME  TABLESPACE_SIZE  ALLOCATED_SPACE  FREE_SPACE
---------------  ---------------  ---------------  -----------
TEMP             41943040         41943040         40894464
The column definiton as following:

TABLESPACE_SIZE NUMBER: Total size of the tablespace, in bytes
ALLOCATED_SPACE NUMBER: Total allocated space, in bytes, including space that is currently allocated and used and space that is currently allocated and available for reuse
FREE_SPACE NUMBER: Total free space available, in bytes, including space that is currently allocated and available for reuse and space that is currently unallocated
Tablespace Option for Creating Temporary Tables
When you created a global temporary table in Oracle Database 10g, you didn’t have to specify a tablespace. In Oracle Database 11g, you can specify a tablespace clause when creating a temporary table. If you omit the tablespace clause, the database creates the global temporary table in the default temporary tablespace for the database. The database also stores the indexes you create on the global temporary table in the same tablespace as the temporary table, you can't designate other tablespace for tempoary index.
The ability to specify the tablespace when creating a global temporary table means that you can now assign a proper extent size for a temporary table to deal with its sort usage. Different tables might use the temporary space differently, and the capability to assign different extent sizes to them leads to better performance.

 

参 考至: 《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》

《Oracle® Database Reference 11g Release 1 (11.1)》

http://www.oracle-base.com/articles/11g/temporary-tablespace-enhancements-11gr1.php

本文原创，转载请注明出处、作者

如有错误，欢迎指正

邮箱:czmcj@163.com