oracle 清理队列表,ORA-24008: 队列表SS_MT_TAB必须首先删除

Host01# su - oracle

Sun Microsystems Inc.  SunOS 5.8

Generic

Patch  February 2004

Host01% sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on 星期三 9月 23 15:03:41

2015

Copyright (c) 1982, 2002, Oracle Corporation.

All rights reserved.

SQL> conn sgdd/sgdd

ERROR:

ORA-01017: invalid username/password; logon denied

SQL> conn sgdd/sgdd123

已连接。

SQL> disc

从Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit

Production

With the Partitioning and Oracle Label Security options

JServer Release 9.2.0.6.0 - Production中断开

SQL> conn /as sysdba

已连接。

SQL> drop user sgdd cascade;

drop user sgdd cascade

*

ERROR 位于第 1 行:

ORA-24008: 队列表SGDD.SS_MT_TAB必须首先删除

SQL> conn sgdd/sgdd123

已连接。

SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE('SS_MT_TAB');

BEGIN DBMS_AQADM.DROP_QUEUE_TABLE('SS_MT_TAB'); END;

*

ERROR 位于第 1 行:

ORA-24012: 无法删除 QUEUE_TABLE，在SGDD.SS_MT_TAB中的某些队列无法被删除

ORA-06512: 在"SYS.DBMS_AQADM_SYS", line 2973

ORA-06512: 在"SYS.DBMS_AQADM", line 197

ORA-06512: 在line 1

SQL> set line 132

SQL> set wrap off

SQL> select object_type,object_name from dba_objects where

object_name like '%S_MT%' and owner='SGDD';

OBJECT_TYPE  OBJECT_NAME

------------------

-----------------------------------------------------------------------------------------------------------------

QUEUE  AQ$_SS_MT_TAB_E

QUEUE  SMS_MT_QUEUE

SQL> drop table AQ$_SS_MT_TAB_E cascade constraints;

drop table AQ$_SS_MT_TAB_E cascade constraints

*

ERROR 位于第 1 行:

ORA-00942: 表或视图不存在

SQL> drop table SMS_MT_QUEUE cascade constraints;

drop table SMS_MT_QUEUE cascade constraints

*

ERROR 位于第 1 行:

ORA-00942: 表或视图不存在

SQL> ALTER SESSION SET EVENTS '10851 trace name context

forever, level 2';

会话已更改。

SQL> drop table AQ$_SS_MT_TAB_E cascade constraints;

drop table AQ$_SS_MT_TAB_E cascade constraints

*

ERROR 位于第 1 行:

ORA-00942: 表或视图不存在

SQL> drop table SMS_MT_QUEUE cascade constraints;

drop table SMS_MT_QUEUE cascade constraints

*

ERROR 位于第 1 行:

ORA-00942: 表或视图不存在

SQL> select object_type,object_name from dba_objects where

object_name like '%S_MT%' and owner='ADMIN';

未选定行

SQL> select object_type,object_name from dba_objects where

object_name like '%S_MT%' and owner='SGDD';

OBJECT_TYPE  OBJECT_NAME

------------------

-----------------------------------------------------------------------------------------------------------------

QUEUE  AQ$_SS_MT_TAB_E

QUEUE  SMS_MT_QUEUE

SQL> select job,what from dba_jobs;

JOB WHAT

----------

-------------------------------------------------------------------------------------------------------------------------

1

COI.CleanServiceAccount;

2 COI.CleanAccount;

328532

JOB_dispatchorderinfo;

312963

SP_RENT_SENDMSG;

319278

sp_rent_freshtiming;

195237

FWBZ_AUTO_REPLY_TO_CRM;

620640

SMS_13_time;

148515

FWBZ_AUTO_REMIND_UNSEND_ORDER;FWBZ_AUTO_REMIND_INPRO_ORDER;

620636

SMS_12_time;

319279

sp_rent_freshtiming;

462645

FWBZ_AUTO_REMIND_RECALL_ORDER;

JOB WHAT

----------

-------------------------------------------------------------------------------------------------------------------------

580230 local_fix;

582922

local_fix_dedicated;

647442

FWBZ_AUTO_FIX_OVERTIME_WARNING;

691760

FWBZ_AUTO_UPDATE_LONGTIME;

691779

FWBZ_AUTO_UPDATE_ALARM_DEPT;

691780

dbms_utility.analyze_schema

321 EverydayCheckData();

341

PKG_DEAL_CIRCUIT_OTHER.SPLITCOMPLETEDELECHILDSDH();

402 overtime_receive;

40340

ORDERWARNING;

421 ZYNTEMP;

JOB WHAT

----------

