[2021-07-17]删除UNDO表空间提示ORA-01561

1、删除表空间报错

SQL> drop tablespace UNDOTBS1  including contents and datafiles;
drop tablespace UNDOTBS1  including contents and datafiles
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified

2、查询表空间对应的段类型

SQL> select ts# from ts$ where name ='UNDOTBS1';

       TS#
----------
         2

SQL> select count(*) from seg$ where ts#=2;

  COUNT(*)
----------
        12

SQL> 
SQL> select file#,type# from  seg$ where ts#=2;

     FILE#      TYPE#
---------- ----------
         3         10
         3          3
         3          3
         3          3
         3          3
         3         10
         3          3
         3          3
         3          3
         3          3
         3          3
         3          3

12 rows selected.

SQL> 

可以看到，UNDOTBS1表包含的段类型有3、10两种，3、10分别代表TEMPORARY，TYPE2 UNDO。

SQL> set lin 2000
SQL> set long 20000
SQL> set pagesize 1000
SQL> select dbms_metadata.get_ddl('VIEW','SYS_DBA_SEGS','SYS') from dual;

DBMS_METADATA.GET_DDL('VIEW','SYS_DBA_SEGS','SYS')
--------------------------------------------------------------------------------


SQL> set wrap on
SQL> /

DBMS_METADATA.GET_DDL('VIEW','SYS_DBA_SEGS','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."SYS_DBA_SEGS" ("OWNER
", "SEGMENT_NAME", "PARTITION_NAME", "SE
GMENT_TYPE", "SEGMENT_TYPE_ID", "SEGMENT
_SUBTYPE", "TABLESPACE_ID", "TABLESPACE_
NAME", "BLOCKSIZE", "HEADER_FILE", "HEAD
ER_BLOCK", "BYTES", "BLOCKS", "EXTENTS",
 "INITIAL_EXTENT", "NEXT_EXTENT", "MIN_E
XTENTS", "MAX_EXTENTS", "MAX_SIZE", "RET
ENTION", "MINRETENTION", "PCT_INCREASE",
 "FREELISTS", "FREELIST_GROUPS", "RELATI
VE_FNO", "BUFFER_POOL_ID", "FLASH_CACHE"
, "CELL_FLASH_CACHE", "SEGMENT_FLAGS", "
SEGMENT_OBJD") AS
  select NVL(u.name, 'SYS'),
       o.name,
       o.su bname,
       so.object_type,
       s.type#,
       decode(bitand(s.spare1, 2097408),
               2097152,
               'SECUREFILE',
               256,
               'ASSM',
               'MSS
M'),
       ts.ts#,
       ts.name,
       ts.blocksize,
       f.file#,
       s.block#,
       NVL(s.blocks, 0) * ts.blocksize,
       NVL(s.block s, 0),
       s.extents,
       s.iniexts * ts.blocksize,
       s.extsize * ts.blocksize,
       s.minexts,
       s.maxexts,
       decode(bitand(s.spare1, 4194304), 419430 4, bitmapranges, NULL),
       to_char(decode(bitand(s.spare1, 2097152),
                       2097152,
                       decode(s.lists,
                               0,
                               'NONE',
                               1,
                               'AUTO',
                               2,
                               'MI
N',
                               3,
                               'MAX',
                               4,
                               'DEFAULT',
                               'INVALID'),
                       NULL)),
       decode(bitand(s.spare1, 2097152), 2097152, s.groups, NULL),
       decode(bitand(ts.flags, 3), 1, to_num ber(NULL), s.extpct),
       decode(bitand(ts.flags, 32),
              32,
              to_number(NULL),
              decode(s.lists, 0, 1, s.lists)),
       decode(bitand(ts.flags, 32),
              32,
              to_number(NULL),
              
              decode(s.groups, 0, 1, s.groups)),
       s.file#,
       bitand(s.cachehint, 3),
       bitand(s.c achehint, 12) / 4,
       bitand(s.cachehint, 48) / 16,
       NVL(s.sp are1, 0),
       decode(bitand(s.spare1, 1), 1, s.hwmincr, o.dataobj#)
  from sys.user$ u,
       sys.obj$  o,
       sys.ts$   ts,
       sys.sys   _objects so,
       sys.seg$  s,
       sys.file$ f
 where s.file# = so.header_file
   and s.block# = so.header_block
   and s.ts# = so.ts_number
   and s.ts# = ts.ts#
   and o.obj# = so.object_id
   and o.owner# = u.user#(+)
   and s.type# = so.segment_type_id
   and o.type# = so.object_type_id
   and s.ts# = f.ts#
   and s.file# = f.relfile#
union all
select NVL(u.name, 'SYS'),
       un.name,
       NULL,
       decode(s.type#, 1, 'ROLLBACK', 10, 'TYPE2 UNDO'),
       s.type#,
       NULL,
       ts.ts#,
       ts.name,
       ts.blocksize,
       f.file#,
       s. block#,
       NVL(s.blocks, 0) * ts.blocksize,
       NVL(s.blocks, 0),
       s.extents,
       s.iniexts * ts.blocksize,
       s.extsize * ts.blocksize,
       s.minexts,
       s.maxexts,
       decode(bitand(s.spare1, 4194304), 4194304, bitma pranges, NULL),
       NULL,
       NULL,
       s.extpct,
       decode(bitand(ts.flags, 32),
              32,
              to_number(NULL),
              decode(s.lists, 0, 1, s.lists)),
       decode(bitand(ts.flags, 32),
              32,
              to_number(N ULL),
              decode(s.groups, 0, 1, s.groups)),
       s.file#,
       bitand(s.cachehint, 3),
       bitan d(s.cachehint, 12) / 4,
       bitand(s.cachehint, 48) / 16,
       NVL(s.spare1, 0),
       un.us#

  from sys.user$ u, sys.ts$ ts, sys.undo$ un, sys.seg$ s, sys.file$ f
 where s.file# = un.file#
   and s.block# = un.block#
   and s.ts# = un.ts#
   and s.ts# = ts.ts#
   and s.user# = u.user#(+)
   and s.type# in (1, 10)
   and un.status$ != 1
   and un.ts# = f.ts#
   and un.file# = f.relfile#
union all
select NVL(u.name, 'SYS'),
       to_char(f.file#) || '.' || to_char(s.block#),
       NULL,
       decode(s.type#,
               2,
               'DEFERRED ROLLBACK
',
               3,
               'TEMPORARY',
               4,
               'CACHE',
               9,
               'SPA
CE HEADER',
               'UNDEFINED'),
       s.type#,
       NULL,
       ts.ts#,
       ts.name,
       ts.blocksize,
       f.file#,
       s.block#,
       NVL(s.blocks, 0) * ts.blocksize,
       NVL(s.blocks, 0),
       s.extents,
       s.iniexts * ts.blocksize,
       s.extsize * ts.blocksize,
       s.minexts,
       s.maxexts,
       decode(bitand(s.spare1, 4194304), 4194304, b itmapranges, NULL),
       NULL,
       NULL,
       decode(bitand(ts.flag s, 3), 1, to_number(NULL), s.extpct),
       decode(bitand(ts.flags, 32),
              32,
              to_n umber(NULL),
              decode(s.lists, 0, 1, s.lists)),
       decode(bitand(ts.flags, 32),
              32,
              to_number(NULL),
              decode(s.groups, 0, 1, s.grou ps)),
       s.file#,
       bitand(s.cachehint, 3),
       bitand(s.cache hint, 12) / 4,
       bitand(s.cachehint, 48) / 16,
       NVL(s.spare1, 0),
       s.hwmincr
  from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.file$ f
 where s.ts# = ts.ts#
   and s.user# = u.user#(+)
   and s.type# not in (1, 5, 6, 8, 10)
   and s.ts# = f.ts#
   and s.file# = f.relfile#

3、把类型为10的修改为3

SQL> update seg$ set type# = 3 where ts#=2;

12 rows updated.

SQL> commit;

Commit complete.

SQL>  drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

SQL>