FPSAPP.ADM_TEMP_UNDO_SPACE

>

> FPSAPP.WIP_WAFER_HIST_LOOP

View FPSADMIN.ADM_TEMP_UNDO_SPACE

This view gives information about the space available in the TEMP and UNDO tablespaces. It is formatted all in one row so that it can be cross joined to insert into log files. This query helps diagnose the cause when we encounter the dreaded "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP" error plus the comments contains some more details and information. DBA_TEMP_FILES is a basic view to just like the files and the temporary tablespace and the bytes: SELECT tablespace_name, file_name, bytes, autoextensible, maxbytes, status FROM dba_temp_files WHERE tablespace_name in (select tablespace_name from dba_tablespaces where contents = 'TEMPORARY'); DBA_TEMP_FREE_SPACE is the most important view. The columns are: -TABLESPACE_NAME - temporary tablespace name -TABLESPACE_SIZE - total size in bytes -ALLOCATED_SPACE - size in bytes for (allocated space in use) + (allocated space for reuse) -FREE_SPACE - size in bytes for (allocated space for reuse) + (unallocated space) Please note that allocated space for reuse is part of both allocated_space and free_space. Normally the entire tablespace_size will be allocated but most of it will be available to reuse and therefore in free space. If you want to shrink the file, meaning make the tablespace_size only equal to the allocated space in use, a DBA can do that with this query. Assuming that autoextend is enabled then it will just expand as needed. alter tablespace YOUR_TEMP_TABLESPACE_NAME shrink space; Here are some example results when we did this: Before shrink: tablespace_size and allocated_size both 32 GB which is the maxbytes, free_size nearly zero when CTM_SUMMARY running After shrink: tablespace_size and allocated_size both about 30 MB (yes MB not GB), free_size most of it when nothing was running 30 seconds after starting CTM_SUMMARY: tablespace_size and allocated_size both about 170 MB, free_size 0 as it was autoextending After 60 seconds: 700 MB and 0 free After 20 minutes: 4 GB and 0 free After complete: 8 GB and all 8 GB free