• ? Créer le répertoire du ou des datafiles de PSAPUNDO

    En tant que oraSID : mkdir undo_1
  • ? Se connecter en tant que SYSDBA

sqlplus "/as sysdba"
SQL> show parameter undo;
NAME                  TYPE        VALUE
undo_management       string      MANUAL
undo_retention        integer     900
undo_suppress_errors  boolean     FALSE
undo_tablespace       string
SQL> create undo tablespace PSAPUNDO datafile '/oracle/SID/sapdata1/undo_1/undo.data1'  2  size 4000M reuse autoextend off;

Tablespace created.

  • ? Mettre à jour le iniSID.ora ou le spfile 
-vi initSID.ora

# UNDO MANAGEMENT
undo_tablespace=PSAPUNDO

undo_retention=4200        12 heures de rétention

undo_management=AUTO       activation AUM 

-spfile

SQL>alter system set undo_tablespace = PSAPUNDO scope = spfile;

SQL>alter system set undo_retention = 43200 scope = spfile;

SQL>alter system set undo_management=auto scope = spfile;

SQL>shutdown immediate

SQL>startup

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

ORACLE instance started.
Total System Global Area 1492336896 bytes
Fixed Size                   725248 bytes
Variable Size             671088640 bytes
Database Buffers          819200000 bytes
Redo Buffers                1323008 bytes
Database mounted.
Database opened.

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     4200
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      PSAPUNDO

SQL> select segment_name, tablespace_name, status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
PRS_1                          PSAPROLL                       OFFLINE
PRS_2                          PSAPROLL                       OFFLINE
PRS_3                          PSAPROLL                       OFFLINE
PRS_4                          PSAPROLL                       OFFLINE
PRS_5                          PSAPROLL                       OFFLINE
PRS_6                          PSAPROLL                       OFFLINE
PRS_7                          PSAPROLL                       OFFLINE
PRS_8                          PSAPROLL                       OFFLINE
PRS_9                          PSAPROLL                       OFFLINE
PRS_10                         PSAPROLL                       OFFLINE

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU12$                     PSAPUNDO                       ONLINE
_SYSSMU13$                     PSAPUNDO                       ONLINE
_SYSSMU14$                     PSAPUNDO                       ONLINE
_SYSSMU15$                     PSAPUNDO                       ONLINE
_SYSSMU16$                     PSAPUNDO                       ONLINE
_SYSSMU17$                     PSAPUNDO                       ONLINE
_SYSSMU18$                     PSAPUNDO                       ONLINE
_SYSSMU19$                     PSAPUNDO                       ONLINE
_SYSSMU20$                     PSAPUNDO                       ONLINE
_SYSSMU21$                     PSAPUNDO                       ONLINE

21 rows selected.

SQL> show parameter rollback_segments;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_rollback_segments            integer     400
rollback_segments                    string       PRS_1, PRS_2, PRS_3, PRS_4,
                                                              PRS_5, PRS_6, PRS_7, PRS_8, PRS_9, PRS_10

SQL> select segment_name, tablespace_name, status from dba_rollback_segs where status ='OFFLINE' and segment_name not like '_SYSSMU%$';

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
PRS_1                          PSAPROLL                       OFFLINE
PRS_2                          PSAPROLL                       OFFLINE
PRS_3                          PSAPROLL                       OFFLINE
PRS_4                          PSAPROLL                       OFFLINE
PRS_5                          PSAPROLL                       OFFLINE
PRS_6                          PSAPROLL                       OFFLINE
PRS_7                          PSAPROLL                       OFFLINE
PRS_8                          PSAPROLL                       OFFLINE
PRS_9                          PSAPROLL                       OFFLINE
PRS_10                         PSAPROLL                       OFFLINE

10 rows selected.

  • Dropper les rollback segments

SQL> drop rollback segment PRS_1;
Rollback segment dropped.

SQL> drop rollback segment PRS_10;
Rollback segment dropped.

SQL> select segment_name, tablespace_name, status from dba_rollback_segs where status ='OFFLINE' and segment_name not like '_SYSSMU%$';
no rows selected

SQL> select segment_name, tablespace_name, status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU12$                     PSAPUNDO                       ONLINE
_SYSSMU13$                     PSAPUNDO                       ONLINE
_SYSSMU14$                     PSAPUNDO                       ONLINE
_SYSSMU15$                     PSAPUNDO                       ONLINE
_SYSSMU16$                     PSAPUNDO                       ONLINE
_SYSSMU17$                     PSAPUNDO                       ONLINE
_SYSSMU18$                     PSAPUNDO                       ONLINE
_SYSSMU19$                     PSAPUNDO                       ONLINE
_SYSSMU20$                     PSAPUNDO                       ONLINE
_SYSSMU21$                     PSAPUNDO                       ONLINE

11 rows selected.

-initSID.ora
Supprimer les paramètres relatifs aux rollbacks segments du initSID.ora :

max_rollback_segments = 400
transactions_per_rollback_segment = 20
rollback_segments = (PRS_1,PRS_2,PRS_3,PRS_4,PRS_5,PRS_6,PRS_7,PRS_8,PRS_9,PRS_10)

-spfile

SQL>alter system reset rollback_segments scope = spfile sid = '*';

SQL>alter system reset transactions scope = spfile sid = '*';

SQL>alter system reset transactions_per_rollback_segment scope = spfile sid = '*';

SQL>alter system reset max_rollback_segments scope = spfile sid='*';

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1492336896 bytes
Fixed Size                   725248 bytes
Variable Size             671088640 bytes
Database Buffers          819200000 bytes
Redo Buffers                1323008 bytes
Database mounted.
Database opened.

SQL> drop tablespace PSAPROLL including contents and datafiles;
Tablespace dropped.

SQL> select segment_name, tablespace_name, status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU12$                     PSAPUNDO                       ONLINE
_SYSSMU13$                     PSAPUNDO                       ONLINE
_SYSSMU14$                     PSAPUNDO                       ONLINE
_SYSSMU15$                     PSAPUNDO                       ONLINE
_SYSSMU16$                     PSAPUNDO                       ONLINE
_SYSSMU17$                     PSAPUNDO                       ONLINE
_SYSSMU18$                     PSAPUNDO                       ONLINE
_SYSSMU19$                     PSAPUNDO                       ONLINE
_SYSSMU20$                     PSAPUNDO                       ONLINE
_SYSSMU21$                     PSAPUNDO                       ONLINE


  • Effacer les arborescences des datafiles (rm –r).
  • Rajouter des datafiles au tablespace PSAPUNDO
  • Effectuer une sauvegarde.