TEMP space error

dealing with ORA-01652 : unable to extend temp segment by 128 in tablespace FCPTEST


alter system set events '1652 trace name errorstack level 1';

ALTER SESSION SET EVENTS '1652 trace name errorstack level 1 ';


or
alter system set events '1652 trace name errorstack level 1 ';
alter system set events '1652 trace name errorstack off ';


To deactivate:

ALTER SESSION SET EVENTS '1652 trace name context off';
ALTER SYSTEM SET EVENTS '1652 trace name context off';


monitoring sort space by statement-
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;


alter database tempfile '/metrix/mtrx01/oracle/mtrx/temp01.dbf' resize 8G;

monitoring sort space by session-
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,
COUNT(*) sort_ops
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
ORDER BY sid_serial;

monitoring temporary segments
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;


1. Obtain the Oracle process identifier or the Operating System process
identifier (SPID) from v$process:

> sqlplus /nolog
SQL> connect / as sysdba
SQL> select pid, spid, username from v$process;

PID SPID USERNAME
---- ----- --------
8 25807 oracle


2. Attach to the process using ORADEBUG.

Using the Oracle process identifier:

SQL> oradebug setorapid 8

Unix process pid: 25807, image: oracleV804


- or -


Using the Operating System process identifier:

SQL> oradebug setospid 25807

Oracle pid: 8, Unix process pid: 25807, image: oracleV804


3. Turn on SQL Trace for the session.

SQL> oradebug event 10046 trace name context forever, level 12

Statement processed.


4. Turn off the SQL trace for the session.


SQL> oradebug event 10046 trace name context off


5. Format trace file using TKPROF.