Permanent tablespace INDEX_TBS is being used and has 20gb of space free #此时无法扩展临时表空间的问题当属第2种情形
TIME 1 : Session 1 begins a CREATE INDEX command with the index stored in INDEX_TBS
TIME 2 : Session 1 exhausts all of the free space in INDEX_TBS as a result the CREATE INDEX abends
TIME 3 : SMON cleans up the temporary segments that were used to attempt to create the index
TIME 4 : Queries are run against the views V$SORTSEG_USAGE or V$TEMSEG_USAGE ... and it is found that the INDEX_TBS has no space used (this is normal)
#下面是Solution部分
First it is important to forget what is known about past behavior of the instance as the current tablespace size is insufficient to handle the demand by current sessions
There are three recommended methods of supplying sufficient storage space to meet the needs of current sessions by increasing the size of your temporary tablespace
1) Set one or more of the tempfiles|datafiles for the tablespace to AUTOEXTEND with MAXSIZE set ... so that you do not exhaust all available disk volume space
(discuss this with a System Administrator)
After a month or so of having this setting ... AUTOEXTEND can be disabled .. as it is likely that the system has found its true high watermark for temporary segment usage
(This is the most recommended method as it allows the database instance to find its own high watermark)
2) Monitor the temporary segment usage via queries like
SELECT sum(blocks)*
FROM v$tempseg_usage
WHERE tablespace = '
and resize one or more of the tempfiles|datafiles for the tablespace as the tablespace becomes close to exhausted
3) Add a tempfile|datafile to the temporary tablespace with the problem and monitor usage as described in #2
Another good idea is to monitor temporary tablespace usage over time to determine what queries are consuming the temporary space space
For example: How Can Temporary Segment Usage Be Monitored Over Time (Doc ID 364417.1)
This note was written to monitor temporary tablespaces .. but may be able to be modified to also monitor permanent tablespaces
4、本案例故障解决方案
SQL> @temp_sort_segment.sql
+==================================================================================+
| 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 bytes |
| 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 bytes ever used |
| Max Used Size : Maximum number of bytes used by all sorts |
| Max Sort Size : Maximum number of bytes used by an individual sort |
| Free Requests : Number of requests to deallocate |
+==================================================================================+
Tablespace Segment Current Total Temp Currently Pct. Extent Max Max Used Max Sort Free
Name Name Users Segment Size Used Bytes Used Hits Size Size Size Requests
-------------- -------- ------- ---------------- ---------------- ---- -------- ---------------- ---------------- ---------------- --------
TEMP SYS.0.0