hi,
oracle database version 9.2.0.7 and windows, no archive log mode.
we have strange issues like dropping the packages we handled like :
Ø Select * from v$access where object=’<pkg name>’;
Ø Clear the sessions holding this package in case if any
Ø Drop package body owner.<pck body name>;
Ø Drop package owner.<pck name>;
Ø Here we get the error like > ORA-04043: object SYS_PLSQL_2700917_191_1
does not exist
Ø Then we need to drop all the types with this id no matter what schema
Ø To identify the objects use the below query>
select owner, object_name, object_type
from dba_objects
where object_name like 'SYS_PLSQL_2700917%'
order by object_name;
Ø Once dropping all the objects then drop the package as “drop package
owner.<pck name> ;
Ø And issue commit at the end;
only commit cleared the dropping of object this is strange
now we have the issue is while compiling the database seems to be hung for hours withoout any progress. no locks found and i have bounced the database but no luck.
SQL> EXEC DBMS_UTILITY.compile_schema('<schema name>');
BEGIN DBMS_UTILITY.compile_schema('<schema name>'); END;
*
ERROR at line 1:
ORA-20000: You have insufficient privileges for an object in this schema.
ORA-06512: at "SYS.DBMS_UTILITY", line 423
ORA-06512: at line 1