? 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
# UNDO MANAGEMENT
undo_tablespace=PSAPUNDO
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.