Hello, we have following problem.
Database: 10.2.0.5.0
We want to move all objects to another tablespace. Most of them were moved (table, lobs,..).
But now we have 13 elements (TABLE, LOBINDEX, INDEX, LOBSEGMENT) in the BIGFILE-TS we can't move, rebuild, drop etc.
select segment_name, PARTITION_NAME, segment_type, header_file, header_block from dba_segments where tablespace_name like 'MYTS' and OWNER = 'OFFICE' order by TABLESPACE_NAME, blocks desc;
When I'm looking for the IOT_NAME, I only get a NULL-Value
select iot_name from user_tables where table_name in ('SYS_IOT_OVER_56507'); -- same like dba_tables
Is there any way to find out, to whom the elements belong or how I can drop them? I also took the tablespace offline for a while and had no problems.
I think, somebody had dropped (dirty) e.g. a queue (table) and these objects were part of them.
If I'm sure that these objects will no longer required how could I drop them?
drop table SYS_IOT_OVER_56507;
SQL-Fehler: ORA-25191: cannot reference overflow table of an index-organized table
25191. 00000 - "cannot reference overflow table of an index-organized table"
*Cause: An attempt to directly access the overflow table of an
index-organized table
*Action: Issue the statement against the parent index-organized table
containing the specified overflow table.
drop index SYS_IOT_TOP_56503;
SQL-Fehler: ORA-01418: specified index does not exist
01418. 00000 - "specified index does not exist"
I hope, somebody had the same problem and could solve it.
thanks & Best Regards,
Gerald