Introduction

Removing a tablespace shouldn’t be a problem as soon as no segments remain in it. So why sometimes Oracle prevents you from removing a tablespace?

My database

The database I’m working on is a 19c database for SAP. A previously used tablespace has been put offline several months ago, and it’s now time to remove it from the database. Easy task.

Prerequisites before removal

Let’s first identify the tablespace:

. oraenv <<< DOT
sqlplus / as sysdba

select tablespace_name, status from dba_tablespaces where status = 'OFFLINE';
TABLESPACE_NAME 	       STATUS
------------------------------ ---------
PSAPSR3740		       OFFLINE

Now, let’s check if there are segments in this tablespace:

col owner for a20
col segment_type for a30
col segment_name for a50
set lines 200
set pages 200
select owner, segment_type, segment_name from dba_segments where tablespace_name='PSAPSR3740'; 
OWNER		     SEGMENT_TYPE		    SEGMENT_NAME
-------------------- ------------------------------ --------------------------------------------------
SAPSR3		     TEMPORARY			    7.130
SAPSR3		     TEMPORARY			    7.138
SAPSR3		     TEMPORARY			    7.146
SAPSR3		     TEMPORARY			    7.154
SAPSR3		     TEMPORARY			    7.162
SAPSR3		     TEMPORARY			    7.170
SAPSR3		     TEMPORARY			    7.178
SAPSR3		     TEMPORARY			    7.186
SAPSR3		     TEMPORARY			    7.194
SAPSR3		     TEMPORARY			    7.210
SAPSR3		     TEMPORARY			    7.218
SAPSR3		     TEMPORARY			    7.226
SAPSR3		     TEMPORARY			    7.234
SAPSR3		     TEMPORARY			    7.242
SAPSR3		     TEMPORARY			    7.250
SAPSR3		     TEMPORARY			    7.1027610
…
SAPSR3		     TEMPORARY			    12.1317458
SAPSR3		     TEMPORARY			    12.1338026
SAPSR3		     TEMPORARY			    12.1338082
SAPSR3		     TEMPORARY			    12.1338090
SAPSR3		     TEMPORARY			    12.1338098
SAPSR3		     TEMPORARY			    12.1382362
SAPSR3		     TEMPORARY			    12.1384850
SAPSR3		     TEMPORARY			    12.1384898
SAPSR3		     TEMPORARY			    12.1384922
SAPSR3		     TEMPORARY			    12.1384930
SAPSR3		     TEMPORARY			    12.1384938
SAPSR3		     TEMPORARY			    12.1386154

Temporary segments are located in this tablespace. Let’s put back this tablespace online, and then remove these temporary segments with COALESCE.

ALTER TABLESPACE PSAPSR3740 online;
Tablespace altered.

ALTER TABLESPACE PSAPSR3740 coalesce;
Tablespace altered.

select owner, segment_type, segment_name from dba_segments where tablespace_name='PSAPSR3740';
no rows selected

This tablespace could also be the default’s one for some users, let’s check that:

select username from dba_users where default_tablespace='PSAPSR3740';
no rows selected

This tablespace could normally be removed now.

Error when removing the tablespace

Let’s now try to drop this tablespace:

DROP TABLESPACE PSAPSR3740 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE PSAPSR3740 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-22868: table with LOBs contains segments in different tablespaces

There is still an object linked to this tablespace, let’s try to identify it:

select * from v$tablespace where name='PSAPSR3740';
       TS# NAME 		     INC BIG FLA ENC	 CON_ID
---------- ------------------------- --- --- --- --- ----------
	 9 PSAPSR3740		     YES NO  YES	      0

select t.obj# from tab$ t,ind$ i where i.type#=8 and i.ts#=9 and i.bo#=t.obj# and bitand(t.property, 32+64)=0 and t.ts#<>9 union all select null from tab$ t,ind$ iot,ind$ i where i.type#=8 and i.ts#=9 and i.bo#=t.obj# and bitand(t.property, 32)=0 and bitand(t.property, 64)<>0 and t.pctused$=iot.obj# and iot.ts#<>9 ;
      OBJ#
----------
    173459

col object_name for a30
select owner, object_name, object_type from dba_objects where object_id=173459;
OWNER		     OBJECT_NAME		    OBJECT_TYPE
-------------------- ------------------------------ -----------------------
SAPSR3		     DBMAPS			    TABLE

Let’s check if this object has a segment, meaning that it needs tablespace storage:

select tablespace_name from dba_segments where segment_name='DBMAPS';
no rows selected

select count(*) from SAPSR3.DBMAPS;
  COUNT(*)
----------
	 0

The table is empty, and as Enterprise Edition has deferred segment creation, the segment doesn’t exist at all.

Let’s have a look at the DDL of this object:

set long 200000 pages 0 lines 131
column txt format a121 word_wrapped
SELECT DBMS_METADATA.GET_DDL('TABLE','DBMAPS','SAPSR3') from dual;
  CREATE TABLE "SAPSR3"."DBMAPS"
   (	"TABNAME" VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE,
	"VARKEY" VARCHAR2(180) DEFAULT ' ' NOT NULL ENABLE,
	"DATALN" NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,
	"VARDATA" BLOB
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE( INITIAL 16384 NEXT 163840 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0)
  TABLESPACE "PSAPSR3750"
 LOB ("VARDATA") STORE AS SECUREFILE (
  TABLESPACE "PSAPSR3740" ENABLE STORAGE IN ROW CHUNK 8192
  CACHE  NOCOMPRESS  KEEP_DUPLICATES );

This table has a LOB column, and the associated storage clause for this LOB points to the tablespace I want to delete. Let’s change the table definition and use another online tablespace for the storage clause:

ALTER TABLE SAPSR3.DBMAPS MOVE TABLESPACE PSAPSR3750
LOB ("VARDATA") STORE AS SECUREFILE (
  TABLESPACE "PSAPSR3750" ENABLE STORAGE IN ROW CHUNK 8192
  CACHE  NOCOMPRESS  KEEP_DUPLICATES );
Table altered.

Now let’s try again to remove the tablespace:

DROP TABLESPACE PSAPSR3740 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

It’s done now.

Conclusion

The storage clause for a LOB was preventing the tablespace from being removed, although no storage was used.

Post Views: 22
Thumbnail [60x60]
by
Jérôme Dubar