Shulou(Shulou.com)06/02 Report--

For temporary tablespaces, it is mainly used to do some sort operations. Oracle's use strategy for temporary tablespaces is different from that of other permanent tablespaces. When the first sorting operation using temporary tablespaces starts, the temporary period is created with the corresponding view: SQL > select table_name from dict where table_name like 'VendTMP%'. TABLE_NAME--V$TEMPFILEV$TEMPORARY_LOBSV$TEMPSEG_USAGEV$TEMPSTATV$TEMP_CACHE_TRANSFERV$TEMP_EXTENT_MAPV$TEMP_EXTENT_POOLV$TEMP_HISTOGRAMV$TEMP_PINGV$TEMP_SPACE_HEADERSQL > SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE 'VroomSORT%' TABLE_NAME--V$SORT_SEGMENTV$SORT_USAGE1, v$sysstat (View database sorting information) SQL > select name,value from v$sysstat where name like 'sort%' NAME VALUE-----sorts (memory) 3272sorts (disk) 0sorts (rows) 164422, v$sort_usage=v$tempseg_usage (View temporary tablespace usage) SQL > select username,session_num from v$tempseg_usage USERNAME SESSION_NUM---SYS 8SYS 8SYS 83, v$sort_segment (extent Management in temporary period) SQL > select TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS,ADDED_EXTENTS from v$sort_segment where tablespace_name='TEMP' TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS ADDED_EXTENTS- 902 0902 898v$temp_space_header (this view is used to query temporary tablespace usage) SQL > select tablespace_name,BYTES_USED,BYTES_FREE from v$temp_space_header TABLESPACE_NAME BYTES_USED BYTES_FREE---TEMP 946864128 0

Extent will be assigned to this temporary period, but when the sorting operation is completed, the temporary period will not be deleted, but will only be marked as Free by oracle so that other sorting operations can continue to use this temporary period. From here, we can also understand that there can be only one temporary period for a temporary table space. For extent management in segments, use v$sort_segment:SQL > select TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS,ADDED_EXTENTS from v$sort_segment where tablespace_name='TEMP' TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS ADDED_EXTENTS- 11770 6571 when the temp tablespace in our database is no longer used by the session, the corresponding temporary period will be released. Extent is marked as FreeTOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS ADDED_EXTENTS- 364 0364 6818. As can be seen from the above, as soon as the temporary period is used, oracle will assign the corresponding extent and continue to add extent to the segment. As soon as the sorting operation is completed, oracle will immediately release the used segment. Thus, the characteristic that the extent in the segment is marked as Free.oracle also makes the data file of the temporary tablespace of oracle become larger and larger, so we must observe and maintain the temporary tablespace regularly. Simulated temporary tablespaces are heavily consumed: (simulating a sort operation) select * from dba_tab_columns a dbathing tablespace columns a.ownerjournal b.tabletable name; after running it for a period of time, it is found that the corresponding oracle directory becomes larger and the temporary tablespace also becomes larger. SQL >! df-kFilesystem 1K-blocks Used Available Use% Mounted on/dev/sda1 8254240 2244484 5590464 29% / none 403460 403460 0% / dev/shm/dev/sda2 10317860 6550784 3242956 67% / oracleSQL > select username,session_num from v$sort_usage where tablespace='TEMP' USERNAME SESSION_NUM---SYS 8 at this time we look for the corresponding temporary data file usage: SQL > select max (SEGBLK#) from vandalism sortusability Max (SEGBLK#)-9SQL > select 8192 from dual 1024 from dual We can see that although the temporary table space is very large, the use of temporary table space is not large. We use resize's method to reduce the data file: SQL > alter database tempfile'/ oracle/app/oradata/orcl/temp01.dbf' resize 100m. Database altered.SQL >! ls-1 / oracle/app/oradata/orcl/temp01.dbf-rw-r- 1 oracle dba 104865792 data file has shrunk to 100m to reduce temporary tablespaces. Another way to free up disk space is to replace temporary tablespaces: create temp:SQL > create temporary tablespace temp2 2 tempfile'E:\ XXXXXX\ XXXXX\ XXXX.dbf' size 100m autoextend on Verify that the original temp is free: SQL > SELECT USERNAME,USER FROM V$SORT_USAGE WHERE TABLESPACE='TEMP'SQL > alter database default temporary tablespace TEMP2SQL > drop tablespace TEMP including contents and datafiles to see if the temp tablespace is deleted correctly. Check to see if the space is free.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.