Hello,
I'm trying to move several schemas to a different tablespace (because some objects were created in the wrong tablespace).
Although this is not a big deal for regular table and indexes, I can't figure out how to find the segments that belong to a DOMAIN index in order to check if they are in the right (or wrong) tablespace.
A select * from dba_indexes where index_type = 'DOMAIN' gives me those indexes.
The only way to figure out the corresponding segments for the secondary objects seems to be to match on the segment names. So if the index is name FOO_IDX, the segments would be DR$FOO_IDX$I, DR$FOO_IDX$K, DR$FOO_IDX$R and DR$FOO_IDX$N.
What I currently have, is the following statement, but it takes ages (> 5 minutes) to find the domain indexes that on the "wrong" tablespace even for just a single user:
select *
from dba_indexes idx
where idx.owner = 'FOOBAR'
and idx.index_type = 'DOMAIN'
and exists (select 1
from dba_segments seg
where regexp_replace(seg.segment_name, '^DR\$|\$[RIKN]$', '') = idx.index_name
and seg.owner = idx.owner
and seg.tablespace_name = 'WRONG_TBS'
and seg.segment_type = 'TABLE');
I can't find a system view that would link a segment to the "owning" index directly without reverting to name mangling.
This is my Oracle version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production