Oracle dba_objects和all_objects 最大的區別

maohaiqing0304發表於2015-08-14
OracleObject

連結：http://blog.itpub.net/28602568/viewspace-1771547/

標題： Oracle dba_objects和all_objects 最大的區別 

作者：ｌōττéｒｙ©版權所有[文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任.]

ALL_OBJECTS      describes all objects accessible to the current user.    描述當前使用者有訪問許可權的所有物件 
DBA_OBJECTS     describes all objects in the database.                          描述了資料庫中的所有物件 
USER_OBJECTS    describes all objects owned by the current user.        描述了當前使用者所擁有的所有物件

千萬不要覺得all_*和dba_*檢視都是所有物件的意思， all_*和許可權有關；
所以出現【不同使用者訪問all_objects檢視，相同過濾條件，結果不同 (例如："A使用者訪問all_objects檢視過濾B.T1表有資料，而C使用者也訪問all_objects檢視過濾B.T1表卻沒有資料" ）】是正常的，是因為C使用者沒有訪問B.T1表許可權，用dba_objects可以解決；
所以適當的選擇 dba_*和all_*檢視。

如下部分為2個檢視的建立語句：

ALL_OBJECTS 檢視建立語句:    ** 重點檢視各個object_type的privs部分
獲取建立語句命令: SELECT VIEW_NAME,TEXT FROM DBA_VIEWS WHERE VIEW_NAME=upper('ALL_OBJECTS');
語句如下：
CREATE OR REPLACE VIEW ALL_OBJECTS
(owner, object_name, subobject_name , object_id, data_object_id, object_type, created , last_ddl_time, timestamp, status , temporary, generated, secondary , namespace, edition_name)
AS
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 '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, 'EDITION' , 59, 'RULE',
                      60, 'CAPTURE' , 61, 'APPLY',
                      62, 'EVALUATION CONTEXT' ,
                      66, 'JOB' , 67, 'PROGRAM', 68 , 'JOB CLASS', 69, 'WINDOW',
                      72, 'SCHEDULER GROUP' , 74, 'SCHEDULE', 79 , 'CHAIN',
                      81, 'FILE GROUP' , 82, 'MINING MODEL', 87 , 'ASSEMBLY',
                      90, 'CREDENTIAL' , 92, 'CUBE DIMENSION', 93 , 'CUBE',
                      94, 'MEASURE FOLDER' , 95, 'CUBE BUILD PROCESS',
                      100, 'FILE WATCHER' , 101, 'DESTINATION',
                     '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'),
       o.namespace,
       o.defining_edition
