[Oracle] [ORA-12916]: cannot shrink permanent or dictionary managed tablespace; Oracle Tablespace Shrink Command Failed - undo tablespace

Analyse rapide de l'erreur :

SQL> select tablespace_name, contents, EXTENT_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN
------------------------------ --------- ----------
SYSTEM                         PERMANENT LOCAL
SYSAUX                         PERMANENT LOCAL
UNDOTBS1                       UNDO      LOCAL
TEMP                           TEMPORARY LOCAL
USERS                          PERMANENT LOCAL
UNDOTBS2                       UNDO      LOCAL
DATA01                         PERMANENT LOCAL


Correction rapide :

ALTER DATABASE TEMPFILE '/your/path/goes/here/TEMP01.DBF' RESIZE 10G;

ALTER DATABASE DATAFILE '/your/path/goes/here/USERS01.DBF' RESIZE 10G;

Correction à terme :

Operations qui peuvent agrandir le tablespace temporaire :

  CREATE INDEX
  SELECT ... ORDER BY
  SELECT ... DISTINCT
  SELECT ... GROUP BY
  SELECT ... UNION
  SELECT ... INTERSECT
  SELECT ... MINUS
  jointures non indexées
  certaines sous requetes

 Vérifier ce qui augmente le temp :

  SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
         operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
         trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
         NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
  FROM V$SQL_WORKAREA_ACTIVE
  ORDER BY 1,2;

Autres solutions :

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/filesystem01/undotbs_01.dbf' SIZE 300M AUTOEXTEND OFF;
Tablespace created.

Step 2 : Edit the parameter file

SQL> alter system set undo_tablespace=UNDOTBS2 ;

SQL> create temporary tablespace temp2 tempfile  'D:\ORACLE\ORADATA\NOIDA\temp02.DBF'  size 100M;
Tablespace created.

-------


ALTER TABLESPACE TEMP ADD TEMPFILE '[chemin du fichier]\TEMP02.dbf' SIZE 1000M REUSE AUTOEXTEND ON;
ALTER DATABASE TEMPFILE '[chemin du fichier]\TEMP01.dbf' DROP INCLUDING DATAFILES;



-------

Désactivation/activation de undo

désactivation : alter system set undo_management = manual scope=spfile;

activation : undo_management=auto scope=spfile;


Analyse de l'erreur

SELECT /*+ RULE */ s.SID "SID",s.username "User",s.program "Program", u.TABLESPACE "Tablespace",
u.CONTENTS "Contents", u.EXTENTS "Extents", a.OBJECT, u.blocks*8/1024 "USED_SPACE_MB", q.sql_text "SQL TEXT",
k.bytes/1024/1024 "Temp File Size"
FROM v$session s, v$sort_usage u, dba_temp_files k, v$sql q, v$access a
WHERE s.saddr = u.session_addr
AND s.sql_address = q.address
AND s.SID = a.SID
--AND s.SID = 438
AND u.TABLESPACE=k.tablespace_name;