And what is the type of this object?
A reader, January 09, 2019 - 2:48 pm UTC
Hello,
What is the result of this query :
SELECT owner, object_type, status from dba_objects where object_name = 'TO_BE_DROPPED';
I ask that because you try to drop a table and after a materialized view log and after a materialized view. I think the first step is to be sure about the object's type and the schema.
And, after, try this, because if the object was built with a "weird" option, maybe it could be difficult to drop it.
If it is a table :
SELECT dbms_metadata.get_ddl('TABLE', 'TO_BE_DROPPED', 'the_owner') from dual;and maybe your dba will see a solution.
January 09, 2019 - 2:53 pm UTC
Yep. I'm assuming there genuinely is something weird about this object. But it's worth checking the basics.
Missing bits
Ferko Mrkvicka, January 09, 2019 - 3:11 pm UTC
Some additional information, but it doesn't reveal much. That's why I didn't provide it in the original question. It gives the impression that TO_BE_DROPPED is an ordinary table.
SELECT owner, object_type, status from dba_objects where object_name = 'TO_BE_DROPPED';
>> SCHEMA, TABLE, VALID
DDL:
CREATE TABLE "SCHEMA"."TO_BE_DROPPED"
( "TEST" VARCHAR2(100)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SDATA01"
January 09, 2019 - 3:39 pm UTC
Yeah, I think you're going to have to go to support to resolve this. Or rebuild the schema!
YES!
David, January 09, 2019 - 3:28 pm UTC
You have a user called SCHEMA but it is a reserved word.
select count(*) from V$RESERVED_WORDS where keyword = 'SCHEMA';
COUNT(*)
-----------
1
Try this, with "" to escape the special word SCHEMA.
DROP TABLE "SCHEMA"."TO_BE_DROPPED";
Is it OK?
January 09, 2019 - 3:42 pm UTC
You can use reserved words - provided you quote them.
create table "TABLE" ( c1 int );
select table_name from user_tables
where table_name = 'TABLE';
TABLE_NAME
TABLE
drop table "TABLE" purge;
And this doesn't explain why it only affects this table...
And no...
David, January 09, 2019 - 3:37 pm UTC
Sorry, I failed because I can create a user called SCHEMA.
SQL> create user SCHEMA identified by YukioYukio78964125;
Utilisateur cree.
But it is a reserved word... it's strange...
Renamed it...
David, January 09, 2019 - 4:28 pm UTC
Well, on Metalink I read that the problem is about the RENAME operation :
"Drop MATERIALIZED VIEW LOG on Table fails if Mview Log was Renamed (Doc ID 2435740.1)
The materialized log is huge and was renamed. After renaming that MLOG$_% Table, Drop Table / Drop Materialized view is reporting ORA- Errors."
I let TOM writes here the solution from the note because I read that it was not permitted to copy/past all a page from Metalink.
David
January 10, 2019 - 1:27 am UTC
Thanks for the input.
Yes - if you rename internal objects, then trouble will no doubt follow :-)
In 12c the workaround is to rename it back to its proper name, then drop:
rename mlog$_t_bkp to mlog$_t;
drop MATERIALIZED VIEW LOG on t;
R.I.P. cat
Racer I., January 10, 2019 - 7:25 am UTC
Hi,
Just out of curiosity I tried this :
create table t (ID NUMBER(1));
alter table t add constraint pk_t primary key (ID);
create materialized view log on t;
select * from dba_mview_logs where master = 'T';
alter table MLOG$_T rename to OTHER; --5)
select * from dba_mview_logs where master = 'T';
drop materialized view log on t; --1)
select * from dba_mview_logs where master = 'T'; --2)
drop table other; --3)
alter table OTHER rename to MLOG$_T;
drop materialized view log on t; --4)
1) silently succeeds : even though MLOG$_T no longer existed to be dropped. Oracle didn't track the rename...
2) shows no result so everything's peachy?
3) fails with ORA-32417 : OTHER is still there, but cannot be dropped (probably a table flag not exposed in dba_tables)
4) fails with ORA-12002 : renaming doesn't help (connection is severed and you have no way to get rid of the orphan now)
5) I guess this should simply fail, same as with 3)
I guess the OP didn't do 1) while the table was named otherwise so 4) worked for them?
At first glance this seems to work :
create materialized view log on t;
select * from dba_mview_logs where master = 'T';
drop materialized view log on t;
select * from dba_mview_logs where master = 'T';
drop table MLOG$_T; -- ORA-32417
but it ultimately doesn't either because this creates and drops a new table MLOG$_T1 instead of picking up the existing one.
You'd need syntax similar to PRESERVE TABLE/USING for the mat view tables themselves.
This one fell into this trap too I guess :
https://community.oracle.com/thread/3652078 This was different in oracle 9 apparently :
https://community.oracle.com/thread/3731733 but not really better :
https://community.oracle.com/thread/3878770 regards,
January 10, 2019 - 10:42 am UTC
Looks like renaming the MV log table could be the culprit here. Nice investigation.
"SCHEMA" is not a real name
Ferko Mrkvicka, January 11, 2019 - 1:25 pm UTC
The user "SCHEMA" is just a replacement for a real schema name, because I didn't want to expose the real name publicly. I should've used MY_SCHEMA, though, to avoid confusion...
January 11, 2019 - 1:41 pm UTC
:)
Only export and import helped
Ferko Mrkvicka, February 12, 2019 - 7:51 am UTC
Finally the export and import helped. But I had to explicitly ask our DBAs to exclude that corrupt object from the export. At first I didn't do it and after the import this object was still corrupt (undroppable). So I asked the DBAs not to export that object, delete the entire schema, and then import it back, and that was the end of the TO_BE_DROPPED table/m-view. :)
February 12, 2019 - 10:46 am UTC
Thanks for letting us know, glad you solved this!
solved
mik, May 05, 2022 - 2:36 pm UTC
I have managed to drop such a table after clearing bit 65536 of property and bit 268435456 of trigflag in sys.tab$. Oracle version 12.1.0.2. Do not try to do this at home.
May 09, 2022 - 2:31 pm UTC
Do not try to do this at home.
Indeed!