Dev SIG

The power of DEV through GIS [Forum SIG / Next Gen]


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

    Partagez
    avatar
    Jeffjefferson
    Admin

    Messages : 35
    Date d'inscription : 18/07/2015

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

    Message par Jeffjefferson le Dim 7 Mai - 17:13

    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  'D:\ORACLE\ORADATA\NOIDA\UNDOTBS02.DBF'  size 100M;
    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;

      La date/heure actuelle est Mer 23 Aoû - 10:12