Problém
Tue Feb 02 10:44:05 2016
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
Analýza
SQL> set linesize 130
SQL> SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*32768/1024/1024 as total_MB,
used_blocks*32768/1024/1024 as used_MB,
free_blocks*32768/1024/1024 as free_MB
FROM gv$sort_segment;
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS TOTAL_MB USED_MB FREE_MB
——————————- ———— ———– ———– ———- ———- ———-
TEMP 8233984 8233984 0 257312 257312 0
TEMP 154112 128 153984 4816 4 4812
SQL> SELECT tablespace_name, file_name, bytes/1024/1024 size_in_mb from dba_temp_files;
TABLESPACE_NAME FILE_NAME SIZE_IN_MB
————————————————————————–
TEMP +DATA/racdb/tempfile/temp.337.861279369 32767
TEMP +DATA/racdb/tempfile/temp.283.861279471 32767
SQL> select tablespace_name, inst_id, file_id, extents_cached, extents_used from gv$temp_extent_pool order by 1,2;
TABLESPACE_NAME INST_ID FILE_ID EXTENTS_CACHED EXTENTS_USED
—————————— ———- ———- ————– ————
TEMP 1 2 31563 31562
TEMP 1 1 32765 32765
TEMP 2 1 1 1
TEMP 2 2 1203 0
ASM
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 2740379 71610 0 71610 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 279284 262426 0 262426 0 N FRA/
MOUNTED NORMAL N 512 4096 1048576 6282 5356 2094 1631 0 Y OCRVOTE/
SQL> select INST_ID,TABLESPACE_NAME,SEGMENT_FILE,MAX_SIZE,MAX_USED_BLOCKS from gv$sort_segment where TABLESPACE_NAME=’TEMP‘;
INST_ID TABLESPACE_NAME SEGMENT_FILE MAX_SIZE MAX_USED_BLOCKS
———- ——————————- ———— ———- —————
1 TEMP 0 65531 8387968
2 TEMP 0 9108 1165824
SQL> show parameter block
NAME TYPE VALUE
———————————— ———– ——————————
db_block_size integer 8192
db_file_multiblock_read_count integer 128
used= MAX_USED_BLOCKS * db_block_size = 63,99 GB
max_size=65531 / 1024 = 63,9GB
Řešení
SQL> alter tablespace TEMP add tempfile ‚+DATA‘ size 15000M;
Tablespace altered.
Kontrola
SELECT tablespace_name, file_name, bytes/1024/1024 size_in_mb from dba_temp_files where tablespace_name=’TEMP‘;
TABLESPACE_NAME FILE_NAME SIZE_IN_MB
————————————————————————–
TEMP +DATA/racdb/tempfile/temp.337.861279369 32767
TEMP +DATA/racdb/tempfile/temp.283.861279471 32767
TEMP +DATA/racdb/tempfile/temp.293.902770917 15000
MOS
ORA-1652 Error Troubleshooting (Doc ID 793380.1)
How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)
How Do You Find Who And What SQL Is Using Temp Segments (Doc ID 317441.1)