Today we have faced a strange problem. After the online reorganization of the PSAPBW3 tablespace was finished, i found out, that there are still 3 objects in the original tablespace. I searched through dba_segments view and found out, that both,the original table and the interim table, from the dbms_redefiniton exists. I tried to finish the reorganization wit dbms_redefinition.finish_redef_table, but got error ORA-12093: invalid interim table "SAPBW3"."/BI0/SSALES_OFF#$". I tryed to abort it trough dbms_redefiniton.abort_redef_table. The procedure finished successfully, but both tables were still in the database. We decided to drop the interim table and reorganize it again. I tried to drop the table, but got:
SQL> drop table "/BI0/SSALES_OFF#$";
drop table "/BI0/SSALES_OFF#$"
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "SAPBW3"."/BI0/SSALES_OFF#$"
Ok. so next thing, what I tryed was drop materialized view:
SQL> drop materialized view "SAPBW3"."/BI0/SSALES_OFF#$";
drop materialized view "SAPBW3"."/BI0/SSALES_OFF#$"
*
ERROR at line 1:
ORA-12003: materialized view "SAPBW3"."/BI0/SSALES_OFF#$" does not exist
I have looked in the dba_mviews and the materialized view was not there. I have looked on the google, and metalink and I found out the 265455.1 It was not my case, but on the bottom, there was Note 148379.1 about Drop Table Fails with Error ORA-12083. I followed the note and created snapshot on the "SAPBW3"."/BI0/SSALES_OFF#$" table:
SQL> create snapshot sapbw3."/BI0/SSALES_OFF#$" on prebuilt table as select * from sapbw3."/BI0/SSALES_OFF";
Materialized view created.
After that I tryed to drop the table again:
SQL> drop table sapbw3."/BI0/SSALES_OFF#$";
drop table sapbw3."/BI0/SSALES_OFF#$"
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "SAPBW3"."/BI0/SSALES_OFF#$"
It was not working, so I tryed to drop the materialized view:
SQL> drop materialized view sapbw3."/BI0/SSALES_OFF#$";
Materialized view dropped.
After that I was able to drop the stupid table:
SQL> drop table sapbw3."/BI0/SSALES_OFF#$";
Table dropped.
And voila... The table was gone:
SQL> select segment_name,segment_type from dba_segments where segment_name like '/BI0/SSALES_OFF%';
SEGMENT_NAME SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------
/BI0/SSALES_OFF TABLE
/BI0/SSALES_OFF~0 INDEX
/BI0/SSALES_OFF~00 INDEX
After this, we ran the online reorg of the table again, and the old tablespace was empty :-)
SQL> drop table "/BI0/SSALES_OFF#$";
drop table "/BI0/SSALES_OFF#$"
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "SAPBW3"."/BI0/SSALES_OFF#$"
Ok. so next thing, what I tryed was drop materialized view:
SQL> drop materialized view "SAPBW3"."/BI0/SSALES_OFF#$";
drop materialized view "SAPBW3"."/BI0/SSALES_OFF#$"
*
ERROR at line 1:
ORA-12003: materialized view "SAPBW3"."/BI0/SSALES_OFF#$" does not exist
I have looked in the dba_mviews and the materialized view was not there. I have looked on the google, and metalink and I found out the 265455.1 It was not my case, but on the bottom, there was Note 148379.1 about Drop Table Fails with Error ORA-12083. I followed the note and created snapshot on the "SAPBW3"."/BI0/SSALES_OFF#$" table:
SQL> create snapshot sapbw3."/BI0/SSALES_OFF#$" on prebuilt table as select * from sapbw3."/BI0/SSALES_OFF";
Materialized view created.
After that I tryed to drop the table again:
SQL> drop table sapbw3."/BI0/SSALES_OFF#$";
drop table sapbw3."/BI0/SSALES_OFF#$"
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "SAPBW3"."/BI0/SSALES_OFF#$"
It was not working, so I tryed to drop the materialized view:
SQL> drop materialized view sapbw3."/BI0/SSALES_OFF#$";
Materialized view dropped.
After that I was able to drop the stupid table:
SQL> drop table sapbw3."/BI0/SSALES_OFF#$";
Table dropped.
And voila... The table was gone:
SQL> select segment_name,segment_type from dba_segments where segment_name like '/BI0/SSALES_OFF%';
SEGMENT_NAME SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------
/BI0/SSALES_OFF TABLE
/BI0/SSALES_OFF~0 INDEX
/BI0/SSALES_OFF~00 INDEX
After this, we ran the online reorg of the table again, and the old tablespace was empty :-)