-------------------------------------------------------------------------------------------------------------------------

4845

UPdate_LEASELINE_TOPTIP;

39714

FWBZ_AUTO_RELEASEHANG;

42726

physicCT;

55312

FWBZ_AUTO_HANGUP;

57331

FWBZ_AUTO_RANDOM_DIAGNOSIS;

55313

FWBZ_AUTO_REPLAY;

55314

FWBZ_AUTO_REMIND;

55999

FWBZ_AUTO_FTLT_REMIND;

76719

FWBZ_AUTO_CUIDAN;

60203

FWBZ_AUTO_CHENGE_PROMPT_TTTYPE;

46 P_ALARM;

JOB WHAT

----------

-------------------------------------------------------------------------------------------------------------------------

562578

SP_YJ_SENDMSG;

835280

WYZC_DEAL_YEAR_MONTH_DATA;

742028

FWBZ_AUTO_INSERT_REPORT;

562559

SP_YJ_CHECKEXPBILL;

827305

dbms_refresh.refresh('"TELECOM"."V_SUBORDER_QUERY_PHONE_SUB"');

891517 next_date :=

sys.dbms_aqadm.aq$_propaq(job);

891515 next_date :=

sys.dbms_aqadm.aq$_propaq(job);

849559

INSERTT_PUSHALERTS;

891516 next_date :=

sys.dbms_aqadm.aq$_propaq(job);

已选择42行。

SQL> desc dba_jobs;

名称  是否为空? 类型

------------------------------------------------------------------------

-------- -------------------------------------------------

JOB  NOT NULL

NUMBER

LOG_USER  NOT NULL VARCHAR2(30)

PRIV_USER  NOT NULL VARCHAR2(30)

SCHEMA_USER  NOT NULL

VARCHAR2(30)

LAST_DATE  DATE

LAST_SEC  VARCHAR2(16)

THIS_DATE  DATE

THIS_SEC  VARCHAR2(16)

NEXT_DATE  NOT NULL DATE

NEXT_SEC  VARCHAR2(16)

TOTAL_TIME  NUMBER

BROKEN  VARCHAR2(1)

INTERVAL  NOT NULL VARCHAR2(200)

FAILURES  NUMBER

WHAT  VARCHAR2(4000)

NLS_ENV  VARCHAR2(4000)

MISC_ENV  RAW(32)

INSTANCE  NUMBER

SQL> select job,what from user_jobs;

未选定行

SQL> exec

DBMS_AQADM.DROP_QUEUE_TABLE('AQ$_SS_MT_TAB_E');

BEGIN DBMS_AQADM.DROP_QUEUE_TABLE('AQ$_SS_MT_TAB_E');

END;

*

ERROR 位于第 1 行:

ORA-24002: QUEUE_TABLE SGDD.AQ$_SS_MT_TAB_E 不存在

ORA-06512: 在"SYS.DBMS_AQADM_SYS", line 2973

ORA-06512: 在"SYS.DBMS_AQADM", line 197

ORA-06512: 在line 1

SQL> select object_type,object_name from user_objects where

object_name like '%MT_TAB%';

OBJECT_TYPE  OBJECT_NAME

------------------

-----------------------------------------------------------------------------------------------------------------

QUEUE  AQ$_SS_MT_TAB_E

SQL> select object_type,object_name from user_objects where

object_name like '%MT_%';

OBJECT_TYPE  OBJECT_NAME

------------------

-----------------------------------------------------------------------------------------------------------------

QUEUE  AQ$_SS_MT_TAB_E

TABLE  MT_BUG

QUEUE  SMS_MT_QUEUE

SQL> drop table SS_MT_TAB cascade constraints;

drop table SS_MT_TAB cascade constraints

*

ERROR 位于第 1 行:

ORA-00942: 表或视图不存在

SQL> drop table SMS_MT_QUEUE force;

drop table SMS_MT_QUEUE force

*

ERROR 位于第 1 行:

ORA-00942: 表或视图不存在

SQL> select obj#,owner#,name,type# from obj$ where name

like '%S_MT%';

select obj#,owner#,name,type# from obj$ where name like

'%S_MT%'

*

ERROR 位于第 1 行:

ORA-00942: 表或视图不存在

SQL> desc dba_users;

名称  是否为空? 类型

------------------------------------------------------------------------

-------- -------------------------------------------------

USERNAME  NOT NULL VARCHAR2(30)

USER_ID  NOT NULL NUMBER

PASSWORD  VARCHAR2(30)

ACCOUNT_STATUS  NOT NULL VARCHAR2(32)

LOCK_DATE  DATE

EXPIRY_DATE  DATE

