Everyone,
Rerun catalog script together with catproc, catalog ended fine but catproc apparently ended with an error of "ORA-20000: Insufficient privileges to analyze an object in Dictionary Schema". It also caused some of the components in DBA_REGISTRY to become invalid (before the ran scripts all comp_names were valid).
SQL> show user
USER is "SYS"
SQL>
SQL> @?\RDBMS\ADMIN\catproc.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following PL/SQL block will cause an ORA-20000 error and
DOC> terminate the current SQLPLUS session if the user is not SYS.
DOC> Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
...........
...........
PL / SQL procedure successfully completed.
Package body has been created.
No errors.
Package body has been created.
BEGIN *
line 1 ORA-20000: Insufficient privileges to analyze an object in Dictionary Schema
ORA-06512: 在 "SYS.DBMS_STATS", line 13197
ORA-06512: 在 "SYS.DBMS_STATS", line 13517
ORA-06512: 在 "SYS.DBMS_STATS", line 15859
ORA-06512: 在 "SYS.DBMS_STATS", line 15901
ORA-06512: 在 line 1
ORA-06512: 在 "SYS.DBMS_REGISTRY", line 560
ORA-06512: 在 "SYS.DBMS_REGISTRY", line 612
ORA-06512: 在 line 4
SQL>select comp_name, status from dba_registry order by status asc;
COMP_NAME STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
OLAP Catalog INVALID
Oracle Data Mining INVALID
Spatial INVALID
Oracle Rules Manager INVALID
Oracle Expression Filter INVALID
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
OLAP Analytic Workspace VALID
Oracle OLAP API VALID
Oracle Text VALID
Oracle XML Database VALID
Oracle interMedia VALID
Oracle Enterprise Manager VALID
Oracle Workspace Manager VALID
17 rows selected
Tried to do what this link says http://www.oraclealchemist.com/news/when-errors-tell-you-absolutely-nothing/ about ORA-20000, like executing dbms_registry and dbms_stats with sys having both ANALYZE ANY and ANALYZE ANY DICTIONARY privileges or extending TEMP tbs, but it resulted to same error again. So any tip on how to resolve cases like this?? Thanks in advance.
SQL>select grantee, privilege from dba_sys_privs where privilege like 'ANALYZE%' order by grantee asc;
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
DBA ANALYZE ANY
DBA ANALYZE ANY DICTIONARY
IMP_FULL_DATABASE ANALYZE ANY
OEM_MONITOR ANALYZE ANY
OEM_MONITOR ANALYZE ANY DICTIONARY
OLAP_DBA ANALYZE ANY
SYS ANALYZE ANY
SYS ANALYZE ANY DICTIONARY
8 rows selected
SQL> show user
USER 为 "SYS"
SQL> execute dbms_registry.gather_stats(null);
BEGIN dbms_registry.gather_stats(null); END;
*
Error Line 1: :
ORA-20000: Insufficient privileges to analyze an object in Dictionary Schema
ORA-06512: 在 "SYS.DBMS_STATS", line 13197
ORA-06512: 在 "SYS.DBMS_STATS", line 13517
ORA-06512: 在 "SYS.DBMS_STATS", line 15859
ORA-06512: 在 "SYS.DBMS_STATS", line 15901
ORA-06512: 在 "SYS.DBMS_REGISTRY", line 562
ORA-06512: 在 line 1
SQL> exec dbms_stats.gather_table_stats('SYS','DBMS_STATS');
BEGIN dbms_stats.gather_table_stats('SYS','DBMS_STATS'); END;
*
Error Line 1: :
ORA-20000: Unable to analyze TABLE "SYS"."DBMS_STATS", insufficient privileges
or does not exist
ORA-06512: 在 "SYS.DBMS_STATS", line 13046
ORA-06512: 在 "SYS.DBMS_STATS", line 13076
ORA-06512: 在 line 1
SQL> exec dbms_stats.gather_table_stats('SYS','DBMS_REGISTRY');
BEGIN dbms_stats.gather_table_stats('SYS','DBMS_REGISTRY'); END;
*
Error Line 1: :
ORA-20000: Unable to analyze TABLE "SYS"."DBMS_REGISTRY", insufficient
privileges or does not exist
ORA-06512: 在 "SYS.DBMS_STATS", line 13046
ORA-06512: 在 "SYS.DBMS_STATS", line 13076
ORA-06512: 在 line 1
Regards,
Mitch