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!
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)
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.
** 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
, 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
( 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);"
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