DEFAULT_TABLESPACE  NOT NULL VARCHAR2(30)

TEMPORARY_TABLESPACE  NOT NULL VARCHAR2(30)

CREATED  NOT NULL DATE

PROFILE  NOT NULL VARCHAR2(30)

INITIAL_RSRC_CONSUMER_GROUP

VARCHAR2(30)

EXTERNAL_NAME  VARCHAR2(4000)

SQL> select user_id from dba_users where

username='SGDD';

USER_ID

----------

102

SQL> select obj#,owner#,name,type# from sys.obj$ where

owner#=102 and name like '%S_MT%';

OBJ#

OWNER# NAME

TYPE#

---------- ---------- ------------------------------

----------

511706

102 AQ$_SS_MT_TAB_E  24

511191

102 AQ$_SS_MT_TAB_N  10

511707

102 SMS_MT_QUEUE  24

SQL> drop sequence AQ$_SS_MT_TAB_N;

drop sequence AQ$_SS_MT_TAB_N

*

ERROR 位于第 1 行:

ORA-02289: 序列(号)不存在

SQL> select eventid,name,table_objno from system.aq$_queues

where name like '%S_MT%' and eventid in(select obj# from sys.obj$

where owner#=102 and name like '%S_MT%');

EVENTID NAME

TABLE_OBJNO

---------- ------------------------------ -----------

511706 AQ$_SS_MT_TAB_E

511210

511707 SMS_MT_QUEUE

511210

SQL> select object_name from dba_objects where

object_id=511210;

未选定行

SQL> select schema,name,objno from system.aq$_queue_tables

where schema='SGDD' and name like '%S_MT%';

SCHEMA  NAME  OBJNO

------------------------------ ------------------------------

----------

SGDD  SS_MT_TAB  511210

SQL> select table_objno from sys.aq$_queue_table_affinities

where table_objno in( select table_objno from system.aq$_queues

where name like '%S_MT%' and eventid in(select obj# from sys.obj$

where owner#=102 and name like '%S_MT%'));

TABLE_OBJNO

-----------

511210

SQL> delete from sys.obj$ where owner#=102 and name like

'%S_MT%';

delete from sys.obj$ where owner#=102 and name like

'%S_MT%'

*

ERROR 位于第 1 行:

ORA-01031: 权限不足

SQL> conn /as sysdba

已连接。

SQL> delete from sys.obj$ where owner#=102 and name like

'%S_MT%';

已删除3行。

SQL> select obj#,owner#,name,type# from sys.obj$ where

owner#=102 and name like '%S_MT%';

未选定行

SQL> delete from sys.aq$_queue_table_affinities where

table_objno =511210;

已删除 1 行。

SQL> delete from system.aq$_queues where name like '%S_MT%'

and eventid in(select obj# from sys.obj$ where owner#=102 and name

like '%S_MT%');

已删除0行。

SQL> select eventid,name,table_objno from system.aq$_queues

where name like '%S_MT%' and eventid in(select obj# from sys.obj$

where owner#=102 and name like '%S_MT%');

未选定行

SQL> delete from sys.aq$_queue_table_affinities where

table_objno =511210;

已删除0行。

SQL> select table_objno from sys.aq$_queue_table_affinities

where table_objno in( select table_objno from system.aq$_queues

where name like '%S_MT%' and eventid in(select obj# from sys.obj$

where owner#=102 and name like '%S_MT%'));

未选定行

SQL>  select table_objno from

sys.aq$_queue_table_affinities;

TABLE_OBJNO

-----------

3803

3812

3861

3871

28464

28470

28493

28514

28532

28559

28577

TABLE_OBJNO

-----------

28604

28622

28649

28655

28679

467253

389940

389965

461527

462087

已选择21行。

SQL> select eventid,name,table_objno from system.aq$_queues

where name like '%S_MT%' and eventid in(select obj# from sys.obj$

where owner#=102 and name like '%S_MT%');

未选定行

SQL> commit;

提交完成。

SQL> select schema,name,objno from system.aq$_queue_tables

where schema='SGDD' and name like '%S_MT%';

SCHEMA  NAME  OBJNO

------------------------------ ------------------------------

----------

SGDD  SS_MT_TAB  511210

SQL> delete from system.aq$_queue_tables where

schema='SGDD' and name like '%S_MT%';

已删除 1 行。

SQL> commit;

提交完成。

SQL> drop user sgdd cascade;

drop user sgdd cascade

*

ERROR 位于第 1 行:

ORA-01940: 无法删除当前已连接的用户

SQL>

SQL>

SQL> drop user sgdd cascade;

用户已丢弃