[20130221]视图dict的问题.txt

[20130221]视图dict的问题.txt

1.测试环境

SQL> select * from v$version where rownum

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

2.问题引出:

SQL> column SUBOBJECT_NAME noprint

SQL> column edition_name noprint

SQL> select * from dba_objects where object_name like 'DBA_SCHEDULER_RUNNING_JOBS';

OWNER                OBJECT_NAME          OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE

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

SYS                  DBA_SCHEDULER_RUNNIN      9188                VIEW                2009-08-15 00:20:20 2009-08-15 00:20:20 2009-08-15:00:20:20 VALID   N N N          1

                     G_JOBS

PUBLIC               DBA_SCHEDULER_RUNNIN      9189                SYNONYM             2009-08-15 00:20:20 2009-08-15 00:20:20 2009-08-15:00:20:20 VALID   N N N          1

                     G_JOBS

但是如果查询字典:

SQL> select * from dict where table_name = 'DBA_SCHEDULER_RUNNING_JOBS';

no rows selected

--可以发现并不在dict视图中.why?看看视图定义:

CREATE OR REPLACE FORCE VIEW SYS.DICTIONARY (table_name, comments)

AS

   SELECT o.NAME, c.comment$

     FROM SYS.obj$ o, SYS.com$ c

    WHERE o.obj# = c.obj#(+)

      AND c.col# IS NULL

      AND o.owner# = 0

      AND o.type# = 4

      AND (   o.NAME LIKE 'USER%'

           OR o.NAME LIKE 'ALL%'

           OR (o.NAME LIKE 'DBA%' AND EXISTS (SELECT NULL

                                                FROM SYS.v$enabledprivs

                                               WHERE priv_number = -47 /* SELECT ANY TABLE */))

          )

   UNION ALL

   SELECT o.NAME, c.comment$

     FROM SYS.obj$ o, SYS.com$ c

    WHERE o.obj# = c.obj#(+)

      AND o.owner# = 0

      AND o.NAME IN

             ('AUDIT_ACTIONS', 'COLUMN_PRIVILEGES', 'DICTIONARY', 'DICT_COLUMNS', 'DUAL', 'GLOBAL_NAME', 'INDEX_HISTOGRAM',

              'INDEX_STATS', 'RESOURCE_COST', 'ROLE_ROLE_PRIVS', 'ROLE_SYS_PRIVS', 'ROLE_TAB_PRIVS', 'SESSION_PRIVS',

              'SESSION_ROLES', 'TABLE_PRIVILEGES', 'NLS_SESSION_PARAMETERS', 'NLS_INSTANCE_PARAMETERS',

              'NLS_DATABASE_PARAMETERS', 'DATABASE_COMPATIBLE_LEVEL', 'DBMS_ALERT_INFO', 'DBMS_LOCK_ALLOCATED')

      AND c.col# IS NULL

   UNION ALL

   SELECT so.NAME, 'Synonym for ' || sy.NAME

     FROM SYS.obj$ ro, SYS.syn$ sy, SYS.obj$ so

    WHERE so.type# = 5

      AND ro.linkname IS NULL

      AND so.owner# = 1

      AND so.obj# = sy.obj#

      AND so.NAME  sy.NAME

      AND sy.owner = 'SYS'

      AND sy.NAME = ro.NAME

      AND ro.owner# = 0

      AND ro.type# = 4

      AND (   ro.owner# = USERENV ('SCHEMAID')

           OR ro.obj# IN (SELECT oa.obj#

                            FROM SYS.objauth$ oa

                           WHERE grantee# IN (SELECT kzsrorol

                                                FROM x$kzsro))

           OR 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 */

                           ))

          );

--可以发现查询条件存在c.col# IS NULL条件.
--如果删除c.col# IS NULL查询(以sys用户查询).

SQL> column comment$ format a100

SELECT *

  FROM (SELECT o.NAME, c.comment$

          FROM SYS.obj$ o, SYS.com$ c

         WHERE o.obj# = c.obj#(+)

           --AND c.col# IS NULL

           AND o.owner# = 0

           AND o.type# = 4

           AND (   o.NAME LIKE 'USER%'

                OR o.NAME LIKE 'ALL%'

                OR (o.NAME LIKE 'DBA%' AND EXISTS (SELECT NULL

                                                     FROM SYS.v$enabledprivs

                                                    WHERE priv_number = -47 /* SELECT ANY TABLE */))

               ))

 WHERE NAME = 'DBA_SCHEDULER_RUNNING_JOBS';

NAME                         COMMENT$

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

DBA_SCHEDULER_RUNNING_JOBS   Owner of the running scheduler job

