Host01% sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on 星期五 4月 24 12:05:02 2015

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn kxzcgl/kxzcgl
已连接。
SQL> set line 132
SQL> set wrap off
SQL> select object_type,object_name from user_objects where object_name like '%MT_TAB%';^C

SQL> col object_name for a20
SQL> col object_type for a10
SQL> select object_type,object_name from user_objects where object_name like '%MT_TAB%';

OBJECT_TYP OBJECT_NAME
---------- --------------------
VIEW       AQ$SS_MT_TAB_R
VIEW       AQ$_SS_MT_TAB_F
EVALUATION AQ$_SS_MT_TAB_V
TABLE      SS_MT_TAB

SQL> drop table SS_MT_TAB cascade constraints;
drop table SS_MT_TAB cascade constraints
           *
ERROR 位于第 1 行:
ORA-24005: 必须使用 DBMS_AQADM.DROP_QUEUE_TABLE 删除队列表

SQL> exec dbms_aqadm.drop_queue_table('ss_mt_tab');
BEGIN dbms_aqadm.drop_queue_table('ss_mt_tab'); END;

*
ERROR 位于第 1 行:
ORA-24002: QUEUE_TABLE WYZC.SS_MT_TAB 不存在
ORA-06512: 在"SYS.DBMS_AQADM_SYS", line 2973
ORA-06512: 在"SYS.DBMS_AQADM", line 197
ORA-06512: 在line 1

SQL> begin dbms_aqadm.create_queue_table(queue_table=>'ss_mt_tab', queue_payload_type=>'SYS.AQ$_JMS_TEXT_MESSAGE',multiple_consumers => true); end;
  2  /
begin dbms_aqadm.create_queue_table(queue_table=>'ss_mt_tab', queue_payload_type=>'SYS.AQ$_JMS_TEXT_MESSAGE',multiple_consumers => true); end;
*
ERROR 位于第 1 行:
ORA-24006: 无法创建 QUEUE，WYZC.AQ$_SS_MT_TAB_E已经存在
ORA-06512: 在"SYS.DBMS_AQADM_SYS", line 2224
ORA-06512: 在"SYS.DBMS_AQADM", line 58
ORA-06512: 在line 1

SQL> !oerr ora 24006
24006, 00000, "cannot create QUEUE, %s already exists"
// *Cause:  The queue requested to be created already exists.
// *Action: Specify another queue name.  Query USER_QUEUES for all the exisiting
//          queues in the users's schema.
//

 

SQL> exec dbms_aqadm.drop_queue(queue_name => 'AQ$_SS_MT_TAB_E');
BEGIN dbms_aqadm.drop_queue(queue_name => 'AQ$_SS_MT_TAB_E'); END;

*
ERROR 位于第 1 行:
ORA-24010: QUEUE WYZC.AQ$_SS_MT_TAB_E不存在
ORA-06512: 在"SYS.DBMS_AQADM_SYS", line 2747
ORA-06512: 在"SYS.DBMS_AQADM", line 167
ORA-06512: 在line 1

SQL> alter   session   set   events'10851   trace   name   context   forever,level   1';

会话已更改。

SQL> drop table SS_MT_TAB cascade constraints;

表已丢弃。

SQL> select object_type,object_name from user_objects where object_name like '%MT_TAB%';

OBJECT_TYP OBJECT_NAME
---------- --------------------
VIEW       AQ$SS_MT_TAB_R
VIEW       AQ$_SS_MT_TAB_F
EVALUATION AQ$_SS_MT_TAB_V

SQL> drop view AQ$SS_MT_TAB_R cascade constraints;

视图已丢掉。

SQL> drop view AQ$_SS_MT_TAB_F cascade constraints;

视图已丢掉。

SQL> select object_type,object_name from user_objects where object_name like '%MT_TAB%';

OBJECT_TYP OBJECT_NAME
---------- --------------------
EVALUATION AQ$_SS_MT_TAB_V

