خب همونطور که می‌دونید حذف کردن 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

منبع