from sys."_CURRENT_EDITION_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 bitand(o.flags , 128) = 0
  and
  (
    o.owner# in (userenv( 'SCHEMAID')/*當前使用者*/, 1 /* PUBLIC */)
    or
    (
      /* non-procedural objects */
      o.type# not in ( 7, 8 , 9, 11, 12 , 13, 14, 28 , 29, 30, 56 , 93)
      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 sys.objauth$ oa
                  where oa.obj# = o.obj#
                    and oa.grantee# in ( select kzsrorol from x$kzsro)
                    and oa.privilege# in ( 12 /* EXECUTE */ , 26 /* DEBUG */))
         or
         exists (select null from v$enabledprivs
                 where priv_number in (
                                        -144 /* EXECUTE ANY PROCEDURE */,
                                        -141 /* CREATE ANY PROCEDURE */ ,
                                        -241 /* DEBUG ANY PROCEDURE */
                                      )
                )
       )
    )
    or
    (
       o.type# in (19) /* partitioned table objects */
       and
       exists (select bo# from tabpart$ where obj# = o.obj# and
               bo# in   (select obj# from sys.objauth$
                where grantee# in ( select kzsrorol from x$kzsro)
                  and privilege# in ( 9 /* SELECT */ ))
              )
    )
    or
    (
       o.type# in (12) /* trigger */
       and
       (
         exists (select null from sys.trigger$ t, sys.objauth$ oa
                  where bitand (t.property, 24) = 0
                    and t.obj# = o.obj#
                    and oa.obj# = t.baseobject
                    and oa.grantee# in ( select kzsrorol from x$kzsro)
                    and oa.privilege# = 26 /* DEBUG */)
         or
         exists (select null from v$enabledprivs
                 where priv_number in (
                                        -152 /* CREATE ANY TRIGGER */ ,
                                        -241 /* DEBUG ANY PROCEDURE */
                                      )
              )
       )
    )
    or
    (
       o.type# = 11 /* pkg body */
       and
       (
         exists (select null
                   from sys."_ACTUAL_EDITION_OBJ" specobj, sys.dependency$ dep ,
                        sys.objauth$ oa
                  where specobj.owner# = o.owner#
                    and specobj.name = o.name
                    and specobj.type# = 9 /* pkg */
                    and dep.d_obj# = o.obj# and dep.p_obj# = specobj.obj#
                    and oa.obj# = specobj.obj#
                    and oa.grantee# in ( select kzsrorol from x$kzsro)
                    and oa.privilege# = 26 /* DEBUG */)
         or
         exists (select null from v$enabledprivs
                 where priv_number in (
                                        -141 /* CREATE ANY PROCEDURE */ ,
                                        -241 /* DEBUG 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 sys.objauth$ oa
                 where oa.obj# = o.obj#
                   and oa.grantee# in ( select kzsrorol from x$kzsro)
                   and oa.privilege# in ( 12 /* EXECUTE */ , 26 /* DEBUG */))
        or
        exists (select null from v$enabledprivs
                where priv_number in (- 184 /* EXECUTE ANY TYPE */ ,
                                      - 181 /* CREATE ANY TYPE */ ,
                                      - 241 /* DEBUG ANY PROCEDURE */ ))
      )
    )
    or
    (
      o.type# = 14 /* type body */
      and
      (
        exists (select null
                  from sys."_ACTUAL_EDITION_OBJ" specobj, sys.dependency$ dep ,
                       sys.objauth$ oa
                 where specobj.owner# = o.owner#
                   and specobj.name = o.name
                   and specobj.type# = 13 /* type */
                   and dep.d_obj# = o.obj# and dep.p_obj# = specobj.obj#
                   and oa.obj# = specobj.obj#
                   and oa.grantee# in ( select kzsrorol from x$kzsro)
                   and oa.privilege# = 26 /* DEBUG */)
        or
        exists (select null from v$enabledprivs
                where priv_number in (
                                       - 181 /* CREATE ANY TYPE */ ,
                                       - 241 /* DEBUG ANY PROCEDURE */
                                     )
               )
      )
    )
    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.name, 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# IN (66, 100)  /* scheduler job or file watcher */
      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, 101))
    /* scheduler windows, scheduler groups, schedules and destinations */
    /* 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 */
                                    )
             )
    )
    or
    (
      o.type# = 57 /* edition */
    )
    or
    (
      o.type# = 82 /* mining model */
      and
      exists (select null from v$enabledprivs
               where priv_number in (
                                       - 292, /* drop any mining model */
                                       - 293, /* select any mining model */
                                       - 294  /* alter any mining model */
                                    )
             )
    )
    or
    (
       o.type# in (87) /* assembly */
       and
       exists (select null from v$enabledprivs
               where priv_number in (
                                      - 282 /* CREATE ANY ASSEMBLY */ ,
                                      - 283 /* ALTER ANY ASSEMBLY */ ,
                                      - 284 /* DROP ANY ASSEMBLY */ ,
                                      - 285 /* EXECUTE ANY ASSEMBLY */
                                    )
              )
    )
    or
    (
      o.type# = 92 /* cube dimension */
      and
      exists (select null from v$enabledprivs
              where priv_number in (
                                      - 302, /* ALTER ANY PRIMARY DIMENSION */
                                      - 303, /* CREATE ANY PRIMARY DIMENSION */
                                      - 304, /* DELETE ANY PRIMARY DIMENSION */
                                      - 305, /* DROP ANY PRIMARY DIMENSION */
                                      - 306, /* INSERT ANY PRIMARY DIMENSION */
                                      - 307  /* SELECT ANY PRIMARY DIMENSION */
                                   )
             )
    )
    or
    (
      o.type# = 93 /* cube */
      and
      ( o.obj# in
            ( select obj#   /* directly granted privileges */
              from sys.objauth$
              where grantee# in ( select kzsrorol from x$kzsro )
            )
       or
       (
        exists (select null from v$enabledprivs
                where priv_number in (
                                        -309, /* ALTER ANY CUBE */
                                        -310, /* CREATE ANY CUBE */
                                        -311, /* DROP ANY CUBE */
                                        -312, /* SELECT ANY CUBE */
                                        -313  /* UPDATE ANY CUBE */
                                     )
               )
       )
      )
      and  /* require access to all Dimensions of the Cube */
      ( 1 = ( SELECT decode( have_all_dim_access, null , 1, have_all_dim_access)
              FROM
                ( SELECT
                    obj#,
                    MIN(have_dim_access ) have_all_dim_access
                  FROM
                    ( SELECT
                        c.obj# obj# ,
                        ( CASE
                          WHEN
                            ( do.owner# in ( userenv( 'SCHEMAID'), 1 )  /* public objects */
                              or do.obj# in
                              ( select obj#   /* directly granted privileges */
                                from sys.objauth$
                                where grantee# in ( select kzsrorol from x$kzsro )
                              )
                              or  /* user has system privileges */
                              ( exists ( select null from v$enabledprivs
                                         where priv_number in (
                                                                 -302, /* ALTER ANY PRIMARY DIMENSION */
                                                                 -303, /* CREATE ANY PRIMARY DIMENSION */
                                                                 -304, /* DELETE ANY PRIMARY DIMENSION */
                                                                 -305, /* DROP ANY PRIMARY DIMENSION */
                                                                 -306, /* INSERT ANY PRIMARY DIMENSION */
                                                                 -307  /* SELECT ANY PRIMARY DIMENSION */
                                                              )
                                       )
                              )
                            )
                          THEN 1
                          ELSE 0
                          END ) have_dim_access
                      FROM
                        olap_cubes$ c ,
                        dependency$ d ,
                        obj$ do
                      WHERE
                        do.obj# = d.p_obj#
                        AND do.type# = 92 /* CUBE DIMENSION */
                        AND c.obj# = d.d_obj#
                    )
                  GROUP BY obj# ) da
              WHERE
                o.obj#=da.obj# (+)
            )
      )
    )
    or
    (
      o.type# = 94 /* measure folder */
      and
      exists (select null from v$enabledprivs
              where priv_number in (
                                      - 315, /* CREATE ANY MEASURE FOLDER */
                                      - 316, /* DELETE ANY MEASURE FOLDER */
                                      - 317, /* DROP ANY MEASURE FOLDER */
                                      - 318  /* INSERT ANY MEASURE FOLDER */
                                   )
             )
    )
    or
    (
      o.type# = 95 /* cube build process */
      and
      exists (select null from v$enabledprivs
              where priv_number in (
                                      - 320, /* CREATE ANY BUILD PROCESS */
                                      - 321, /* DROP ANY BUILD PROCESS */
                                      - 322  /* UPDATE ANY BUILD PROCESS */
                                   )
             )
    )
  );
