DBA, Developer, Author, Trainer

خب همونطور که می‌دونید حذف کردن tablespace undo به این سادگی‌ها نیست. یه بار که میخواستم به دلایلی اینکارو انجام بدم فهمیدم که اصلاً حذف tablespace undo راحت نیست و همش با خطای زیر مواجه میشدم:

SQL> select tablespace_name,file_name from dba_data_files;
 
 TABLESPACE_NAME                FILE_NAME
 ------------------------------ ---------------------------------------------------------------------
 USERS                          D:\ORACLE\ORADATA\NOIDA\USERS01.DBF
 UNDOTBS1                       D:\ORACLE\ORADATA\NOIDA\UNDOTBS01.DBF
 SYSAUX                         D:\ORACLE\ORADATA\NOIDA\SYSAUX01.DBF
 SYSTEM                         D:\ORACLE\ORADATA\NOIDA\SYSTEM01.DBF
 EXAMPLE                        D:\ORACLE\ORADATA\NOIDA\EXAMPLE01.DBF

SQL> drop tablespace undotbs1;
 drop tablespace undotbs1
 *
 ERROR at line 1:
 ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

این خطا نشون میده که tablespace undo در حال استفاده است برای حل این مورد اومدم tablespace ام رو offline کنم و با این خطا مواجه شدم:

SQL> alter tablespace undotbs1  offline;
 alter tablespace undotbs1  offline
 *
 ERROR at line 1:
 ORA-30042: Cannot offline the undo tablespace.

ار این رو برای حذف tablespace undo باید قدم‌های زیر رو طی کنیم:

1. ساخت یک tablespace undo جدید
2. دیفالت کردن tablespace جدید و تنظیم undo management به حالت manual با ویرایش فایل پارمیتر
3. چک کردن تمام سگمنت‌های tablespace undo قدیمی برای آفلاین بودن
4. حذف tablespace قدیمی
5. تغییر undo management به حالت auto با ویرایش فایل پارمیتر

مرحله اول

ساخت tablespace

SQL> create undo tablespace UNDOTBS2 datafile  'D:\ORACLE\ORADATA\NOIDA\UNDOTBS02.DBF'  size 100M;
 Tablespace created.

مرحله دوم

ویرایش فایل پارمیتر

SQL> alter system set undo_tablespace=UNDOTBS2 ;
 System altered.
 
 SQL> alter system set undo_management=MANUAL scope=spfile;
 System altered.
 
 SQL> shut immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 
 SQL> startup
 ORACLE instance started.
 Total System Global Area  426852352 bytes
 Fixed Size                  1333648 bytes
 Variable Size             360711792 bytes
 Database Buffers           58720256 bytes
 Redo Buffers                6086656 bytes
 Database mounted.
 Database opened.
 SQL> show parameter undo_tablespace
 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 undo_tablespace                      string      UNDOTBS2

مرحله سوم

چک کردن تمام سگمنت‌های tablespace undo قدیمی برای آفلاین بودن

SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;
 
 OWNER     SEGMENT_NAME                   TABLESPACE_NAME                STATUS
 ------ ------------------------------ ------------------------------ ----------------
 SYS       SYSTEM                         SYSTEM                         ONLINE
 PUBLIC    _SYSSMU10_1192467665$          UNDOTBS1                       OFFLINE
 PUBLIC    _SYSSMU1_1192467665$           UNDOTBS1                       OFFLINE
 PUBLIC    _SYSSMU2_1192467665$           UNDOTBS1                       OFFLINE
 PUBLIC    _SYSSMU3_1192467665$           UNDOTBS1                       OFFLINE
 PUBLIC    _SYSSMU4_1192467665$           UNDOTBS1                       OFFLINE
 PUBLIC    _SYSSMU5_1192467665$           UNDOTBS1                       OFFLINE
 PUBLIC    _SYSSMU6_1192467665$           UNDOTBS1                       OFFLINE
 PUBLIC    _SYSSMU7_1192467665$           UNDOTBS1                       OFFLINE
 PUBLIC    _SYSSMU8_1192467665$           UNDOTBS1                       OFFLINE
 PUBLIC    _SYSSMU9_1192467665$           UNDOTBS1                       ONLINE
 PUBLIC    _SYSSMU12_1304934663$          UNDOTBS2                       OFFLINE
 PUBLIC    _SYSSMU13_1304934663$          UNDOTBS2                       OFFLINE
 PUBLIC    _SYSSMU14_1304934663$          UNDOTBS2                       OFFLINE
 PUBLIC    _SYSSMU15_1304934663$          UNDOTBS2                       OFFLINE
 PUBLIC    _SYSSMU11_1304934663$          UNDOTBS2                       OFFLINE
 PUBLIC    _SYSSMU17_1304934663$          UNDOTBS2                       OFFLINE
 PUBLIC    _SYSSMU18_1304934663$          UNDOTBS2                       OFFLINE
 PUBLIC    _SYSSMU19_1304934663$          UNDOTBS2                       OFFLINE
 PUBLIC    _SYSSMU20_1304934663$          UNDOTBS2                       OFFLINE
 PUBLIC    _SYSSMU16_1304934663$          UNDOTBS2                       OFFLINE
 
 21 rows selected.
 

خب حالا باید هرکدوم از سگمنت‌های بالا که ONLINE هستن رو OFFLINE کنیم:

SQL>alter rollback segment "_SYSSMU9_1192467665$" offline;

مرحله چهارم

حذف tablespace قدیمی:

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

مرحله پنجم

تغییر undo management به حالت auto با ویرایش فایل پارمیتر

SQL> alter system set undo_management=auto scope=spfile;
 System altered.
 
 SQL> shut immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup
 ORACLE instance started.
 Total System Global Area  426852352 bytes
 Fixed Size                  1333648 bytes
 Variable Size             364906096 bytes
 Database Buffers           54525952 bytes
 Redo Buffers                6086656 bytes
 Database mounted.
 Database opened.
 
 SQL> show parameter undo_tablespace
 NAME                                       TYPE        VALUE
 ------------------------------------   ----------- ------------------------------
 undo_tablespace                            string      UNDOTBS2

منبع