This content has been marked as final. Show 3 replies
If there is no free space in a tablespace, dba_free_space doesn't produce results.
Senior Oracle DBA
Error: ORA-01652: unable to extend temp segment by string in tablespace string
Cause: Failed to allocate an extent for temporary segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated
what query is creating this issue?In first place, you have to know what query is the problem before add more space in temp tablespace, to see it execute:
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace,T.segtype
ORDER BY mb_used,sid_serial;
Edited by: Fran on 12-abr-2012 5:42
I replicated the error on my intranet site and viewed the log files. Do I need to be in the database directory to excute that query? I'm not a Oracle expert. I just took over this database and learning Oracle at the same time.