SQL> select schema,name,objno from system.aq$_queue_tables where name like '%SMS_MT_QUEUE%';

未选定行

SQL> select obj#,owner#,name,type# from obj$ where name like '%SMS_MT_QUEUE%';
select obj#,owner#,name,type# from obj$ where name like '%SMS_MT_QUEUE%'
                                   *
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在

SQL> select obj#,owner#,name,type# from sys.obj$ where name like '%SMS_MT_QUEUE%';

      OBJ#     OWNER# NAME                                TYPE#
---------- ---------- ------------------------------ ----------
    389963         78 SMS_MT_QUEUE_R                         46
    389962         78 SMS_MT_QUEUE                           24
    461550         83 SMS_MT_QUEUE_R                         46
    461549         83 SMS_MT_QUEUE                           24
    462498         86 SMS_MT_QUEUE_N                         46
    462110         86 SMS_MT_QUEUE_R                         46
    462109         86 SMS_MT_QUEUE                           24
    466447         97 SMS_MT_QUEUE_R                         46

已选择8行。

SQL> select obj#,owner#,name,type# from sys.obj$ where name like '%AQ$_SS_MT_TAB%';

      OBJ#     OWNER# NAME                                TYPE#
---------- ---------- ------------------------------ ----------
    389946         78 AQ$_SS_MT_TAB_S                         2
    389950         78 AQ$_SS_MT_TAB_V                        62
    389951         78 AQ$_SS_MT_TAB_T                         2
    389948         78 AQ$_SS_MT_TAB_N                         6
    389953         78 AQ$_SS_MT_TAB_H                         2
    389955         78 AQ$_SS_MT_TAB_NR                        2
    389958         78 AQ$_SS_MT_TAB_I                         2
    389960         78 AQ$_SS_MT_TAB_E                        24
    461533         83 AQ$_SS_MT_TAB_S                         2
    461537         83 AQ$_SS_MT_TAB_V                        62
    461538         83 AQ$_SS_MT_TAB_T                         2

      OBJ#     OWNER# NAME                                TYPE#
---------- ---------- ------------------------------ ----------
    461535         83 AQ$_SS_MT_TAB_N                         6
    461540         83 AQ$_SS_MT_TAB_H                         2
    461542         83 AQ$_SS_MT_TAB_NR                        2
    461545         83 AQ$_SS_MT_TAB_I                         2
    461547         83 AQ$_SS_MT_TAB_E                        24
    462382         86 AQ$_SS_MT_TAB_F                         4
    462093         86 AQ$_SS_MT_TAB_S                         2
    462097         86 AQ$_SS_MT_TAB_V                        62
    462098         86 AQ$_SS_MT_TAB_T                         2
    462095         86 AQ$_SS_MT_TAB_N                         6
    462100         86 AQ$_SS_MT_TAB_H                         2

      OBJ#     OWNER# NAME                                TYPE#
---------- ---------- ------------------------------ ----------
    462102         86 AQ$_SS_MT_TAB_NR                        2
    462105         86 AQ$_SS_MT_TAB_I                         2
    462107         86 AQ$_SS_MT_TAB_E                        24
    462132         86 AQ$_SS_MT_TAB_G                         2
    466446         97 AQ$_SS_MT_TAB_V                        62
    466974         97 AQ$_SS_MT_TAB_S                        10
    466975          1 AQ$_SS_MT_TAB_S                        10

已选择29行。

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='KXZCGL';

   USER_ID
----------
        97

SQL> select name,table_objno from system.aq$_queues where name like '%AQ$_SS_MT_TAB_%';

NAME                           TABLE_OBJNO
------------------------------ -----------
AQ$_SS_MT_TAB_E                     389940
AQ$_SS_MT_TAB_E                     461527
AQ$_SS_MT_TAB_E                     462087

