Re: Failded oracle migration when call oracle stored procedure ANALYZE_SCHEMA

[Deville pj]

Hi

I had the same probleme and it occur even when run in SQLPlus and logged as SYSTEM :
--------------------------------------
Enter user-name: system
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CALL DBMS_UTILITY.ANALYZE_SCHEMA('TPUSER', 'ESTIMATE', 50, 0, 'FOR TABLE');
CALL DBMS_UTILITY.ANALYZE_SCHEMA('TPUSER', 'ESTIMATE', 50, 0, 'FOR TABLE')
*
ERROR at line 1:
ORA-20000: You have insufficient privileges for an object in this schema.
ORA-06512: at "SYS.DBMS_UTILITY", line 475
ORA-06512: at line 1
--------------------------------------


This error is due to the recycle bin which was not empty. ANALYZE_SCHEMA was trying to gather stat on the recycle bin objects.
You can see theses objects with :
SELECT * FROM DBA_RECYCLEBIN;

The solution is to empty recycle bin with :
PURGE recyclebin; -- Empty The Recycle Bin
or
PURGE dba_recyclebin; -- Empty Everything In All Recycle Bins


After that, it works for me.