Oracle Database Discussions

Greetingss,
Installed 11.2.0.1 several months ago and upgraded to 11.2.0.2 a month ago without issues. However prior to upgrade I was able to drop schema objects. Since upgrade I do not recall specifically dropping any objects. However, now trying to drop a few objects and discovered all drops attempted are failing, i.e. tables, packages, users, function, views, directories, etc. Create or Replace and Alter all appear to still work.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

5 rows selected.


SQL> connect sys as sysdba
Connected.

SQL> create user drop_test identified by drop_test account unlock;

User created.

SQL> alter user drop_test default tablespace users;

User altered.

SQL> grant connect, resource, dba to drop_test;

Grant succeeded.

SQL> connect drop_test/drop_test
Connected.

SQL> create table a (a number);

Table created.

SQL> create view av as select * from a;

View created.

SQL> create function ac return number as
2 result number;
3 begin
4 select count (*) into result from a;
5 return result;
6 end;
7 /

Function created.

SQL> insert into a values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select ac from dual;

AC
----------
1

1 row selected.

SQL> select * from av;
Enter
A
----------
1

1 row selected.

SQL> drop function ac;
drop function ac
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 3, column 83:
PLS-00302: component 'DBMS_XDBZ' must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored


SQL> drop view av;
drop view av
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 3, column 83:
PLS-00302: component 'DBMS_XDBZ' must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored


SQL> drop table a;
drop table a
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 3, column 83:
PLS-00302: component 'DBMS_XDBZ' must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored


SQL> connect sys as sysdba
Connected.
SQL> drop function drop_test.ac;
drop function drop_test.ac
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 3, column 83:
PLS-00302: component 'DBMS_XDBZ' must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored


SQL> drop view drop_test.av;
drop view drop_test.av
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 3, column 83:
PLS-00302: component 'DBMS_XDBZ' must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored


SQL> drop table drop_test.a;
drop table drop_test.a
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 3, column 83:
PLS-00302: component 'DBMS_XDBZ' must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored


SQL> drop user drop_test;
drop user drop_test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 3, column 83:
PLS-00302: component 'DBMS_XDBZ' must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored


SQL> drop user drop_test cascade;
drop user drop_test cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 3, column 83:
PLS-00302: component 'DBMS_XDBZ' must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored


SQL> get /x92
1 select owner, object_name, object_type, status
2 from dba_objects
3* where object_name = 'DBMS_XDBZ'
SQL> /

OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- -------
PUBLIC DBMS_XDBZ SYNONYM VALID
XDB DBMS_XDBZ PACKAGE VALID
XDB DBMS_XDBZ PACKAGE BODY VALID

3 rows selected.

SQL> @invalid

no rows selected

SQL> l
1 select
2 owner c1,
3 object_type c3,
4 object_name c2
5 from
6 dba_objects
7 where
8 status != 'VALID'
9 order by
10 owner,
11 object_type
12*

Advanced appreciation for any assistence provided.

best Regards