SQL> desc system.aq$_queues;
 名称                                                                     是否为空? 类型
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 OID                                                                      NOT NULL RAW(16)
 EVENTID                                                                  NOT NULL NUMBER
 NAME                                                                     NOT NULL VARCHAR2(30)
 TABLE_OBJNO                                                              NOT NULL NUMBER
 USAGE                                                                    NOT NULL NUMBER
 ENABLE_FLAG                                                              NOT NULL NUMBER
 MAX_RETRIES                                                                       NUMBER
 RETRY_DELAY                                                                       NUMBER
 PROPERTIES                                                                        NUMBER
 RET_TIME                                                                          NUMBER
 QUEUE_COMMENT                                                                     VARCHAR2(2000)
 SUBSCRIBERS                                                                       SYS.AQ$_SUBSCRIBERS
 MEMORY_THRESHOLD                                                                  NUMBER

 

SQL> select obj#,owner#,name,type# from sys.obj$ where name like '%AQ$_SS_MT_TAB%' and owner#=97
  2  ;

      OBJ#     OWNER# NAME                                TYPE#
---------- ---------- ------------------------------ ----------
    466974         97 AQ$_SS_MT_TAB_S                        10
    466446         97 AQ$_SS_MT_TAB_V                        62

SQL> select schema,name,objno from system.aq$_queue_tables where name like '%SMS_MT_QUEUE%';

未选定行

 

SQL> select obj#,owner#,name,type# from sys.obj$ where name like '%AQ$_SS_MT_TAB%' and owner#=97;           

      OBJ#     OWNER# NAME                                TYPE#
---------- ---------- ------------------------------ ----------
    466974         97 AQ$_SS_MT_TAB_S                        10
    466446         97 AQ$_SS_MT_TAB_V                        62

SQL> select name,table_objno from system.aq$_queues where name like '%AQ$_SS_MT_TAB_%';

NAME                           TABLE_OBJNO
------------------------------ -----------
AQ$_SS_MT_TAB_E                     389940
AQ$_SS_MT_TAB_E                     461527
AQ$_SS_MT_TAB_E                     462087

SQL> delete from sys.obj$ where name like '%AQ$_SS_MT_TAB%' and owner#=97;
delete from sys.obj$ where name like '%AQ$_SS_MT_TAB%' and owner#=97
                *
ERROR 位于第 1 行:
ORA-01031: 权限不足

SQL> conn /as sysdba
已连接。
SQL> delete from sys.obj$ where name like '%AQ$_SS_MT_TAB%' and owner#=97;

已删除2行。

SQL> commit;

提交完成。

SQL> select obj#,owner#,name,type# from sys.obj$ where name like '%AQ$_SS_MT_TAB%' and owner#=97;

未选定行

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 2233439448 bytes
Fixed Size                   734424 bytes
Variable Size             620756992 bytes
Database Buffers         1610612736 bytes
Redo Buffers                1335296 bytes
数据库装载完毕。
数据库已经打开。
SQL> conn kxzcgl/kxzcgl
已连接。
SQL> select object_type,object_name from user_objects where object_name like '%MT_TAB%';

未选定行

SQL> select schema,name,objno from system.aq$_queue_tables where name like '%SMS_MT_QUEUE%';

未选定行

SQL> select obj#,owner#,name,type# from sys.obj$ where name like '%AQ$_SS_MT_TAB%' and owner#=97;

未选定行

SQL> select name,table_objno from system.aq$_queues where name like '%AQ$_SS_MT_TAB_%';

NAME                           TABLE_OBJNO
------------------------------ -----------
AQ$_SS_MT_TAB_E                     389940
AQ$_SS_MT_TAB_E                     461527
AQ$_SS_MT_TAB_E                     462087

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
     389940
     389965
     461527
     462087
     466439

已选择21行。

SQL> select obj#,owner#,name,type# from sys.obj$ where name like '%AQ$_SS_MT_TAB%';

      OBJ#     OWNER# NAME                                TYPE#
