This is another little “case study” on solving a problem. It’s based on a question on the MOSC DBA Forum (needs an account) about trying to drop a tablespace and seeing the error “ORA-29857: domain indexes and/or secondary objects exist in the tablespace” when (apparently) there are no domain indexes or secondary objects in the tablespace according to the queries that have been used to try and locate the problem objects.
Not being completely familiar with all the possibilities that might be relevant to that particular error, but having a vague memory of seeing an oddity once before, I thought I’d do a quick check on a couple of possible causes.
- Case 1, which I assumed ought to raise the error because the tablespace held some objects that met the requirements for the error: I created a table in a tablespace called users and, for the domain indexes a couple of Context (Oracle Text) indexes in a tablespace called test_8k_assm. Then I tried to drop the tablespace holding the context indexes. Here’s the relevant cut-n-paste (19.11.0.0):
SQL> drop tablespace test_8k_assm including contents and datafiles;
drop tablespace test_8k_assm including contents and datafiles
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace
- Case 2, which I thought would drop the tablespace because the model didn’t meet the requirement of the error message: I swapped the locations of the table and its indexes, creating the table in the tablespace called test_8k_assm and the Context indexes in the tablespace called users then I tried to drop the tablespace holding only the table. Here’s the relevant cut-n-paste (19.11.0.0):
SQL> drop tablespace test_8k_assm including contents and datafiles;
drop tablespace test_8k_assm including contents and datafiles
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace
So, the error message is misleading and, possibly, this might explain why the queries the OP had posted weren’t reporting any guilty objects. Whatever the error message says, the domain indexes that you need to deal with may not be in the tablespace you want to drop.
I also have a question-mark over “secondary objects” – I don’t know what objects (other than the bits and pieces that make up domain indexes) are deemed to be secondary.
Investigating
It’s at this point that the purpose of the blog note starts to appear – I don’t know the answer to the OP’s question and I’ve discovered a gap in my knowledge so I’m willing to spend a little time extending my knowledge and trying to give a little help to the OP.
We can start with the view dba_secondary_objects (based on secobj$ from script dcore.bsq) which tells us that there are two possible sources of secondary objects – index types and statistics types:
SQL> select dbms_metadata.get_ddl('VIEW','DBA_SECONDARY_OBJECTS') view_text from dual;
-- with a little cosmetic tidying:
VIEW_TEXT
-------------------------------------------------------------------------------------------
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."DBA_SECONDARY_OBJECTS" ("INDEX_OWNER", "INDEX_NAME", "SECONDARY_OBJECT_OWNER", "SECONDARY_OBJECT_NAME", "SECONDARY_OBJDATA_TYPE")
AS
select u.name, o.name, u1.name, o1.name,
decode(s.spare1, 0, 'FROM INDEXTYPE',1, 'FROM STATISTICS TYPE')
from sys.user$ u, sys.obj$ o, sys.user$ u1, sys.obj$ o1, sys.secobj$ s
where s.obj# = o.obj# and o.owner# = u.user# and
s.secobj# = o1.obj# and o1.owner# = u1.user#;
With this view definition I’d be tempted to do a little drilling down, with queries that return increasing amounts of data, and then pick subsets to expand to higher precision, e.g:
select secondary_objdata_type, count(*)
from dba_secondary_objects
group by
secondary_objdata_type
/
Are there any secondary objects that aren’t domain indexes – if there are a few then query the view for just those to find out what they are and who owns them.
select *
from dba_secondary_objects
where secondary_objdata_type = 'FROM STATISTICS TYPE'
/
In fact if the total number of secondary objects was small I might report the details about all of them and try to track them down, or if there was only one schema using that tablespace I might restrict my queries to the subset of secondary objects owned by that schema:
select distinct index_owner, index_name
from dba_secondary_objects
where secondary_object_owner = 'TEST_USER'
;
The OP had a rather larger task than my little model – there were 14,499 segments in the tablespace, and a query for (segment_owner, segment_type, count(*)) returned 332 rows – so had taken a similar approach to mine to try and minimise the amount of work needed, starting with the following queries (which I’ve enhanced slightly and given a cosmetic makeover):
prompt ==================================
prompt Domain indexes (in the tablespace)
prompt ==================================
select owner, index_name, table_owner, table_name, tablespace_name
from dba_indexes
where index_type = 'DOMAIN'
-- and tablespace_name = 'TEST_8K_ASSM' -- error: domain indexes don't have tablespaces
;
prompt ==========================================
prompt Domain indexes on tables in the tablespace
prompt ==========================================
select t.table_name, t.tablespace_name, i.index_name, i.tablespace_name
from dba_indexes i, dba_tables t
where t.table_name = i.table_name
and t.owner = i.owner
and i.index_type = 'DOMAIN'
and t.tablespace_name = 'TEST_8K_ASSM'
;
prompt ===================================================
prompt Secondary objects that are tables in the tablespace
prompt ===================================================
select index_owner, index_name, secondary_object_owner, secondary_object_name, secondary_objdata_type
from dba_secondary_objects
where secondary_object_name in (
select table_name
from dba_tables
where tablespace_name = 'TEST_8K_ASSM'
)
;
prompt ======================================================
prompt Indexes in the tablespace that are secondary objects ?
prompt ======================================================
select index_owner, index_name, secondary_object_owner, secondary_object_name, secondary_objdata_type
from dba_secondary_objects
where index_name in (
select index_name
from dba_indexes
where tablespace_name = 'TEST_8K_ASSM'
)
;
The first query had an error – domain indexes do not report a tablespace_name in dba_indexes, so this query inevitably returned no rows.
The second query would report any domain indexes on tables that were in the given tablespace – which would be useful for the simple case where everything was created in the user’s default tablespace.
The third query reports any tables in the given tablespace that are the physical tables instantiating the “logical” domain index – which is particularly important for my example of the indexes being in the given tablespace when the table itself was in some other tablespace.
The final query is trying to report domain indexes that have corresponding physical objects in the given tablespace – but this isn’t going to help (at least with my Context indexes) because the index_name in dba_secondary_indexes will be a name that appears in dba_indexes as index_type = ‘DOMAIN’ and won’t have a value stored in the tablespace_name column.
Here’s the output I get (preceded by a full report of the view dba_secondary_objects in my otherwise empty little database) when I had the table in test_8k_assm and the indexes in tablespace users.
SQL> select * from dba_secondary_objects;
INDEX_OWNER INDEX_NAME SECONDARY_OBJECT_OWNER SECONDARY_OBJECT_NAME SECONDARY_OBJDATA_TY
--------------- -------------------- -------------------------------- -------------------------------- --------------------
TEST_USER SH_IX_NAME TEST_USER DR$SH_IX_NAME$I FROM INDEXTYPE
TEST_USER SH_IX_NAME TEST_USER DR$SH_IX_NAME$K FROM INDEXTYPE
TEST_USER SH_IX_NAME TEST_USER DR$SH_IX_NAME$N FROM INDEXTYPE
TEST_USER SH_IX_NAME TEST_USER DR$SH_IX_NAME$U FROM INDEXTYPE
TEST_USER SH_IX_HANDLE TEST_USER DR$SH_IX_HANDLE$I FROM INDEXTYPE
TEST_USER SH_IX_HANDLE TEST_USER DR$SH_IX_HANDLE$K FROM INDEXTYPE
TEST_USER SH_IX_HANDLE TEST_USER DR$SH_IX_HANDLE$N FROM INDEXTYPE
TEST_USER SH_IX_HANDLE TEST_USER DR$SH_IX_HANDLE$U FROM INDEXTYPE
8 rows selected.
==================================
Domain indexes (in the tablespace)
==================================
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME
--------------- -------------------- --------------- ------------------------- ------------------------------
TEST_USER SH_IX_NAME TEST_USER SCRIPT_HANDLES
TEST_USER SH_IX_HANDLE TEST_USER SCRIPT_HANDLES
2 rows selected.
==========================================
Domain indexes on tables in the tablespace
==========================================
TABLE_NAME TABLESPACE_NAME INDEX_NAME TABLESPACE_NAME
------------------------- ------------------------------ -------------------- ------------------------------
SCRIPT_HANDLES TEST_8K_ASSM SH_IX_NAME
SCRIPT_HANDLES TEST_8K_ASSM SH_IX_HANDLE
2 rows selected.
===================================================
Secondary objects that are tables in the tablespace
===================================================
no rows selected
=====================================================
Indexes in the tablespace that have secondary objects
=====================================================
no rows selected
As you can see I have two Context indexes, each represented by 4 secondary tables (and that’s not the complete story because those tables have their own indexes and a couple of them have LOB columns, and one of the secondary tables is actually an index-organized table).
And this is the result when the base table is in tablespace users and the indexes are in test_8k_assm:
==================================
Domain indexes (in the tablespace)
==================================
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME
--------------- -------------------- --------------- ------------------------- ------------------------------
TEST_USER SH_IX_NAME TEST_USER SCRIPT_HANDLES
TEST_USER SH_IX_HANDLE TEST_USER SCRIPT_HANDLES
2 rows selected
==========================================
Domain indexes on tables in the tablespace
==========================================
no rows selected
===================================================
Secondary objects that are tables in the tablespace
===================================================
INDEX_OWNER INDEX_NAME SECONDARY_OBJECT_OWNER SECONDARY_OBJECT_NAME SECONDARY_OBJDATA_TY
--------------- -------------------- -------------------------------- -------------------------------- --------------------
TEST_USER SH_IX_NAME TEST_USER DR$SH_IX_NAME$I FROM INDEXTYPE
TEST_USER SH_IX_NAME TEST_USER DR$SH_IX_NAME$K FROM INDEXTYPE
TEST_USER SH_IX_NAME TEST_USER DR$SH_IX_NAME$U FROM INDEXTYPE
TEST_USER SH_IX_HANDLE TEST_USER DR$SH_IX_HANDLE$I FROM INDEXTYPE
TEST_USER SH_IX_HANDLE TEST_USER DR$SH_IX_HANDLE$K FROM INDEXTYPE
TEST_USER SH_IX_HANDLE TEST_USER DR$SH_IX_HANDLE$U FROM INDEXTYPE
6 rows selected
=====================================================
Indexes in the tablespace that have secondary objects
=====================================================
no rows selected
So one of the middle two queries ought to return some rows if there are any Context indexes in the tablespace or on tables in the tablespace. (And for the final option – i.e. the table and its Context indexes being in the tablespace – both queries will return rows). The OP got no rows returned on all 4 queries.
What next?
It looks like the OP must have some secondary objects that aren’t playing by the rules if they’re “from index types”, or there are some secondary objects that are “from statistics types” and don’t get picked up by the queries. There is a view dba_ustat for “user stats types” which has interesting column names, but at this point I decided to follow a strategy that I’ve used many times in the past when Oracle raises an unexpected error on a repeatable process: repeat the process but enable SQL trace and see what Oracle was doing just before it raised the error.
Here’s the critical line in the trace file I produced; with a couple of the precding lines:
CLOSE #140708355472384:c=1860,e=1512,dep=1,type=0,tim=31701605233
EXEC #140708354845632:c=68018,e=68006,p=0,cr=1276,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=31701605472
ERROR #140708354845632:err=29857 tim=31701605485
A search backwards up the trace file showed that cursor #140708354845632 was for the “drop tablespace” statement. Just above the ERROR and EXEC lines we see cursor #140708355472384 closing. It’s reasonably likely (though not 100% certain) that this is the cursor representing the last SQL statement that Oracle executed before raising the error so searching backwards for “PARSING IN CURSOR #140708355472384” was my next step (and if that statement didn’t look promising I’d have search backwards from the ERROR line to find the cursor number from the most recent “EXEC” line). In this case (with my data) the CLOSE took me to the following (cosmetically cleaned) SQL:
select null from (
select o.obj# from obj$ o, tab$ t where t.ts#=:1 and t.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all select o.obj# from obj$ o, tabpart$ tp where tp.ts#=:1 and tp.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all select o.obj# from obj$ o, tabsubpart$ tsp where tsp.ts#=:1 and tsp.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all select o.obj# from obj$ o, ind$ i where i.ts#=:1 and i.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all select o.obj# from obj$ o, indpart$ ip where ip.ts#=:1 and ip.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all select o.obj# from obj$ o, indsubpart$ isp where isp.ts#=:1 and isp.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all select o.obj# from obj$ o, clu$ c where c.ts#=:1 and c.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all select i.obj# from ind$ i, tab$ t, tabpart$ tp where tp.ts#=:1 and tp.bo# = t.obj# and t.obj#=i.bo# and i.type#=9
union all select i.obj# from ind$ i, tab$ t, tabcompart$ tcp,tabsubpart$ tsp where tsp.ts#=:1 and tsp.pobj#=tcp.obj# and tcp.bo#=t.obj# and t.obj#=i.bo# and i.type#=9
union all select i.obj# from ind$ i, tab$ t where t.ts#=:1 and t.obj#=i.bo# and i.type#=9
)
Every single line of the UNION ALL is addressing the same tablespace by number (:1) looking for objects where bit 5 is set in the obj$.flags column (i.e. secondary object) or indexes where the ind$.type# is 9 (“cooperative index method”).
That looks like a good bet for being the code that Oracle uses to check if there are any objects that should block the drop, so I changed the bind variable to the tablespace number (from v$tablespace) changed the “select null” to “select obj#” and stuck the resulting query block into an IN subquery against dba_objects (this may not be the most efficient of queries):
select
secondary, owner, object_name, object_type, object_id
from dba_objects
where object_id in (
select obj# from (
select o.obj# from obj$ o, tab$ t where t.ts#=6 and t.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all ...
union all select i.obj# from ind$ i, tab$ t where t.ts#=6 and t.obj#=i.bo# and i.type#=9
)
)
order by secondary, owner, object_name
/
S OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
- --------------- -------------------------------- ----------------------- ----------
N TEST_USER SH_IX_HANDLE INDEX 123956
N TEST_USER SH_IX_NAME INDEX 123944
Y TEST_USER DR$SH_IX_HANDLE$I TABLE 123957
Y TEST_USER DR$SH_IX_HANDLE$K TABLE 123960
Y TEST_USER DR$SH_IX_HANDLE$KD INDEX 123966
Y TEST_USER DR$SH_IX_HANDLE$KR INDEX 123967
Y TEST_USER DR$SH_IX_HANDLE$U TABLE 123963
Y TEST_USER DR$SH_IX_HANDLE$X INDEX 123965
Y TEST_USER DR$SH_IX_NAME$I TABLE 123945
Y TEST_USER DR$SH_IX_NAME$K TABLE 123948
Y TEST_USER DR$SH_IX_NAME$KD INDEX 123954
Y TEST_USER DR$SH_IX_NAME$KR INDEX 123955
Y TEST_USER DR$SH_IX_NAME$U TABLE 123951
Y TEST_USER DR$SH_IX_NAME$X INDEX 123953
Y TEST_USER SYS_C0016528 INDEX 123952
Y TEST_USER SYS_C0016536 INDEX 123964
Y TEST_USER SYS_IL0000123945C00006$$ INDEX 123947
Y TEST_USER SYS_IL0000123957C00006$$ INDEX 123959
Y TEST_USER SYS_IOT_TOP_123949 INDEX 123950
Y TEST_USER SYS_IOT_TOP_123961 INDEX 123962
20 rows selected.
This is the list I got when the base table and the Context indexes were all created in the tablespace I wanted to drop – notice that the “logical” indexes (the first two items, flagged as secondary=’N’) are reported along with all the physical objects relating to the context indexes.
When I created the indexes in the target tablespace but the base table in an alternative tablespace the query reported only the physical objects making up the Context indexes; when I created the base table in the target tablespace and the Context indexes in an alternative tablespace the query reported only the “logical” indexes.
Conclusion
As a last resort, when the simple queries you find in the manuals or on MOS (or elsewhere on the internet) don’t seem to supply all the answers the strategy of finding and hacking the query that Oracle runs just before raising the error is a good generic method for getting a better handle on what’s causing the problem.
In this example of Oracle error ORA-29758 you can get a list of the objects (logical or physical or both) that are blocking a “drop tablespace” command and that makes it easier to find out what else you need to drop before you can drop the tablespace.
Bear in mind that I’ve just demonstrated the approach while experimenting with a couple of Context indexes, where the information I needed was available through a couple of ordinary data dictionary views – I only dived down into the internals because the data dictionary views weren’t giving the OP a complete answer.
I don’t know what was in the OP’s database to make this problem happen (it may simply be that the database was version 11.2.0.4 while mine was 19.11.0.0 and there’s a bug that’s been fixed since 11g), so the OP may find that after they’ve cleared all domain indexes indicated by this query they may still see ORA-29758 and find that there’s another query that Oracle uses to check for other conditions (maybe those “from statistics type” secondary objects) that also needs to be hijacked and subverted to complete the job. I know that when I traced a successful “drop tablespace” there were 13 more statements after the one I’ve shown that looked as if they might be checking for the (non-)existence of other types of object before the tablespace could be dropped.
Update
The OP has reported back that following this strategy they’ve chased the problem through (for their system) three further sets of objects, with the following error messages:
ORA-23515: materialized views and/or their indices exist in the tablespace
ORA-25183: index-organized table top index segment is in a different tablespace
ORA-02449: unique/primary keys in table referenced by foreign keys
Oracle Scratchpad 
[…] ORA-39857 (June 2022): using a “drop tablespace” problem to describe a generic method for finding out why Oracle is not doing what you thought it would. […]
Pingback by Troubleshooting catalogue | Oracle Scratchpad — June 20, 2022 @ 3:34 pm BST Jun 20,2022 |
Nice article. Good analysis. I’ve had to run down this rabbit hole myself a few times. It kind of makes the “including contents” clause rather misleading, don’t you think?
Comment by Michael Fontana — June 21, 2022 @ 1:39 pm BST Jun 21,2022 |
Michael,
Thanks for the comment – sorry it’s taken so long to reply.
Definitely misleading – or, as I put it more kindly in the note, the message ought to be enhanced to suggest dependencies.
Reading your comment I pondered briefly the effect of enhancing the “drop tablespace” to allow the extra clause “and dependents” – but decided that was a little too scary as it would be too easy for someone to drop something because they didn’t realise it was still needed by some other object.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — November 17, 2022 @ 12:07 pm GMT Nov 17,2022 |
Hello, Congratulations !! very good documents..
Comment by Anonymous — June 22, 2022 @ 3:55 pm BST Jun 22,2022 |
Thanks for the comment.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — November 17, 2022 @ 12:08 pm GMT Nov 17,2022 |