我今天发现数据库不能删除USER,几个新建未用的USER均不能删除,提示如下:
DROP USER "QUOTA" CASCADE
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
when I compiled all db objects,I found errors above ten views and package body on SYS scheme.
SYS的某些表和视图不存在,是不是有谁意外删除了SYSTEM的某些表,怎样修改这些错误呢?
那位好心人告诉我,我的MSN是:[email protected]
Type JAVA CLASS
Owner SYS
Name /ad6de920_dbExecuteTag
Line Error text
0 ORA-29534: referenced object SYS.oracle/jsp/dbutil/CursorBean could not be resolved
Type JAVA CLASS
Owner SYS
Name oracle/jsp/dbutil/BeanUtil
Line Error text
0 ORA-29521: referenced name oracle/xml/sql/query/OracleXMLQuery could not be found
Type JAVA CLASS
Owner SYS
Name oracle/jsp/dbutil/ConnBean
Line Error text
0 ORA-29534: referenced object SYS.oracle/jsp/dbutil/CursorBean could not be resolved
Type JAVA CLASS
Owner SYS
Name oracle/jsp/dbutil/CursorBean
Line Error text
0 ORA-29534: referenced object SYS.oracle/jsp/dbutil/BeanUtil could not be resolved
Type JAVA CLASS
Owner SYS
Name oracle/jsp/dbutil/DBBean
Line Error text
0 ORA-29534: referenced object SYS.oracle/jsp/dbutil/BeanUtil could not be resolved
Type JAVA CLASS
Owner SYS
Name oracle/jsp/tags/sql/dbCloseTag
Line Error text
0 ORA-29534: referenced object SYS.oracle/jsp/dbutil/ConnBean could not be resolved
Type JAVA CLASS
Owner SYS
Name oracle/jsp/tags/sql/dbNextTag
Line Error text
0 ORA-29534: referenced object SYS.oracle/jsp/tags/sql/dbQueryTag could not be resolved
Type JAVA CLASS
Owner SYS
Name oracle/jsp/tags/sql/dbOpenTag
Line Error text
0 ORA-29534: referenced object SYS.oracle/jsp/dbutil/ConnBean could not be resolved
Type JAVA CLASS
Owner SYS
Name oracle/jsp/tags/sql/dbQueryTag
Line Error text
0 ORA-29534: referenced object SYS.oracle/jsp/dbutil/CursorBean could not be resolved
Type VIEW
Owner SYS
Name ALL_QUEUES
Line Error text
0 ORA-00942: table or view does not exist
Type VIEW
Owner SYS
Name DBA_QUEUES
Line Error text
0 ORA-00942: table or view does not exist
Type VIEW
Owner SYS
Name DBA_QUEUE_SCHEDULES
Line Error text
0 ORA-00942: table or view does not exist
Type VIEW
Owner SYS
Name DBA_QUEUE_TABLES
Line Error text
0 ORA-00942: table or view does not exist
Type VIEW
Owner SYS
Name DEFDEFAULTDEST
Line Error text
0 ORA-00942: table or view does not exist
Type VIEW
Owner SYS
Name DEFERRCOUNT
Line Error text
0 ORA-04063: view "SYS.DEFERROR" has errors
Type VIEW
Owner SYS
Name DEFERROR
Line Error text
0 ORA-00942: table or view does not exist
Type VIEW
Owner SYS
Name DEFLOB
Line Error text
0 ORA-00980: synonym translation is no longer valid
Type VIEW
Owner SYS
Name DEFPROPAGATOR
Line Error text
0 ORA-00942: table or view does not exist
Type VIEW
Owner SYS
Name DEFSCHEDULE
Line Error text
0 ORA-00942: table or view does not exist
Type VIEW
Owner SYS
Name DEFTRANDEST
Line Error text
0 ORA-00942: table or view does not exist
Type VIEW
Owner SYS
Name PLUGGABLE_SET_CHECK
Line Error text
0 ORA-04063: view "SYS.UNI_PLUGGABLE_SET_CHECK" has errors
Type VIEW
Owner SYS
Name STRADDLING_TS_OBJECTS
Line Error text
0 ORA-04063: view "SYS.DBA_QUEUE_TABLES" has errors
Type VIEW
Owner SYS
Name TS_PLUG_INFO
Line Error text
0 ORA-04063: view "SYS.STRADDLING_TS_OBJECTS" has errors
Type VIEW
Owner SYS
Name UNI_PLUGGABLE_SET_CHECK
Line Error text
0 ORA-04063: view "SYS.TS_PLUG_INFO" has errors
Type VIEW
Owner SYS
Name USER_QUEUES
Line Error text
0 ORA-00942: table or view does not exist
Type VIEW
Owner SYS
Name USER_QUEUE_SCHEDULES
Line Error text
0 ORA-00942: table or view does not exist
Type VIEW
Owner SYS
Name USER_QUEUE_TABLES
Line Error text
0 ORA-00942: table or view does not exist
From metalink:
·fact: Oracle Server - Enterprise Edition 8
·fact: Oracle Spatial
·symptom: Errors reported during DROP USER CASCADE
·symptom: ORA-00604: error occurred at recursive SQL level 1
·symptom: ORA-00942: table or view does not exist
·symptom: ORA-06512: at line %
·change: Script CATMD.SQL was executed to setup SDOPT against the database.
·cause: During a DROP USER CASCADE, Oracle attempts to remove any schema related information from the SDOPT product tables.
Enabling SQL_TRACE showed that the failing statement was DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = '''|| dictionary_obj_name || ''' ';
The above statement is issued from a PL/SQL block.
However, table SDO_GEOM_METADATA_TABLE does not exist in the database.
The MDSYS schema (used by the SDOPT product) had been created with a default tablespace pointing to a tablespace of type TEMPORARY. As a result of this, CATMD.SQL failed to run successfully and only partially installed the SDOPT related objects.
If the Spatial Option is not required, then removing this option will also fix the problem.
fix:
Change the default tablespace for the MDSYS schema to a more appropriate
tablespace of type PERMANENT and rerun CATMD.SQL.
[/COLOR]
我用TOAD、PL DEVELOP在CLIENT段都无权改数据库SQL_TRACE或重做数据库吧,我刚到新公司,还没以SYSDBA身份登陆过呢,我觉得数据库有点乱,有什么好办法吗?
table SDO_GEOM_METADATA_TABLE does not exist in the database。