oracle10.2.0.1.0中與all_objects有關的bug
IIRC this is a known bug. Just go back to 9i view definition or change
the all_objects view definition with /*+RULE */ hint.
K Gopalakrishnan
Co-Author: Oracle Wait Interface, Oracle Press 2004
Author: Oracle Database 10g RAC Handbook, Oracle Press 2006
can arrive by
> ## Test Env.
>
> ## MS Windows
>
> ## Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
Prod
>
> PL/SQL Release 10.2.0.1.0 - Production
>
> CORE 10.2.0.1.0 Production
>
> TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
>
> NLSRTL Version 10.2.0.1.0 - Production
>
>
>
>
>
> select distinct owner from sys.all_objects; consume 100%
CPU,hanging.
>
>
>
> If I run with Rule ,it returns the rows.
>
>
>
> Statistics is there on SYS tables.
>
>
>
> Explain plan show HASH JOIN between sys.user$ and sys.obj$ if it
runs
> without Rule hint.
>
--------------definition of all_objects---------------------------------------
select u.name, o.name, o.subname, o.obj#, o.dataobj#,
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, NVL((SELECT distinct 'REWRITE EQUIVALENCE'
FROM sum$ s
WHERE s.obj#=o.obj#
and bitand(s.xpflags, 8388608) = 8388608),
'MATERIALIZED VIEW'),
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
60, 'CAPTURE', 61, 'APPLY',
62, 'EVALUATION CONTEXT',
66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
81, 'FILE GROUP',
'UNDEFINED'),
o.ctime, o.mtime,
to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N')
from sys.obj$ o, sys.user$ u
where o.owner# = u.user#
and o.linkname is null
and (o.type# not in (1 /* INDEX - handled below */,
10 /* NON-EXISTENT */)
or
(o.type# = 1 and 1 = (select 1
from sys.ind$ i
where i.obj# = o.obj#
and i.type# in (1, 2, 3, 4, 6, 7, 9))))
and o.name != '_NEXT_OBJECT'
and o.name != '_default_auditing_options_'
and
(
o.owner# in (userenv('SCHEMAID'), 1 /* PUBLIC */)
or
(
/* EXECUTE privilege does not let user see package/type body */
o.type# != 11 and o.type# != 14
and
o.obj# in (select obj# from sys.objauth$
where grantee# in (select kzsrorol from x$kzsro)
and privilege# in (3 /* DELETE */, 6 /* INSERT */,
7 /* LOCK */, 9 /* SELECT */,
10 /* UPDATE */, 12 /* EXECUTE */,
11 /* USAGE */, 16 /* CREATE */,
17 /* READ */, 18 /* WRITE */ ))
)
or
(
o.type# in (7, 8, 9, 28, 29, 30, 56) /* prc, fcn, pkg */
and
exists (select null from v$enabledprivs
where priv_number in (
-144 /* EXECUTE ANY PROCEDURE */,
-141 /* CREATE ANY PROCEDURE */
)
)
)
or
(
o.type# in (12) /* trigger */
and
exists (select null from v$enabledprivs
where priv_number in (
-152 /* CREATE ANY TRIGGER */
)
)
)
or
(
o.type# = 11 /* pkg body */
and
exists (select null from v$enabledprivs
where priv_number = -141 /* CREATE ANY PROCEDURE */
)
)
or
(
o.type# in (22) /* library */
and
exists (select null from v$enabledprivs
where priv_number in (
-189 /* CREATE ANY LIBRARY */,
-190 /* ALTER ANY LIBRARY */,
-191 /* DROP ANY LIBRARY */,
-192 /* EXECUTE ANY LIBRARY */
)
)
)
or
(
/* index, table, view, synonym, table partn, indx partn, */
/* table subpartn, index subpartn, cluster */
o.type# in (1, 2, 3, 4, 5, 19, 20, 34, 35)
and
exists (select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */)
)
)
or
( o.type# = 6 /* sequence */
and
exists (select null from v$enabledprivs
where priv_number = -109 /* SELECT ANY SEQUENCE */)
)
or
( o.type# = 13 /* type */
and
exists (select null from v$enabledprivs
where priv_number in (-184 /* EXECUTE ANY TYPE */,
-181 /* CREATE ANY TYPE */))
)
or
(
o.type# = 14 /* type body */
and
exists (select null from v$enabledprivs
where priv_number = -181 /* CREATE ANY TYPE */)
)
or
(
o.type# = 23 /* directory */
and
exists (select null from v$enabledprivs
where priv_number in (
-177 /* CREATE ANY DIRECTORY */,
-178 /* DROP ANY DIRECTORY */
)
)
)
or
(
o.type# = 42 /* summary jjf table privs have to change to summary */
and
exists (select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */)
)
)
or
(
o.type# = 32 /* indextype */
and
exists (select null from v$enabledprivs
where priv_number in (
-205 /* CREATE INDEXTYPE */ ,
-206 /* CREATE ANY INDEXTYPE */ ,
-207 /* ALTER ANY INDEXTYPE */ ,
-208 /* DROP ANY INDEXTYPE */
)
)
)
or
(
o.type# = 33 /* operator */
and
exists (select null from v$enabledprivs
where priv_number in (
-200 /* CREATE OPERATOR */ ,
-201 /* CREATE ANY OPERATOR */ ,
-202 /* ALTER ANY OPERATOR */ ,
-203 /* DROP ANY OPERATOR */ ,
-204 /* EXECUTE OPERATOR */
)
)
)
or
(
o.type# = 44 /* context */
and
exists (select null from v$enabledprivs
where priv_number in (
-222 /* CREATE ANY CONTEXT */,
-223 /* DROP ANY CONTEXT */
)
)
)
or
(
o.type# = 48 /* resource consumer group */
and
exists (select null from v$enabledprivs
where priv_number in (12) /* switch consumer group privilege */
)
)
or
(
o.type# = 46 /* rule set */
and
exists (select null from v$enabledprivs
where priv_number in (
-251, /* create any rule set */
-252, /* alter any rule set */
-253, /* drop any rule set */
-254 /* execute any rule set */
)
)
)
or
(
o.type# = 55 /* XML schema */
and
1 = (select /*+ NO_MERGE */ xml_schema_name_present.is_schema_present(o.na
me, u2.id2) id1 from (select /*+ NO_MERGE */ userenv('SCHEMAID') id2 from dual)
u2)
/* we need a sub-query instead of the directy invoking
* xml_schema_name_present, because inside a view even the function
* arguments are evaluated as definers rights.
*/
)
or
(
o.type# = 59 /* rule */
and
exists (select null from v$enabledprivs
where priv_number in (
-258, /* create any rule */
-259, /* alter any rule */
-260, /* drop any rule */
-261 /* execute any rule */
)
)
)
or
(
o.type# = 62 /* evaluation context */
and
exists (select null from v$enabledprivs
where priv_number in (
-246, /* create any evaluation context */
-247, /* alter any evaluation context */
-248, /* drop any evaluation context */
-249 /* execute any evaluation context */
)
)
)
or
(
o.type# = 66 /* scheduler job */
and
exists (select null from v$enabledprivs
where priv_number = -265 /* create any job */
)
)
or
(
o.type# IN (67, 79) /* scheduler program or chain */
and
exists (select null from v$enabledprivs
where priv_number in (
-265, /* create any job */
-266 /* execute any program */
)
)
)
or
(
o.type# = 68 /* scheduler job class */
and
exists (select null from v$enabledprivs
where priv_number in (
-268, /* manage scheduler */
-267 /* execute any class */
)
)
)
or (o.type# in (69, 72, 74))
/* scheduler windows, window groups and schedules */
/* no privileges are needed to view these objects */
or
(
o.type# = 81 /* file group */
and
exists (select null from v$enabledprivs
where priv_number in (
-277, /* manage any file group */
-278 /* read any file group */
)
)
)
)
-------------------------end-----------------------------------------------
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/94384/viewspace-600270/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 查詢11G的資料字典試圖ALL_OBJECTS遇到BUGObject
- Spring中與bean有關的生命週期SpringBean
- Java面試中與原始碼有關的問題分享Java面試原始碼
- linux中與Oracle有關的核心引數詳解LinuxOracle
- ERP系統中與BOM有關的常用方法(轉)
- 關於VS中區分debug與release，32位與64位編譯的巨集定義編譯
- linux中與Oracle有關的核心引數詳解(zhuan)LinuxOracle
- [BUG反饋]AuthGroupModel的CheckId有BUG
- 有關字元的加密與解密 (轉)字元加密解密
- cgibin中與upnp協議有關的一些漏洞分析與復現協議
- git stash關於程式碼中bug的查詢使用Git
- Delphi中兩個BUG的分析與修復 (轉)
- 關於js中的‘==’ 與 ‘===’JS
- [BUG反饋]關於ot模型中的時間型別欄位bug問題模型型別
- Oracle dba_objects和all_objects 最大的區別OracleObject
- 關於Oracle中重啟資料庫的一個bugOracle資料庫
- 與控制檔案有關的恢復
- 與LINQ有關的語言特性
- PHP：與型別有關的函式PHP型別函式
- Bitmap的有關講解與優化優化
- 與系統管理有關的命令(轉)
- 軟體測試中的Bug迴歸，到底有多重要？
- [BUG反饋]關於設定選單的BUG
- create 與 store中的關係
- 有關於JAVA中的CLASSPATH的作用 (轉)Java
- 突然發現junit單元測試報錯竟然與類中的有參構造有關
- Android 開發中，與螢幕有關的三個小眾知識Android
- Java中Class類與Object類之間有什麼關係？JavaObject
- 與控制檔案有關的恢復(二)
- 與RabbitMQ有關的一些知識MQ
- 與CNAME有關的DNS解決例項DNS
- Linux與使用者有關的命令Linux
- 與效能優化有關的幾個程式優化
- 與控制檔案有關的恢復1
- 與控制檔案有關的恢復2
- [BUG反饋]兩個關於釋出文章的BUG
- 一個關於recyclerView的bugView
- 關於UIInterfaceOrientation的一個bugUI