---------- ---------- ------------------------------ ----------
    389946         78 AQ$_SS_MT_TAB_S                         2
    389950         78 AQ$_SS_MT_TAB_V                        62
    389951         78 AQ$_SS_MT_TAB_T                         2
    389948         78 AQ$_SS_MT_TAB_N                         6
    389953         78 AQ$_SS_MT_TAB_H                         2
    389955         78 AQ$_SS_MT_TAB_NR                        2
    389958         78 AQ$_SS_MT_TAB_I                         2
    389960         78 AQ$_SS_MT_TAB_E                        24
    461533         83 AQ$_SS_MT_TAB_S                         2
    461537         83 AQ$_SS_MT_TAB_V                        62
    461538         83 AQ$_SS_MT_TAB_T                         2

      OBJ#     OWNER# NAME                                TYPE#
---------- ---------- ------------------------------ ----------
    461535         83 AQ$_SS_MT_TAB_N                         6
    461540         83 AQ$_SS_MT_TAB_H                         2
    461542         83 AQ$_SS_MT_TAB_NR                        2
    461545         83 AQ$_SS_MT_TAB_I                         2
    461547         83 AQ$_SS_MT_TAB_E                        24
    462382         86 AQ$_SS_MT_TAB_F                         4
    462093         86 AQ$_SS_MT_TAB_S                         2
    462097         86 AQ$_SS_MT_TAB_V                        62
    462098         86 AQ$_SS_MT_TAB_T                         2
    462095         86 AQ$_SS_MT_TAB_N                         6
    462100         86 AQ$_SS_MT_TAB_H                         2

      OBJ#     OWNER# NAME                                TYPE#
---------- ---------- ------------------------------ ----------
    462102         86 AQ$_SS_MT_TAB_NR                        2
    462105         86 AQ$_SS_MT_TAB_I                         2
    462107         86 AQ$_SS_MT_TAB_E                        24
    462132         86 AQ$_SS_MT_TAB_G                         2

已选择26行。

SQL> desc sys.aq$_queue_table_affinities;
 名称                                                                     是否为空? 类型
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 TABLE_OBJNO                                                              NOT NULL NUMBER
 PRIMARY_INSTANCE                                                         NOT NULL NUMBER
 SECONDARY_INSTANCE                                                       NOT NULL NUMBER
 OWNER_INSTANCE                                                           NOT NULL NUMBER

SQL> select * from sys.aq$_queue_table_affinities;

TABLE_OBJNO PRIMARY_INSTANCE SECONDARY_INSTANCE OWNER_INSTANCE
----------- ---------------- ------------------ --------------
       3803                0                  0              1
       3812                0                  0              1
       3861                0                  0              1
       3871                0                  0              1
      28464                1                  2              1
      28470                2                  1              1
      28493                0                  0              1
      28514                0                  0              1
      28532                0                  0              1
      28559                0                  0              1
      28577                0                  0              1

TABLE_OBJNO PRIMARY_INSTANCE SECONDARY_INSTANCE OWNER_INSTANCE
----------- ---------------- ------------------ --------------
      28604                0                  0              1
      28622                0                  0              1
      28649                0                  0              1
      28655                0                  0              1
      28679                0                  0              1
     389940                0                  0              1
     389965                1                  0              1
     461527                0                  0              1
     462087                0                  0              1
     466439                0                  0              1

已选择21行。

SQL> desc sys.obj$;
 名称                                                                     是否为空? 类型
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 OBJ#                                                                     NOT NULL NUMBER
 DATAOBJ#                                                                          NUMBER
 OWNER#                                                                   NOT NULL NUMBER
 NAME                                                                     NOT NULL VARCHAR2(30)
 NAMESPACE                                                                NOT NULL NUMBER
 SUBNAME                                                                           VARCHAR2(30)
 TYPE#                                                                    NOT NULL NUMBER
 CTIME                                                                    NOT NULL DATE
 MTIME                                                                    NOT NULL DATE
 STIME                                                                    NOT NULL DATE
 STATUS                                                                   NOT NULL NUMBER
 REMOTEOWNER                                                                       VARCHAR2(30)
 LINKNAME                                                                          VARCHAR2(128)
 FLAGS                                                                             NUMBER
 OID$                                                                              RAW(16)
 SPARE1                                                                            NUMBER
 SPARE2                                                                            NUMBER
 SPARE3                                                                            NUMBER
 SPARE4                                                                            VARCHAR2(1000)
 SPARE5                                                                            VARCHAR2(1000)
 SPARE6                                                                            DATE

