if you are getting the error “ORA-01652: unable to extend temp segment by 128 in tablespace TEMP” you could just increase the temp tablespace, i.e. by adding an additional tempfile… the smarter way would be to check who is consuming so much temp tablespace…<\/p>\n
The following SQL is giving you a live view:<\/p>\n
\r\nSELECT\r\n\t s.sid\r\n\t,s.serial# \r\n\t,s.osuser\r\n\t,s.user#\r\n\t,s.username \r\n\t,s.sql_id\r\n\t,s.module\r\n\t,s.program \r\n\t,round(su.blocks * tbs.block_size \/ 1024 \/ 1024 \/1024) gb_used \r\n\t,su.tablespace\r\n\t--,sa.sql_text -- uncomment if you want to see the SQL text\r\n\t--,sa.sql_fulltext -- uncomment if your Client can handle CLOB's (i.e. SQL Developer)\r\nFROM v$sort_usage su \r\ninner join v$session s on su.session_addr=s.saddr\r\ninner join dba_tablespaces tbs on su.tablespace=tbs.tablespace_name\r\ninner join v$sqlarea sa on s.sql_id=sa.sql_id\r\nwhere su.blocks*tbs.block_size > 1024*1024*1024 -- more than 1 GB TEMP\r\nORDER BY gb_used desc; \r\n<\/pre>\nand if you have to check what happende some time ago (take care: Diagnosting & Tuning pack required!)<\/p>\n
\r\nSELECT\r\n snap_id\r\n ,sample_time\r\n ,session_id\r\n ,session_serial#\r\n ,sql_id\r\n ,round(temp_space_allocated\/1024\/1024\/1024) gb_used\r\n FROM dba_hist_active_sess_history\r\nWHERE temp_space_allocated > 1024*1024*1024\r\nORDER BY sample_time;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"if you are getting the error “ORA-01652: unable to extend temp segment by 128 in tablespace TEMP” you could just increase the temp tablespace, i.e. by adding an additional tempfile… the smarter way would be to check who is consuming … Continue reading