Oracle 11G Cleanup Temp table space ____oracle

I. Temporary table space contraction

1.1 Description

About Oracle's temporary tablespace, a blog has been collated before:

Oracle Temp temporary table space

http://blog.csdn.net/tianlesoftware/article/details/4697417

The following operations can take up a large amount of temporary:

1, users perform IMP/EXP Import export operations, will use a large number of temporary segments

2, the user in Rebuild index

3. Execute CREATE TABLE ... as statement

4, mobile users under the data to other table space

A large number of sort operations can cause a significant increase in temporary tablespace space. To improve performance, after the sorting area is physically allocated, they are managed in memory to avoid later physical recycling. As a result, the disk contains a huge temporary file until it is deleted. One possible workaround would be to create a new temporary tablespace with a smaller file, set the new tablespace to the user's default temporary tablespace, and then delete the old tablespace. However, there is a disadvantage that the procedure requires that the old temporary table space be deleted without an active sort operation.

Starting with Oracle DATABASE11G version 1, you can use the Alter tablespaceshrink Space command to shrink temporary tablespaces, or you can use the ALTER tablespace shrinktempfile command to shrink temporary files. For both commands, you can specify an optional keep clause that defines the lower bound of the table space/temp file to shrink to.

If you omit the Keep clause, the database tries to shrink the tablespace/temp file (the total space of all the currently used extents) whenever the other storage properties are met. This operation needs to be performed online. However, if some of the currently used extents are allocated that exceed the shrinkage estimate, the system waits for the extents to be freed to complete the shrink operation.

Note:

The ALTER databasetempfile RESIZE command usually fails because of ORA-03297 because the temporary file contains more data than is required for the RESIZE value.

In contrast to alter TABLESPACE SHRINK, the alter DATABASE command does not attempt to deallocate after a sorted area is allocated.

Before Oracle 11g, the temp table space was used, although it could be freed, but the amount of tablespace usage was shown to be 100%, you can use the following script to view the actual usage of each data file in the temporary tablespace:

Set PageSize 50

Col Tablespace_name for A20

Col "tempfile name" for A42

Set Linesize 300

Select F.tablespace_name,

D.file_name "Tempfile name",

Round ((F.bytes_free + f.bytes_used)/1024/1024, 2) "Total MB",

Round ((F.bytes_free + f.bytes_used)-nvl (p.bytes_used, 0))/1024/1024, 2) "Free MB",

Round (NVL (p.bytes_used, 0)/1024/1024, 2) "Used MB",

Round (Round (NVL (p.bytes_used, 0)/1024/1024, 2)/round ((F.bytes_free + f.bytes_used)/1024/1024, 2)) *100,2) as "Used_ra Te (%) "

From SYS. V_$temp_space_header f,dba_temp_files D, SYS. V_$temp_extent_pool P

where f.tablespace_name (+) = D.tablespace_name

and f.file_id (+) = d.file_id

and p.file_id (+) =d.file_id;

1.2 dba_temp_free_space View

The dictionary view is a newly added view of Oracle 11g, which is used to view temporary space usage information at the table space level. This information is exported from a variety of existing views.

(1) List of temporary space usage information

(2) Temporary table Space Utilization Center Point

Description of Column Name

Tablespace_name the name of the table space

Total size of the tablespace_size tablespace (in bytes)

Allocated_space the total allocated space (in bytes), including the space currently allocated and in use, and the currently allocated and reusable space

Free_space The total space available (in bytes), including currently allocated, reusable, and currently unallocated space

1.3 Table space options for creating temporary tables

Starting with Oracle DATABASE11G version 1, you can specify the TABLESPACE clause when creating a global temporary table.

If no table space is specified, the global temporary table is created in the default temporary table space. In addition, indexes created in temporary tables are created in the same temporary table space as the temporary table.

Note:

You can find tablespace in Dba_tables to store the global temporary table.

Such as:

CREATE Temporary Tablespace Temp

Tempfile ' tbs_temp.dbf ' SIZE 600m reuseautoextend on MAXSIZE

Unlimited

EXTENT MANAGEMENT Local uniform SIZE 1m;

CREATE GLOBAL Temporary TABLE temp_table (CVARCHAR2 (10))

On COMMIT DELETE ROWS tablespace temp;

two. Sample

2.1 View Dba_temp_free_space

Sql> Set Lin 160

Sql> Col Tablespace_name for A20

Sql> Col tablespace_size for 99999999999

Sql> Col allocated_space for 99999999999

Sql> Col free_space for 99999999999

Sql> select * from Dba_temp_free_space;

Tablespace_name tablespace_size Allocated_space Free_space

-------------------- ------------------------------ ------------

TEMP 524288000 7340032 523239424

Sql> Select 524288000/1024/1024| | ' M ' from dual;

5242

----

500M

--the temp table space here is 500M.

2.2 Perform the online shrink operation of the temp table space:

sql> alter tablespace temp shrink spacekeep 400M;

Tablespace altered.

Sql> select * from Dba_temp_free_space;

Tablespace_name tablespace_size Allocated_space Free_space

-------------------- ------------------------------ ------------

TEMP 420478976 1048576 419430400

Sql> Select 420478976/1024/1024| | ' M ' from dual;

4204

----

401M

2.3 Shrink Data file

--If you have more than one temp data file, you can also specify a specific temp data file to shrink:

Sql> Col file_name for A50

sql> select file_name fromdba_temp_files;

file_name

--------------------------------------------------

/u01/app/oracle/oradata/anqing/temp01.dbf

sql> alter tablespace temp shrinktempfile '/u01/app/oracle/oradata/anqing/temp01.dbf ' keep 300M;

Tablespace altered.

Sql> select * from Dba_temp_free_space;

Tablespace_name tablespace_size Allocated_space Free_space

-------------------- ------------------------------ ------------

TEMP 315613184 1040384 314572800

Sql> Select 315613184/1024/1024| | ' M ' from dual;

315613184/10

------------

300.9921875M

Sql>

2.4 Keep Option Description

The KEEP option specifies the minimum value of the tablespace or data file shrink when compressed, or if the command is not executed, the tablespace or data file is compressed to the minimum.

sql> alter tablespace temp shrink space;

Tablespace altered.

Sql> select * from Dba_temp_free_space;

Tablespace_name tablespace_size Allocated_space Free_space

-------------------- ------------------------------ ------------

TEMP 2088960 1040384 1048576

Sql> Select 2088960/1024/1024| | ' M ' fromdual;

2088960/10

----------

1.9921875M

--it's been pressed directly to 2 m. The Temp table space is too small to have an effect on performance, so it is recommended that you use keep to specify a minimum value when shrink.

Reproduced in: http://blog.csdn.net/tianlesoftware/article/details/8225395