comment on table ALL_OBJECTS is 'Objects accessible to the user';  -->使用者可訪問的物件
comment on column ALL_OBJECTS.OWNER is 'Username of the owner of the object';
comment on column ALL_OBJECTS.OBJECT_NAME is 'Name of the object';
comment on column ALL_OBJECTS.SUBOBJECT_NAME is 'Name of the sub-object (for example, partititon)';
comment on column ALL_OBJECTS.OBJECT_ID is 'Object number of the object';
comment on column ALL_OBJECTS.DATA_OBJECT_ID is 'Object number of the segment which contains the object';
comment on column ALL_OBJECTS.OBJECT_TYPE is 'Type of the object';
comment on column ALL_OBJECTS.CREATED is 'Timestamp for the creation of the object';
comment on column ALL_OBJECTS.LAST_DDL_TIME is 'Timestamp for the last DDL change (including GRANT and REVOKE) to the object';
comment on column ALL_OBJECTS.TIMESTAMP is 'Timestamp for the specification of the object';
comment on column ALL_OBJECTS.STATUS is 'Status of the object';
comment on column ALL_OBJECTS.TEMPORARY is 'Can the current session only see data that it placed in this object itself?';
comment on column ALL_OBJECTS.GENERATED is 'Was the name of this object system generated?';
comment on column ALL_OBJECTS.SECONDARY is 'Is this a secondary object created as part of icreate for domain indexes?';
comment on column ALL_OBJECTS.NAMESPACE is 'Namespace for the object';
comment on column ALL_OBJECTS.EDITION_NAME is 'Name of the edition in which the object is actual';