DBA_SCHEDULER_RUNNING_JOBS   Name of the running scheduler job

DBA_SCHEDULER_RUNNING_JOBS   Subname of the running scheduler job (for a job running a chain step)

DBA_SCHEDULER_RUNNING_JOBS   Job style. - regular, lightweight or volatile

DBA_SCHEDULER_RUNNING_JOBS   Process number of the slave process running the scheduler job

DBA_SCHEDULER_RUNNING_JOBS   Operating system process number of the slave process running the scheduler job

DBA_SCHEDULER_RUNNING_JOBS   Database instance number of the slave process running the scheduler job

DBA_SCHEDULER_RUNNING_JOBS   Resource consumer group of the session in which the scheduler job is running

DBA_SCHEDULER_RUNNING_JOBS   Time elapsed since the scheduler job started

DBA_SCHEDULER_RUNNING_JOBS   CPU time used by the running scheduler job, if available

DBA_SCHEDULER_RUNNING_JOBS   Owner of destination object (if used) else NULL

DBA_SCHEDULER_RUNNING_JOBS   Destination that this job is running on

DBA_SCHEDULER_RUNNING_JOBS   Owner of login credential used for this running job, if any

DBA_SCHEDULER_RUNNING_JOBS   Name of login credential used for this running job, if any

14 rows selected.

--很奇怪sys.com$中不存在该对象col#为空的的记录:

SQL> SELECT * FROM SYS.com$ WHERE obj# IN (SELECT obj# FROM SYS.obj$ WHERE NAME = 'DBA_SCHEDULER_RUNNING_JOBS');

      OBJ#       COL# COMMENT$

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

      9188          1 Owner of the running scheduler job

      9188          2 Name of the running scheduler job

      9188          3 Subname of the running scheduler job (for a job running a chain step)

      9188          4 Job style. - regular, lightweight or volatile

      9188          7 Process number of the slave process running the scheduler job

      9188          8 Operating system process number of the slave process running the scheduler job

      9188          9 Database instance number of the slave process running the scheduler job

      9188         10 Resource consumer group of the session in which the scheduler job is running

      9188         11 Time elapsed since the scheduler job started

      9188         12 CPU time used by the running scheduler job, if available

      9188         13 Owner of destination object (if used) else NULL

      9188         14 Destination that this job is running on

      9188         15 Owner of login credential used for this running job, if any

      9188         16 Name of login credential used for this running job, if any

14 rows selected.

3.自己建立一个视图看看.

SQL> create view v_dept as select * from dept ;

View created.

SQL> SELECT * FROM SYS.com$ WHERE obj# IN (SELECT obj# FROM SYS.obj$ WHERE NAME = 'V_DEPT');

no rows selected

SQL> COMMENT ON TABLE SCOTT.V_DEPT IS 'this is a test!';

Comment created.

SQL> SELECT * FROM SYS.com$ WHERE obj# IN (SELECT obj# FROM SYS.obj$ WHERE NAME = 'V_DEPT');

      OBJ#       COL# COMMENT$

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

    269361            this is a test!

--可以发现表或者视图的注解对应的col#为NULL.

SQL> COMMENT ON TABLE SCOTT.V_DEPT IS '';

Comment created.

SQL> SELECT * FROM SYS.com$ WHERE obj# IN (SELECT obj# FROM SYS.obj$ WHERE NAME = 'V_DEPT');

      OBJ#       COL# COMMENT$

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

    269361

SQL> COMMENT ON TABLE SCOTT.V_DEPT IS null;

COMMENT ON TABLE SCOTT.V_DEPT IS null

                                 *

ERROR at line 1:

ORA-01780: string literal required

--另外可以发现一旦建立注解,无法删除,仅仅为空.而且即使是视图,注解的对象是view,命令也是COMMENT ON TABLE,有点奇怪!

--给SYS.DBA_SCHEDULER_RUNNING_JOBS

SQL> COMMENT ON TABLE SYS.DBA_SCHEDULER_RUNNING_JOBS IS 'SCHEDULER RUNNING JOBS';

Comment created.

SQL> select * from dict where TABLE_NAME='DBA_SCHEDULER_RUNNING_JOBS';

TABLE_NAME                     COMMENTS

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

DBA_SCHEDULER_RUNNING_JOBS     SCHEDULER RUNNING JOBS

-- 这样查询就存在了.

4.这样就有许多视图不存在dict中.

SELECT *

  FROM dba_objects

 WHERE object_id IN (

 SELECT DISTINCT obj# FROM SYS.com$ WHERE col# IS NOT NULL

 MINUS

 SELECT obj# FROM SYS.com$ WHERE col# IS NULL);

--这样这些对象在dict中都应该无法查询.