Oracle shrinking temporary tablespace

When sorting operations, re-indexing, and other large operations cannot be completed in the memory, the temporary tablespace will facilitate sorting. Generally, temporary tablespace is shared by multiple users and multiple sessions. The quota cannot be set for sessions in batches. An error "ORA-1652: unable to extend temp segment" will be reported when temporary tablespace is overconsumed and cannot be automatically expanded. The following describes how to release a temporary tablespace after over-expansion.

For more information, see:

Oracle tablespace and data files

Temporary Oracle tablespace management and impaired recovery

Oracle kill session

I. When to release temporary tablespace

When the session cursor for data retrieval is closed, the occupied temporary space is released.

The database is shut down, restarted (normally), and session log is off.

2. Release a large temporary tablespace

<Span style = "font-family: Verdana;">
1. view the current temporary tablespace
SQL> select * from v $ version where rownum <2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0-64bit Production
SQL> @ temp_sort_segment

+ ============================================== ========================================================== ===+
| Segment Name: The segment name is a concatenation of the |
| SEGMENT_FILE (File number of the first extent) |
| And the |
| SEGMENT_BLOCK (Block number of the first extent) |
| Current Users: Number of active users of the segment |
| Total Temp Segment Size: Total size of the temporary segment in MB |
| Currently Used Bytes: Bytes allocated to active sorts |
| Extent Hits: Number of times an unused extent was found in the pool |
| Max Size: Maximum number of MB ever used |
| Max Used Size: Maximum number of MB used by all sorts |
| Max Sort Size: Maximum number of MB used by an individual sort |
| Free Requests: Number of requests to deallocate |
+ ============================================== ========================================================== ===+
--> The temporary tablespace go_temp reaches 32 GB.
Tablespace Segment Current Currently Pct. Extent Max Used Max Sort Free
Name Users Used MB Used Hits Size MB Requests
----------------------------------------------------------------------------------
TEMP SYS.0.0 4 4 2 1,864 217 217 217 0
GO_TEMP SYS.0.0 0 0 0 1,305 32,766 367 0
**************--------------------------------------------------------
Sum 4 4 3,169 32,983 584 584 0

SQL> col tbsname format a15
SQL> select s. name tbsname, t. name, (t. bytes/1024/1024) mb, t. status
2 from v $ tablespace s, v $ tempfile t
3 where s. ts # = t. ts #;

TBSNAME NAME MB STATUS
----------------------------------------------------------------------------------
TEMP/u02/database/ORADB/temp/tempORADB. dbf 235 ONLINE
GO_TEMP/u02/database/ORADB/temp/ORADB_tempORADB.dbf 32767 ONLINE

SQL> @ temp_usage2 --> at this time, temp is used 4 MB, while GO_TEMP is not used.

TABLESPACE MB_TOTAL MB_USED MB_FREE
--------------------------------------------------
GO_TEMP 32767 0 32767
TEMP 218 4 214

2. observe and analyze the consumption of temporary tablespace
SQL> select count (*) from big_table; --> enable another session

COUNT (*)
----------
2000000

SQL> select * from big_table order by, desc; --> sort big_table

SQL> alter index pk_stock_tbl_arc rebuild; --> enable another session to recreate the index

SQL> @ temp_sort_segment. SQL --> the consumption of temp tablespace reaches 234 MB, and that of go_temp reaches 375 MB.

Tablespace Segment Current Currently Pct. Extent Max Used Max Sort Free
Name Users Used MB Used Hits Size MB Requests
----------------------------------------------------------------------------------
TEMP SYS.0.0 4 234 2 2,077 234 234 230 0
GO_TEMP SYS.0.0 1 375 1 2,055 32,766 375 375 0
**************--------------------------------------------------------
Sum 5 609 4,132 33,000 609 605 0

SQL> @ temp_sort_users. SQL --> obtain the Session of the current sorting

INST_ID SID_SERIAL Username osuser spid module program MB_USED TABLESPACE STATEMENTS
-----------------------------------------------------------------------------------------------------------
1 14456 SCOTT oracle 234 SQL * Plus oracle @ SZD TEMP 4
B (TNS V1-
V3)