DBA_OBJECTS 檢視建立語句：
獲取建立語句命令: SELECT VIEW_NAME,TEXT FROM DBA_VIEWS WHERE VIEW_NAME=upper('dba_objects');
建立語句：
CREATE OR REPLACE VIEW DBA_OBJECTS
(owner, object_name, subobject_name , object_id, data_object_id, object_type, created , last_ddl_time, timestamp, status , temporary, generated, secondary , namespace, edition_name)
AS
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 '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' ,
                      51, 'SUBSCRIPTION' , 52, 'LOCATION',
                      55, 'XML SCHEMA' , 56, 'JAVA DATA',
                      57, 'EDITION' , 59, 'RULE',
                      60, 'CAPTURE' , 61, 'APPLY',
                      62, 'EVALUATION CONTEXT' ,
                      66, 'JOB' , 67, 'PROGRAM', 68 , 'JOB CLASS', 69, 'WINDOW',
                      72, 'SCHEDULER GROUP' , 74, 'SCHEDULE', 79 , 'CHAIN',
                      81, 'FILE GROUP' , 82, 'MINING MODEL', 87 , 'ASSEMBLY',
                      90, 'CREDENTIAL' , 92, 'CUBE DIMENSION', 93 , 'CUBE',
                      94, 'MEASURE FOLDER' , 95, 'CUBE BUILD PROCESS',
                      100, 'FILE WATCHER' , 101, 'DESTINATION',
                     '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'),
       o.namespace,
       o.defining_edition
from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
  and o.linkname is null
  and o.type# !=  10 /* NON-EXISTENT */
  and o.name != '_NEXT_OBJECT'
  and o.name != '_default_auditing_options_'
  and bitand(o.flags , 128) = 0
union all
select u.name , l.name, NULL, to_number (null), to_number(null ),
       'DATABASE LINK',
       l.ctime, to_date (null), NULL, 'VALID' ,'N', 'N', 'N' , NULL, NULL
from sys.link$ l, sys.user$ u
where l.owner# = u.user#;
comment on table DBA_OBJECTS is 'All objects in the database';
comment on column DBA_OBJECTS.OWNER is 'Username of the owner of the object';
comment on column DBA_OBJECTS.OBJECT_NAME is 'Name of the object';
comment on column DBA_OBJECTS.SUBOBJECT_NAME is 'Name of the sub-object (for example, partititon)';
comment on column DBA_OBJECTS.OBJECT_ID is 'Object number of the object';
comment on column DBA_OBJECTS.DATA_OBJECT_ID is 'Object number of the segment which contains the object';
comment on column DBA_OBJECTS.OBJECT_TYPE is 'Type of the object';
comment on column DBA_OBJECTS.CREATED is 'Timestamp for the creation of the object';
comment on column DBA_OBJECTS.LAST_DDL_TIME is 'Timestamp for the last DDL change (including GRANT and REVOKE) to the object';
comment on column DBA_OBJECTS.TIMESTAMP is 'Timestamp for the specification of the object';
comment on column DBA_OBJECTS.STATUS is 'Status of the object';
comment on column DBA_OBJECTS.TEMPORARY is 'Can the current session only see data that it place in this object itself?';
comment on column DBA_OBJECTS.GENERATED is 'Was the name of this object system generated?';
comment on column DBA_OBJECTS.SECONDARY is 'Is this a secondary object created as part of icreate for domain indexes?';
comment on column DBA_OBJECTS.NAMESPACE is 'Namespace for the object';
comment on column DBA_OBJECTS.EDITION_NAME is 'Name of the edition in which the object is actual';




