Summary of Oracle temporary tablespace issues

1> User temporary default tablespace Switching

-- Query the user's default temporary tablespace SQL> SelectUsername,Temporary_tablespaceFromDba_users; USERNAME TEMPORARY_TABLESPACE ------------------------------------------------------------ MGMT_VIEW TEMP SYS TEMP SYSTEM TEMP DBSNMP TEMP SYSMAN TEMP SCOTT TEMP HR TEMP OUTLN TEMP MDSYS TEMP ORDSYS TEMP EXFSYS TEMP DMSYS TEMP WMSYS TEMP CTXSYS TEMP ANONYMOUS TEMP XDB TEMP ORDPLUGINS TEMP SI_INFORMTN_SCHEMA TEMP OLAPSYS TEMP TSMSYS TEMP BI TEMP PM TEMP MDDATA TEMP IX TEMP SH TEMP DIP TEMP OE TEMP 27RowsSelected.

-- Create a new temporary tablespace SQL> create temporary tablespace temp2 tempfile '/u02/temp1.dbf' size 10M; Tablespace created. -- If necessary -- Alter tablespace temp add tempfile '/u02/temp2.dbf '10 M;

-- Switch SQL> alter database default temporary tablespace temp2 ; Database altered. -- Query whether switchover is successful SQL> SelectUsername,Temporary_tablespaceFromDba_users; USERNAME TEMPORARY_TABLESPACE ------------------------------------------------------------ MGMT_VIEW TEMP2 SYS TEMP2 SYSTEM TEMP2 DBSNMP TEMP2 SYSMAN TEMP2 SCOTT TEMP2 HR TEMP2 OUTLN TEMP2 MDSYS TEMP2 ORDSYS TEMP2 EXFSYS TEMP2 DMSYS TEMP2 WMSYS TEMP2 CTXSYS TEMP2 ANONYMOUS TEMP2 XDB TEMP2 ORDPLUGINS TEMP2 SI_INFORMTN_SCHEMA TEMP2 OLAPSYS TEMP2 TSMSYS TEMP2 BI TEMP2 PM TEMP2 MDDATA TEMP2 IX TEMP2 SH TEMP2 DIP TEMP2 OE TEMP2 27RowsSelected.

2> full table space
Method 1: restart the database Method 2:

SQL>alter tablespace temp increase 1; SQL>alter tablespace temp increase 0;

Method 3: 1. check who is using the temporary segment. SELECT username,
Sid,
Serial #,
SQL _address,
Machine,
Program,
Tablespace,
Segtype,
Contents
FROM v $ session se,
V $ sort_usage su
WHERE se. saddr = su. session_addr
Or -- Display the session of the top 5 temporary segments Select * from (select sess. username, sess. SID, sess. serial #, segtype, blocks * 8/1024 "MB", SQL _textfrom v $ sort_usage sort, v $ session sess, v $ SQL sqlwhere sort. SESSION_ADDR = sess. SADDRand SQL. ADDRESS = sort. SQLADDR (+) order by blocks desc) awhere rownum <6;
2. processes using temporary segments SQL > Alter   System kill   Session   'Sid, serial #' ;
3. shrink the temporary tablespace SQL> Alter tablespace TEMP coalesce;
-- Quickly contract a temporary segment (suitable for scenarios where the tablespace in the temporary segment is slow) alter tablespace temp default storage (pctincrease 1); alter tablespace temp default storage (pctincrease 0 );
Method 4: 1. Determine the temp tablespace ts #

SQL> select ts#, name from sys.ts$ ;        TS# NAME ---------- --------------------          0 SYSTEM          1 UNDOTBS1          2 SYSAUX          3 TEMP          4 USERS          5 UNDOTBS2          6 EXAMPLE          7 TSS          8 TEMP2

2. Execute cleanup

SQL> alter session set events 'immediate trace name DROP_SEGMENTS level 4' ; Session altered.

Temp tablespace TS # is 3 *, So TS # + 1 = 4

SELECT UPPER(F.TABLESPACE_NAME) "tablesacpe name", D.TOT_GROOTTE_MB "tablespace size(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "have used(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "using rate", F.TOTAL_BYTES "free space(M)", F.MAX_BYTES "max block(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC