数据库版本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
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