1

I'm not a DBA, but let's hope I'll be able to describe our current Problem.

We have a rather large partitioned table that we reorganize regularly via dbms_redefinition.start_redef_table(..) etc. . We had collision with other stuff going on in our database, so this has failed a few times in the past. But it hasn't failed in the last few month. Since the last failure we have reorganized each of the partitions, so the table and all partitions are in a healthy state. We are not experiencing any performance issues due to possible mview updates.

Apparently after one of those failures some mlog$_xxx and rupd$_xxx objects started showing up. Today we have over 100 mlog$_xxx objects and over 30 rupd$_xxx objects. And it keeps getting more. This worries me.

Following did not work: drop materialized view log on xxx; stating me

ORA-12002 that there is no materialized view log on xxx.

when I do: select * from dba_mviews; ... then I get no results

I'm confused now. I have a table and I have mview logs, but I have no mview in between.

We made a database copy to a test machine and I was able to drop the table. But the mlog$_xxx objects were still there.

Can somebody help me on how to clean up this mess?

Aleksej
  • 22,443
  • 5
  • 33
  • 38
EasterBunnyBugSmasher
  • 1,507
  • 2
  • 15
  • 34
  • Is it possible these are in your recycle bin? That can happen with LOBs, [and can be confusing](http://stackoverflow.com/q/18367444/266304). I can't test so just a guess. It might be helpful to say exactly which version and patch level you are on, in case this is a known and/or fixed issue. – Alex Poole Oct 12 '16 at 10:03
  • good hint, but there is nothing in the recycle bin. Oracle Version is 11.2.0.4.0 – EasterBunnyBugSmasher Oct 12 '16 at 10:21

1 Answers1

0

we contacted Oracle Support and they suggested to delete obj$ and sum$ entries. It worked. Don't try this at home!!!

EasterBunnyBugSmasher
  • 1,507
  • 2
  • 15
  • 34
  • Hi, we have the same problem in our database (Oracle 12). Can you explain your answer better?, I mean, I don't understand what do you mean with obj$ and sum$ entries. – doctore Dec 29 '17 at 09:45
  • @doctore : we executed this: delete from obj$ where obj# in (select o.OBJECT_ID from dba_objects o where o.OBJECT_NAME like 'table_name' and o.OWNER = 'me'); for obj$ and sum$ tables. I'm not a DBA, so don't ask me about details or risks. – EasterBunnyBugSmasher Jan 03 '18 at 11:21