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?<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Let\u2019s first identify the tablespace:<\/p>\n\n\n\n
. oraenv <<< DOT\nsqlplus \/ as sysdba\n\nselect tablespace_name, status from dba_tablespaces where status = 'OFFLINE';\nTABLESPACE_NAME \t STATUS\n------------------------------ ---------\nPSAPSR3740\t\t OFFLINE<\/code><\/pre>\n\n\n\nNow, let’s check if there are segments in this tablespace:<\/p>\n\n\n\n
col owner for a20\ncol segment_type for a30\ncol segment_name for a50\nset lines 200\nset pages 200\nselect owner, segment_type, segment_name from dba_segments where tablespace_name='PSAPSR3740'; \nOWNER\t\t SEGMENT_TYPE\t\t SEGMENT_NAME\n-------------------- ------------------------------ --------------------------------------------------\nSAPSR3\t\t TEMPORARY\t\t\t 7.130\nSAPSR3\t\t TEMPORARY\t\t\t 7.138\nSAPSR3\t\t TEMPORARY\t\t\t 7.146\nSAPSR3\t\t TEMPORARY\t\t\t 7.154\nSAPSR3\t\t TEMPORARY\t\t\t 7.162\nSAPSR3\t\t TEMPORARY\t\t\t 7.170\nSAPSR3\t\t TEMPORARY\t\t\t 7.178\nSAPSR3\t\t TEMPORARY\t\t\t 7.186\nSAPSR3\t\t TEMPORARY\t\t\t 7.194\nSAPSR3\t\t TEMPORARY\t\t\t 7.210\nSAPSR3\t\t TEMPORARY\t\t\t 7.218\nSAPSR3\t\t TEMPORARY\t\t\t 7.226\nSAPSR3\t\t TEMPORARY\t\t\t 7.234\nSAPSR3\t\t TEMPORARY\t\t\t 7.242\nSAPSR3\t\t TEMPORARY\t\t\t 7.250\nSAPSR3\t\t TEMPORARY\t\t\t 7.1027610\n\u2026\nSAPSR3\t\t TEMPORARY\t\t\t 12.1317458\nSAPSR3\t\t TEMPORARY\t\t\t 12.1338026\nSAPSR3\t\t TEMPORARY\t\t\t 12.1338082\nSAPSR3\t\t TEMPORARY\t\t\t 12.1338090\nSAPSR3\t\t TEMPORARY\t\t\t 12.1338098\nSAPSR3\t\t TEMPORARY\t\t\t 12.1382362\nSAPSR3\t\t TEMPORARY\t\t\t 12.1384850\nSAPSR3\t\t TEMPORARY\t\t\t 12.1384898\nSAPSR3\t\t TEMPORARY\t\t\t 12.1384922\nSAPSR3\t\t TEMPORARY\t\t\t 12.1384930\nSAPSR3\t\t TEMPORARY\t\t\t 12.1384938\nSAPSR3\t\t TEMPORARY\t\t\t 12.1386154<\/code><\/pre>\n\n\n\nTemporary segments are located in this tablespace. Let’s put back this tablespace online, and then remove these temporary segments with COALESCE.<\/p>\n\n\n\n
ALTER TABLESPACE PSAPSR3740 online;\nTablespace altered.\n\nALTER TABLESPACE PSAPSR3740 coalesce;\nTablespace altered.\n\nselect owner, segment_type, segment_name from dba_segments where tablespace_name='PSAPSR3740';\nno rows selected<\/code><\/pre>\n\n\n\nThis tablespace could also be the default’s one for some users, let’s check that:<\/p>\n\n\n\n
select username from dba_users where default_tablespace='PSAPSR3740';\nno rows selected<\/code><\/pre>\n\n\n\nThis tablespace could normally be removed now.<\/p>\n\n\n\n
Error when removing the tablespace<\/h2>\n\n\n\n
Let’s now try to drop this tablespace:<\/p>\n\n\n\n
DROP TABLESPACE PSAPSR3740 INCLUDING CONTENTS AND DATAFILES;\nDROP TABLESPACE PSAPSR3740 INCLUDING CONTENTS AND DATAFILES\n*\nERROR at line 1:\nORA-22868: table with LOBs contains segments in different tablespaces<\/code><\/pre>\n\n\n\nThere is still an object linked to this tablespace, let’s try to identify it:<\/p>\n\n\n\n
select * from v$tablespace where name='PSAPSR3740';\n TS# NAME \t\t INC BIG FLA ENC\t CON_ID\n---------- ------------------------- --- --- --- --- ----------\n\t 9 PSAPSR3740\t\t YES NO YES\t 0\n\nselect 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 ;\n OBJ#\n----------\n 173459\n\ncol object_name for a30\nselect owner, object_name, object_type from dba_objects where object_id=173459;\nOWNER\t\t OBJECT_NAME\t\t OBJECT_TYPE\n-------------------- ------------------------------ -----------------------\nSAPSR3\t\t DBMAPS\t\t\t TABLE\n<\/code><\/pre>\n\n\n\nLet’s check if this object has a segment, meaning that it needs tablespace storage:<\/p>\n\n\n\n
select tablespace_name from dba_segments where segment_name='DBMAPS';\nno rows selected\n\nselect count(*) from SAPSR3.DBMAPS;\n COUNT(*)\n----------\n\t 0<\/code><\/pre>\n\n\n\nThe table is empty, and as Enterprise Edition has deferred segment creation, the segment doesn’t exist at all.<\/p>\n\n\n\n
Let’s have a look at the DDL of this object:<\/p>\n\n\n\n
set long 200000 pages 0 lines 131\ncolumn txt format a121 word_wrapped\nSELECT DBMS_METADATA.GET_DDL('TABLE','DBMAPS','SAPSR3') from dual;\n CREATE TABLE \"SAPSR3\".\"DBMAPS\"\n (\t\"TABNAME\" VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE,\n\t\"VARKEY\" VARCHAR2(180) DEFAULT ' ' NOT NULL ENABLE,\n\t\"DATALN\" NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,\n\t\"VARDATA\" BLOB\n ) SEGMENT CREATION DEFERRED\n PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255\n NOCOMPRESS LOGGING\n STORAGE( INITIAL 16384 NEXT 163840 MINEXTENTS 1 MAXEXTENTS 2147483645\n PCTINCREASE 0)\n TABLESPACE \"PSAPSR3750\"\n LOB (\"VARDATA\") STORE AS SECUREFILE (\n TABLESPACE \"PSAPSR3740\" ENABLE STORAGE IN ROW CHUNK 8192\n CACHE NOCOMPRESS KEEP_DUPLICATES );<\/code><\/pre>\n\n\n\nThis 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:<\/p>\n\n\n\n
ALTER TABLE SAPSR3.DBMAPS MOVE TABLESPACE PSAPSR3750\nLOB (\"VARDATA\") STORE AS SECUREFILE (\n TABLESPACE \"PSAPSR3750\" ENABLE STORAGE IN ROW CHUNK 8192\n CACHE NOCOMPRESS KEEP_DUPLICATES );\nTable altered.<\/code><\/pre>\n\n\n\nNow let’s try again to remove the tablespace:<\/p>\n\n\n\n
DROP TABLESPACE PSAPSR3740 INCLUDING CONTENTS AND DATAFILES;\nTablespace dropped.\n<\/code><\/pre>\n\n\n\nIt’s done now.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
The storage clause for a LOB was preventing the tablespace from being removed, although no storage was used.<\/p>\n","protected":false},"excerpt":{"rendered":"
ORA-22868: cannot drop a tablespace<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,59],"tags":[2824,2623,656,61,62,3670,96],"type_dbi":[],"class_list":["post-40368","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-11-2","tag-12-1","tag-12-2","tag-18c","tag-19c","tag-ora-22868","tag-oracle"],"acf":[],"yoast_head":"\n
Cannot drop a tablespace? - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Cannot drop a tablespace?\" \/>\n<meta property=\"og:description\" content=\"ORA-22868: cannot drop a tablespace\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2025-09-26T13:43:42+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-09-26T13:43:44+00:00\" \/>\n<meta name=\"author\" content=\"J\u00e9r\u00f4me Dubar\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"J\u00e9r\u00f4me Dubar\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/\"},\"author\":{\"name\":\"J\u00e9r\u00f4me Dubar\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0fb4bbf128b4cda2f96d662dec2baedd\"},\"headline\":\"Cannot drop a tablespace?\",\"datePublished\":\"2025-09-26T13:43:42+00:00\",\"dateModified\":\"2025-09-26T13:43:44+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/\"},\"wordCount\":276,\"commentCount\":0,\"keywords\":[\"11.2\",\"12.1\",\"12.2\",\"18c\",\"19c\",\"ORA-22868\",\"Oracle\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/\",\"name\":\"Cannot drop a tablespace? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2025-09-26T13:43:42+00:00\",\"dateModified\":\"2025-09-26T13:43:44+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0fb4bbf128b4cda2f96d662dec2baedd\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Cannot drop a tablespace?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0fb4bbf128b4cda2f96d662dec2baedd\",\"name\":\"J\u00e9r\u00f4me Dubar\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/efaa5a7def0aa4cdaf49a470fb4a7641a3ea6e378ae1455096a0933f99f46d6b?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/efaa5a7def0aa4cdaf49a470fb4a7641a3ea6e378ae1455096a0933f99f46d6b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/efaa5a7def0aa4cdaf49a470fb4a7641a3ea6e378ae1455096a0933f99f46d6b?s=96&d=mm&r=g\",\"caption\":\"J\u00e9r\u00f4me Dubar\"},\"description\":\"J\u00e9r\u00f4me Dubar has more than 15 years of experience in the field of Information Technology. Ten years ago, he specialized in the Oracle Database technology. His expertise is focused on database architectures, high availability (RAC), disaster recovery (DataGuard), backups (RMAN), performance analysis and tuning (AWR\/statspack), migration, consolidation and appliances, especially ODA (his main projects during the last years). Prior to joining dbi services, J\u00e9r\u00f4me Dubar worked in a Franco-Belgian IT service company as Database team manager and main consultant for 7 years. He also worked for 5 years in a software editor company as technical consultant across France. He was also teaching Oracle Database lessons for 9 years. J\u00e9r\u00f4me Dubar holds a Computer Engineering degree from the Lille Sciences and Technologies university in northern France. His branch-related experience covers the public sector, retail, industry, banking, health, e-commerce and IT sectors.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/jerome-dubar\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Cannot drop a tablespace? - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/","og_locale":"en_US","og_type":"article","og_title":"Cannot drop a tablespace?","og_description":"ORA-22868: cannot drop a tablespace","og_url":"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/","og_site_name":"dbi Blog","article_published_time":"2025-09-26T13:43:42+00:00","article_modified_time":"2025-09-26T13:43:44+00:00","author":"J\u00e9r\u00f4me Dubar","twitter_card":"summary_large_image","twitter_misc":{"Written by":"J\u00e9r\u00f4me Dubar","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/"},"author":{"name":"J\u00e9r\u00f4me Dubar","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0fb4bbf128b4cda2f96d662dec2baedd"},"headline":"Cannot drop a tablespace?","datePublished":"2025-09-26T13:43:42+00:00","dateModified":"2025-09-26T13:43:44+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/"},"wordCount":276,"commentCount":0,"keywords":["11.2","12.1","12.2","18c","19c","ORA-22868","Oracle"],"articleSection":["Database Administration & Monitoring","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/","url":"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/","name":"Cannot drop a tablespace? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2025-09-26T13:43:42+00:00","dateModified":"2025-09-26T13:43:44+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0fb4bbf128b4cda2f96d662dec2baedd"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/cannot-drop-a-tablespace\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Cannot drop a tablespace?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0fb4bbf128b4cda2f96d662dec2baedd","name":"J\u00e9r\u00f4me Dubar","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/efaa5a7def0aa4cdaf49a470fb4a7641a3ea6e378ae1455096a0933f99f46d6b?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/efaa5a7def0aa4cdaf49a470fb4a7641a3ea6e378ae1455096a0933f99f46d6b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/efaa5a7def0aa4cdaf49a470fb4a7641a3ea6e378ae1455096a0933f99f46d6b?s=96&d=mm&r=g","caption":"J\u00e9r\u00f4me Dubar"},"description":"J\u00e9r\u00f4me Dubar has more than 15 years of experience in the field of Information Technology. Ten years ago, he specialized in the Oracle Database technology. His expertise is focused on database architectures, high availability (RAC), disaster recovery (DataGuard), backups (RMAN), performance analysis and tuning (AWR\/statspack), migration, consolidation and appliances, especially ODA (his main projects during the last years). Prior to joining dbi services, J\u00e9r\u00f4me Dubar worked in a Franco-Belgian IT service company as Database team manager and main consultant for 7 years. He also worked for 5 years in a software editor company as technical consultant across France. He was also teaching Oracle Database lessons for 9 years. J\u00e9r\u00f4me Dubar holds a Computer Engineering degree from the Lille Sciences and Technologies university in northern France. His branch-related experience covers the public sector, retail, industry, banking, health, e-commerce and IT sectors.","url":"https:\/\/www.dbi-services.com\/blog\/author\/jerome-dubar\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/40368","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=40368"}],"version-history":[{"count":6,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/40368\/revisions"}],"predecessor-version":[{"id":40374,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/40368\/revisions\/40374"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=40368"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=40368"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=40368"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=40368"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}