privs_number 查詢表 
/*V$ENABLEDPRIVS displays which privileges are enabled. These privileges can be found in the table SYSTEM_PRIVILEGE_MAP.*/
select * from SYSTEM_PRIVILEGE_MAP
-3     ALTER SYSTEM
-4     AUDIT SYSTEM
-5     CREATE SESSION
-6     ALTER SESSION
-7     RESTRICTED SESSION
-10     CREATE TABLESPACE
-11     ALTER TABLESPACE
-12     MANAGE TABLESPACE
-13     DROP TABLESPACE
-15     UNLIMITED TABLESPACE
-20     CREATE USER
-21     BECOME USER
-22     ALTER USER
-23     DROP USER
-30     CREATE ROLLBACK SEGMENT
-31     ALTER ROLLBACK SEGMENT
-32     DROP ROLLBACK SEGMENT
-40     CREATE TABLE
-41     CREATE ANY TABLE
-42     ALTER ANY TABLE
-43     BACKUP ANY TABLE
-44     DROP ANY TABLE
-45     LOCK ANY TABLE
-46     COMMENT ANY TABLE
-47     SELECT ANY TABLE
-48     INSERT ANY TABLE
-49     UPDATE ANY TABLE
-50     DELETE ANY TABLE
-60     CREATE CLUSTER
-61     CREATE ANY CLUSTER
-62     ALTER ANY CLUSTER
-63     DROP ANY CLUSTER
-71     CREATE ANY INDEX
-72     ALTER ANY INDEX
-73     DROP ANY INDEX
-80     CREATE SYNONYM
-81     CREATE ANY SYNONYM
-82     DROP ANY SYNONYM
-83     SYSDBA
-84     SYSOPER
-85     CREATE PUBLIC SYNONYM
-86     DROP PUBLIC SYNONYM
-90     CREATE VIEW
-91     CREATE ANY VIEW
-92     DROP ANY VIEW
-105     CREATE SEQUENCE
-106     CREATE ANY SEQUENCE
-107     ALTER ANY SEQUENCE
-108     DROP ANY SEQUENCE
-109     SELECT ANY SEQUENCE
-115     CREATE DATABASE LINK
-120     CREATE PUBLIC DATABASE LINK
-121     DROP PUBLIC DATABASE LINK
-125     CREATE ROLE
-126     DROP ANY ROLE
-127     GRANT ANY ROLE
-128     ALTER ANY ROLE
-130     AUDIT ANY
-135     ALTER DATABASE
-138     FORCE TRANSACTION
-139     FORCE ANY TRANSACTION
-140     CREATE PROCEDURE
-141     CREATE ANY PROCEDURE
-142     ALTER ANY PROCEDURE
-143     DROP ANY PROCEDURE
-144     EXECUTE ANY PROCEDURE
-151     CREATE TRIGGER
-152     CREATE ANY TRIGGER
-153     ALTER ANY TRIGGER
-154     DROP ANY TRIGGER
-160     CREATE PROFILE
-161     ALTER PROFILE
-162     DROP PROFILE
-163     ALTER RESOURCE COST
-165     ANALYZE ANY
-167     GRANT ANY PRIVILEGE
-172     CREATE MATERIALIZED VIEW
-173     CREATE ANY MATERIALIZED VIEW
-174     ALTER ANY MATERIALIZED VIEW
-175     DROP ANY MATERIALIZED VIEW
-177     CREATE ANY DIRECTORY
-178     DROP ANY DIRECTORY
-180     CREATE TYPE
-181     CREATE ANY TYPE
-182     ALTER ANY TYPE
-183     DROP ANY TYPE
-184     EXECUTE ANY TYPE
-186     UNDER ANY TYPE
-188     CREATE LIBRARY
-189     CREATE ANY LIBRARY
-190     ALTER ANY LIBRARY
-191     DROP ANY LIBRARY
-192     EXECUTE ANY LIBRARY
-200     CREATE OPERATOR
-201     CREATE ANY OPERATOR
-202     ALTER ANY OPERATOR
-203     DROP ANY OPERATOR
-204     EXECUTE ANY OPERATOR
-205     CREATE INDEXTYPE
-206     CREATE ANY INDEXTYPE
-207     ALTER ANY INDEXTYPE
-208     DROP ANY INDEXTYPE
-209     UNDER ANY VIEW
-210     QUERY REWRITE
-211     GLOBAL QUERY REWRITE
-212     EXECUTE ANY INDEXTYPE
-213     UNDER ANY TABLE
-214     CREATE DIMENSION
-215     CREATE ANY DIMENSION
-216     ALTER ANY DIMENSION
-217     DROP ANY DIMENSION
-218     MANAGE ANY QUEUE
-219     ENQUEUE ANY QUEUE
-220     DEQUEUE ANY QUEUE
-222     CREATE ANY CONTEXT
-223     DROP ANY CONTEXT
-224     CREATE ANY OUTLINE
-225     ALTER ANY OUTLINE
-226     DROP ANY OUTLINE
-227     ADMINISTER RESOURCE MANAGER
-228     ADMINISTER DATABASE TRIGGER
-233     MERGE ANY VIEW
-234     ON COMMIT REFRESH
-235     EXEMPT ACCESS POLICY
-236     RESUMABLE
-237     SELECT ANY DICTIONARY
-238     DEBUG CONNECT SESSION
-241     DEBUG ANY PROCEDURE
-243     FLASHBACK ANY TABLE
-244     GRANT ANY OBJECT PRIVILEGE
-245     CREATE EVALUATION CONTEXT
-246     CREATE ANY EVALUATION CONTEXT
-247     ALTER ANY EVALUATION CONTEXT
-248     DROP ANY EVALUATION CONTEXT
-249     EXECUTE ANY EVALUATION CONTEXT
-250     CREATE RULE SET
-251     CREATE ANY RULE SET
-252     ALTER ANY RULE SET
-253     DROP ANY RULE SET
-254     EXECUTE ANY RULE SET
-255     EXPORT FULL DATABASE
-256     IMPORT FULL DATABASE
-257     CREATE RULE
-258     CREATE ANY RULE
-259     ALTER ANY RULE
-260     DROP ANY RULE
-261     EXECUTE ANY RULE
-262     ANALYZE ANY DICTIONARY
-263     ADVISOR
-264     CREATE JOB
-265     CREATE ANY JOB
-266     EXECUTE ANY PROGRAM
-267     EXECUTE ANY CLASS
-268     MANAGE SCHEDULER
-269     SELECT ANY TRANSACTION
-270     DROP ANY SQL PROFILE
-271     ALTER ANY SQL PROFILE
-272     ADMINISTER SQL TUNING SET
-273     ADMINISTER ANY SQL TUNING SET
-274     CREATE ANY SQL PROFILE
-275     EXEMPT IDENTITY POLICY
-276     MANAGE FILE GROUP
-277     MANAGE ANY FILE GROUP
-278     READ ANY FILE GROUP
-279     CHANGE NOTIFICATION
-280     CREATE EXTERNAL JOB
-281     CREATE ANY EDITION
-282     DROP ANY EDITION
-283     ALTER ANY EDITION
-284     CREATE ASSEMBLY
-285     CREATE ANY ASSEMBLY
-286     ALTER ANY ASSEMBLY
-287     DROP ANY ASSEMBLY
-288     EXECUTE ANY ASSEMBLY
-289     EXECUTE ASSEMBLY
-290     CREATE MINING MODEL
-291     CREATE ANY MINING MODEL
-292     DROP ANY MINING MODEL
-293     SELECT ANY MINING MODEL
-294     ALTER ANY MINING MODEL
-295     COMMENT ANY MINING MODEL
-301     CREATE CUBE DIMENSION
-302     ALTER ANY CUBE DIMENSION
-303     CREATE ANY CUBE DIMENSION
-304     DELETE ANY CUBE DIMENSION
-305     DROP ANY CUBE DIMENSION
-306     INSERT ANY CUBE DIMENSION
-307     SELECT ANY CUBE DIMENSION
-308     CREATE CUBE
-309     ALTER ANY CUBE
-310     CREATE ANY CUBE
-311     DROP ANY CUBE
-312     SELECT ANY CUBE
-313     UPDATE ANY CUBE
-314     CREATE MEASURE FOLDER
-315     CREATE ANY MEASURE FOLDER
-316     DELETE ANY MEASURE FOLDER
-317     DROP ANY MEASURE FOLDER
-318     INSERT ANY MEASURE FOLDER
-319     CREATE CUBE BUILD PROCESS
-320     CREATE ANY CUBE BUILD PROCESS
-321     DROP ANY CUBE BUILD PROCESS
-322     UPDATE ANY CUBE BUILD PROCESS
-326     UPDATE ANY CUBE DIMENSION
-327     ADMINISTER SQL MANAGEMENT OBJECT
-328     ALTER PUBLIC DATABASE LINK
-329     ALTER DATABASE LINK
-350     FLASHBACK ARCHIVE ADMINISTER
-351     EXEMPT REDACTION POLICY


總結：
  Oracle  all_*檢視和許可權有關，完全不等於dba_*檢視，所以一定要適當的選擇 dba_*和all_*檢視。

 
  【源於本人筆記】 若有書寫錯誤，表達錯誤，請指正...
此條目發表在   Oracle  分類目錄。將固定連線加入收藏夾。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28602568/viewspace-1771547/，如需轉載，請註明出處，否則將追究法律責任。

相關文章