1. 2480 GO_ADMIN oracle 375 SQL * Plus oracle @ SZD GO_TEMP 1
B (TNS V1-
V3)
3. Use resize to narrow down the temporary tablespace. If it cannot, go to the next step.
SQL> SELECT 'alter database tempfile' | a. name | ''' resize' | B. siz |'m; 'resize_command
2 FROM v $ tempfile
3, (SELECT ceil (tmsize. maxblk * bk. value/1024/1024) siz
4 FROM (SELECT nvl (MAX (segblk #), 128) maxblk
5 FROM v $ sort_usage) tmsize
6, (SELECT VALUE
7 FROM v $ parameter
8 where name = 'db _ block_size ') bk) B;

RESIZE_COMMAND
Bytes ----------------------------------------------------------------------------------------
Alter database tempfile '/u02/database/ORADB/temp/oradb_temporadb.dbf' resize 106 M;
Alter database tempfile '/u02/database/ORADB/temp/tempORADB. dbf' resize 106 M;

--> In fact, the temporary data file that occupies 32 GB has been reduced.
Alter database tempfile '/u02/database/ORADB/temp/oradb_temporadb.dbf' resize 106 M;

Database altered.

--> For ease of demonstration, it is assumed that TEMP is a large temporary tablespace and cannot be released.
--> The following adjustment indicates that the used space has exceeded the allocated space.
SQL> alter database tempfile '/u02/database/ORADB/temp/tempORADB. dbf' resize 106 M;
Alter database tempfile '/u02/database/ORADB/temp/tempORADB. dbf' resize 106 M
*
ERROR at line 1:
The ORA-03297: file contains used data beyond requested RESIZE value

SQL> select count (*) from v $ sort_usage where tablespace = 'temp '; --> there are currently unreleased temporary segments

COUNT (*)
----------
4

/*************************************** ***********/
/* Author: Robinson Cheng */
/* Blog: http://blog.csdn.net/robinson_0612 */
/* MSN: robinson_0612@hotmail.com */
/* QQ: 645746311 */
/*************************************** ***********/

4. Create a temporary tablespace for transit
SQL> create temporary tablespace temp2 tempfile '/u02/database/ORADB/temp/oradb_temp02.dbf'
2 size 10 m autoextend on;

Tablespace created.

--> If a large temporary tablespace is the default temporary tablespace, you must set the default temporary tablespace after the new temporary tablespace.
SQL> select property_name, property_value from database_properties
2 where property_name like 'default _ TEMP_TABLESPACE ';

PROPERTY_NAME PROPERTY_VALUE
--------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP

SQL> alter database default temporary tablespace temp2;

Database altered.

5. Transfer the user to the temporary tablespace for transit
--> Users in a large temporary tablespace need to migrate to the newly created temporary tablespace.
--> Query the dba_users view to query which users are located on a large temporary tablespace.
--> And use the following command to switch it to the new temporary tablespace.
Alter user <username> temporary tablespace temp2;

6. When no temporary segments are used in the large temporary tablespace, the large temporary tablespace can be deleted after all partitions are released.

SQL> show user; --> the temporary tablespace cannot be released because the current user is scott.
USER is "SCOTT"

SQL> conn/as sysdba --> switch to sysdba
Connected.

SQL> @ temp_usage2 --> the temporary segment has been released.

TABLESPACE MB_TOTAL MB_USED MB_FREE
--------------------------------------------------
GO_TEMP 106 0 106
TEMP 235 0 235

--> If the session is not released, kill the session if the session can be killed. Execute the session using the sid and serial # obtained earlier (provided that this situation is allowed ).
Alter system kill session '2017 9'

7. delete a large temporary tablespace.

SQL> alter tablespace temp tempfile offline; --> take it offline first

Tablespace altered.

SQL> drop tablespace temp including contents and datafiles; --> Delete temporary tablespace and corresponding files

Tablespace dropped.

SQL> select s. name tbsname, t. name, (t. bytes/1024/1024) mb, t. status
2 from v $ tablespace s, v $ tempfile t
3 where s. ts # = t. ts #;

TBSNAME NAME MB STATUS
----------------------------------------------------------------------------------
GO_TEMP/u02/database/ORADB/temp/ORADB_tempORADB.dbf 106 ONLINE
TEMP2/u02/database/ORADB/temp/ORADB_temp02.dbf 10 ONLINE

--> You can also use the following command to delete a single object.
Alter database tempfile '/u02/database/ORADB/temp/tempORADB. dbf' drop including datafiles; --> delete a single file

7. You can create the original temporary tablespace as needed and switch it out to this temporary tablespace. </span>

Iii. Summary

1. Follow the ORA-1652 error in the alert _ <sid>. log File and investigate the cause of the error. In some cases, the temporary tablespace cannot be expanded because of the current SQL statement. It is very likely that the previous SQL statement consumes 99% of the temporary tablespace, and an error occurs when the last SQL statement is executed. In such cases, you should investigate the previous SQL statement and adjust it to avoid excessive disk sorting.

2. Disable automatic expansion of temporary tablespace Based on Space pressure. Therefore, setting a reasonable size for the temporary tablespace becomes a problem. The personal solution is to first check the ORA-1652

Second, observe the peak of business peaks. For example, you can estimate the value of the Max Size (: Maximum number of MB ever used) field in the preceding query. If masters have better suggestions
Make a brick.

3. By restarting the database, the size consumed by the temporary tablespace cannot be reduced.

4. Before Oracle 11g, temporary tablespace is usually created to reduce the space. It is not perfect because temporary segments cannot be deleted because they are not released. You can use the following command to complete the 11g operation:

Alter tablespace temp shrink space;

Alter tablespace temp shrink tempfile '<dir> 'keep n <mb/kb>;

5. The system's default temporary tablespace cannot be deleted. Therefore, if the system's default temporary tablespace is too large to be deleted, a new system default temporary tablespace should be created.

6. before deleting a large temporary tablespace, users should first set it to the temporary tablespace for transit, and then reset it to the original state.

7. Reduce the primary task of disk sorting to adjust the SQL statement, such as avoiding Cartesian Product and adding reasonable indexes to the table. Second, consider whether the PGA value is set properly.