SQL> select name,owner#,dataobj#,obj# from sys.obj$ where obj#=466439;

未选定行

SQL> select name,owner#,dataobj#,obj# from sys.obj$ where dataobj#=466439;

未选定行

SQL> conn /as sysdba
已连接。
SQL> delete from sys.aq$_queue_table_affinities where TABLE_OBJNO=466439;

已删除 1 行。

SQL> commit;

提交完成。

SQL> select * from sys.aq$_queue_table_affinities;

TABLE_OBJNO PRIMARY_INSTANCE SECONDARY_INSTANCE OWNER_INSTANCE
----------- ---------------- ------------------ --------------
       3803                0                  0              1
       3812                0                  0              1
       3861                0                  0              1
       3871                0                  0              1
      28464                1                  2              1
      28470                2                  1              1
      28493                0                  0              1
      28514                0                  0              1
      28532                0                  0              1
      28559                0                  0              1
      28577                0                  0              1

TABLE_OBJNO PRIMARY_INSTANCE SECONDARY_INSTANCE OWNER_INSTANCE
----------- ---------------- ------------------ --------------
      28604                0                  0              1
      28622                0                  0              1
      28649                0                  0              1
      28655                0                  0              1
      28679                0                  0              1
     389940                0                  0              1
     389965                1                  0              1
     461527                0                  0              1
     462087                0                  0              1

已选择20行。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 2233439448 bytes
Fixed Size                   734424 bytes
Variable Size             620756992 bytes
Database Buffers         1610612736 bytes
Redo Buffers                1335296 bytes
数据库装载完毕。
数据库已经打开。
SQL> select obj#,owner#,name,type# from sys.obj$ where name like '%AQ$_SS_MT_TAB%' and owner#=97;

未选定行

SQL> select name,table_objno from system.aq$_queues where name like '%AQ$_SS_MT_TAB_%';

NAME                           TABLE_OBJNO
------------------------------ -----------
AQ$_SS_MT_TAB_E                     389940
AQ$_SS_MT_TAB_E                     461527
AQ$_SS_MT_TAB_E                     462087

SQL> select schema,name,objno from system.aq$_queue_tables where name like '%SMS_MT_QUEUE%';

未选定行

SQL> select * from sys.aq$_queue_table_affinities;

TABLE_OBJNO PRIMARY_INSTANCE SECONDARY_INSTANCE OWNER_INSTANCE
----------- ---------------- ------------------ --------------
       3803                0                  0              1
       3812                0                  0              1
       3861                0                  0              1
       3871                0                  0              1
      28464                1                  2              1
      28470                2                  1              1
      28493                0                  0              1
      28514                0                  0              1
      28532                0                  0              1
      28559                0                  0              1
      28577                0                  0              1

TABLE_OBJNO PRIMARY_INSTANCE SECONDARY_INSTANCE OWNER_INSTANCE
----------- ---------------- ------------------ --------------
      28604                0                  0              1
      28622                0                  0              1
      28649                0                  0              1
      28655                0                  0              1
      28679                0                  0              1
     389940                0                  0              1
     389965                1                  0              1
     461527                0                  0              1
     462087                0                  0              1

已选择20行。

SQL> conn kxzcgl/kxzcgl                                                                
已连接。
SQL> select object_type,object_name from user_objects where object_name like '%MT_TAB%';

未选定行

SQL> exit