ora-00604 ora-00942 問題處理<轉>

taogchan發表於2012-01-05

資料庫版本oracle10.2.0.1 ,使用者無法刪除

SQL> drop user mr cascade;

drop user mr cascade

ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist


開啟跟蹤:

alter session set events '10046 trace name context forever,level 12';

執行刪除使用者:drop user MR cascade

關閉跟蹤:

alter session set events '10046 trace name context off';

在伺服器端/oracle/admin/orcl/udump目錄下可看到trace檔案

用tkprof orcl2_ora_561298.trc 檔名 分析到檔案,開啟該檔案可看到:

The following statement encountered a error during parse:

delete from system.aq$_internet_agent_privs WHERE db_username = NLS_UPPER(:1

Error encountered: ORA-00942

查metlink文件,其提供的解決方案如下:

Cause

The table system.aq$_internet_agent_privs is missing.

Solution

To implement the solution, execute the following steps:

1.Check if system.aq$_internet_agent_privs exist.

SQL> conn / as sysdba
SQL> desc system.aq$_internet_agent_privs

2.Make sure that the default tablespace of SYSTEM user is properly set to SYSTEM tablespace.

SQL> select default_tablespace from dba_users where username='SYSTEM';

DEFAULT_TABLESPACE
------------------------------
SYSTEM

3.If system.aq$_internet_agent_privs does not exist, run $ORACLE_HOME/rdbms/admin/catqueue.sql
script. manually logged in as 'SYS AS SYSDBA'. This will create the system.aq$_internet_agent_privs
table

SQL> conn / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/catqueue.sql
SQL> exit

4.Confirm that system.aq$_internet_agent_privs is created properly:

SQL> desc system.aq$_internet_agent_privs

Name Null? Type
----------- -------- ------------
AGENT_NAME NOT NULL VARCHAR2(30)
DB_USERNAME NOT NULL VARCHAR2(30)

5.Then execute the DROP USER command again.

執行該指令碼後,問題解決

但執行後 發現exp有問題:報ora-01403 ora-06512

以下是解決辦法:

$sqlplus "/as sysdba"
SQL> startup upgrade
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql

http://blog.itpub.net/post/528/470116

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22392018/viewspace-714434/,如需轉載,請註明出處,否則將追究法律責任。

相關文章