Another interesting issue

[Linda]

Hi Erman!
Again me , seeing this or do I have  a bug now :)
create materialized  view XX_MV
gives ORA-955 name is already used by an existing object
SELECT * FROM dba_OBJECTS WHERE OBJECT_NAME LIKE '%XX_MV%'  ->  doesn’t show any object XX_MV.
 
dba_OBJECTS using    select from sys."_CURRENT_EDITION_OBJ" (joined with user).

select * from sys."_CURRENT_EDITION_OBJ" where NAME LIKE 'XX_MV'
Here I get XX_MV with type# 10 where 10 means object not exists (based on comment in view dba_OBJECTS).
The adop phase is all completed.
resycle bin is oFf in database. Still run purged recycle_bin.. Still the same

Any idea how I can cleanup this object type 10 ?
Thanks for the hint!
Br,Linda

[ErmanArslansOracleBlog]

Hi Linda,

Can you please enable the relevant trace and review the trace files for this.

alter session set events '955 trace name errorstack level 10';
alter session set events '10046 trace name context forever, level 12';
execute your drop here
alter session set events '955 trace name errorstack off';
alter session set events '10046 trace name context off';
Check the trace file (upload it to me as well)

[Linda]

Hi Erman!
Thank you for fast troubleshooting hint!

As I mentioned the adop  phase=cleanup was run..
So I run again  it with :  adop phase=cleanup cleanup_mode=full
It completed and  the row was gone..
So that  made the trick..

So..  in default mode- standard not everything cleaning, so it can give such weird problems :)

Have a nice day!
Br,Linda

[ErmanArslansOracleBlog]

:) yes. That is a good point. That is the tip of the day:)

However, we could clean it specifically and manually without a full cleanup. (after seeing the trace ofcourse :))

Anyways, have a nice day.

[Linda]

Hi Erman!
Can you share how we can cleanup specifically object ? Just for future references :)
Thanks,Linda

[ErmanArslansOracleBlog]

Hi Linda,

Interesting question. There is no documented way of doing this. :)
However, we can create our own way, by using what we are given in "ad_zd.cleanup" package.
There is a reference there. Reference to "sys.ad_zd_sys.drop_covered_object".

Here is the definion of it:

/*
** Drop Covered Objects
**
** Drop objects in retired editions that have a replacement object in any newer edition.
** This is done for both ACTUAL objects and STUB objects.
**
** x_execute
**   true:  Execute the DDLs immediately
**   false: Save DDL to parallel execution service

On the other hand; we need to test it. :)

Here I m testing it;

first I execute the below query to identify the objects;(just like ad_zd.cleanup does with drop_covered_objects procedure..

-- Covered Objects are  
--    actual objects in an Old Edition
--    that have a replacement object in a newer edition
-- Note: the query only returns objects with no dependents
--       and must be processed repeatedly to get all objects
 select co.edition_name, co.owner, co.object_type, co.object_name
    from
        dba_objects_ae co
      , database_properties run /* run edition name */
    where run.property_name = 'DEFAULT_EDITION'
      and co.object_type <> 'NON-EXISTENT'
      and co.edition_name is not null
      and co.edition_name < run.property_value
      and exists
            ( select null from dba_objects_ae ro /* replacement object */
              where ro.owner        = co.owner
              and   ro.object_name  = co.object_name
              and   ro.namespace    = co.namespace
              and   ro.edition_name > co.edition_name
              and   ro.edition_name <= run.property_value )
      and not exists
            ( select null from sys.dependency$ dep /* dependents */
              where  dep.p_obj# = co.object_id )
    order by co.edition_name desc, co.owner, co.object_name

Then I choose of the objects that is returned from the query and execute "sys.ad_zd_sys.drop_covered_object(objrec.owner, objrec.object_name, objrec.object_type, objrec.edition_name);"

For example:
exec('APPS','ARP_DEDUCTION_COVER','PACKAGE BODY','V_20160522_1220');

Then I execute the above query once again and see the package body ARP_DEDUCTION_COVER is not returned anymore.

However, I didn't test a whole patching after doing this.
so you can test it if you can :)
If you work on this and find some more info about this subject, please update here as well

Thanks:)