惜分飞

本篇文章以v$parameter和gv$parameter为例做讲解
1、查看v$parameter对象类型，并查看其创建sql语句

SELECT * FROM all_objects a WHERE a.object_name='V$PARAMETER';
select  dbms_metadata.get_ddl(object_type=>'SYNONYM',NAME=>'V$PARAMETER',SCHEMA=>'PUBLIC') from dual;
--V$PARAMETER SYNONYN
CREATE OR REPLACE PUBLIC SYNONYM "V$PARAMETER" FOR "SYS"."V_$PARAMETER"

2、查看V_$PARAMETER对象类型，并查看其创建语句

SELECT *　FROM all_objects a WHERE a.object_name='V_$PARAMETER';
select  dbms_metadata.get_ddl(object_type=>'VIEW',NAME=>'V_$PARAMETER',SCHEMA=>'SYS') from dual;
--V_$PARAMETER VIEW
  CREATE OR REPLACE FORCE VIEW "SYS"."V_$PARAMETER" 
  ("NUM", "NAME", "TYPE", "VALUE", "DISPLAY_VALUE", "ISDEFAULT", "ISSES_MODIFIABLE", 
  "ISSYS_MODIFIABLE", "ISINSTANCE_MODIFIABLE", "ISMODIFIED", "ISADJUSTED", "ISDEPRECATED", 
  "ISBASIC", "DESCRIPTION", "UPDATE_COMMENT", "HASH") AS 
  SELECT "NUM",
         "NAME",
         "TYPE",
         "VALUE",
         "DISPLAY_VALUE",
         "ISDEFAULT",
         "ISSES_MODIFIABLE",
         "ISSYS_MODIFIABLE",
         "ISINSTANCE_MODIFIABLE",
         "ISMODIFIED",
         "ISADJUSTED",
         "ISDEPRECATED",
         "ISBASIC",
         "DESCRIPTION",
         "UPDATE_COMMENT",
         "HASH"
    FROM V$PARAMETER;

3、查看V$PARAMETER类型，并查看其创建语句

SELECT * FROM v$fixed_table a WHERE a.name='V$PARAMETER';
SELECT * FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_name='V$PARAMETER';
 --V$PARAMETER VIEW
    SELECT NUM,
           NAME,
           TYPE,
           VALUE,
           DISPLAY_VALUE,
           ISDEFAULT,
           ISSES_MODIFIABLE,
           ISSYS_MODIFIABLE,
           ISINSTANCE_MODIFIABLE,
           ISMODIFIED,
           ISADJUSTED,
           ISDEPRECATED,
           ISBASIC,
           DESCRIPTION,
           UPDATE_COMMENT,
           HASH
      FROM GV$PARAMETER
     WHERE INST_ID = USERENV('Instance')

4、查看GV$PARAMETER类型，及其创建sql语句

SELECT * FROM v$fixed_table a WHERE a.name='GV$PARAMETER';
SELECT * FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_name='GV$PARAMETER';
--GV$PARAMETER  VIEW
     SELECT X.INST_ID,
            X.INDX + 1,
            KSPPINM,
            KSPPITY,
            KSPPSTVL,
            KSPPSTDVL,
            KSPPSTDF,
            DECODE(BITAND(KSPPIFLG / 256, 1), 1, 'TRUE', 'FALSE'),
            DECODE(BITAND(KSPPIFLG / 65536, 3),
                   1,
                   'IMMEDIATE',
                   2,
                   'DEFERRED',
                   3,
                   'IMMEDIATE',
                   'FALSE'),
            DECODE(BITAND(KSPPIFLG, 4),
                   4,
                   'FALSE',
                   DECODE(BITAND(KSPPIFLG / 65536, 3), 0, 'FALSE', 'TRUE')),
            DECODE(BITAND(KSPPSTVF, 7),
                   1,
                   'MODIFIED',
                   4,
                   'SYSTEM_MOD',
                   'FALSE'),
            DECODE(BITAND(KSPPSTVF, 2), 2, 'TRUE', 'FALSE'),
            DECODE(BITAND(KSPPILRMFLG / 64, 1), 1, 'TRUE', 'FALSE'),
            DECODE(BITAND(KSPPILRMFLG / 268435456, 1), 1, 'TRUE', 'FALSE'),
            KSPPDESC,
            KSPPSTCMNT,
            KSPPIHASH
       FROM X$KSPPI X, X$KSPPCV Y
      WHERE (X.INDX = Y.INDX)
        AND BITAND(KSPPIFLG, 268435456) = 0
        AND ((TRANSLATE(KSPPINM, '_', '#') NOT LIKE '##%') AND
            ((TRANSLATE(KSPPINM, '_', '#') NOT LIKE '#%') OR
            (KSPPSTDF = 'FALSE') OR (BITAND(KSPPSTVF, 5) > 0)))

5、查看gv$parameter同义词和gv_$parameter视图

--GV$PARAMETER  SYNONYM
CREATE OR REPLACE PUBLIC SYNONYM "GV$PARAMETER" FOR "SYS"."GV_$PARAMETER"
--GV_$PRAMETER VIEW
CREATE OR REPLACE FORCE VIEW "SYS"."GV_$PARAMETER" 
("INST_ID", "NUM", "NAME", "TYPE", "VALUE", "DISPLAY_VALUE", "ISDEFAULT", 
"ISSES_MODIFIABLE", "ISSYS_MODIFIABLE", "ISINSTANCE_MODIFIABLE", "ISMODIFIED",
 "ISADJUSTED", "ISDEPRECATED", "ISBASIC", "DESCRIPTION", "UPDATE_COMMENT", "HASH") AS 
  SELECT "INST_ID",
         "NUM",
         "NAME",
         "TYPE",
         "VALUE",
         "DISPLAY_VALUE",
         "ISDEFAULT",
         "ISSES_MODIFIABLE",
         "ISSYS_MODIFIABLE",
         "ISINSTANCE_MODIFIABLE",
         "ISMODIFIED",
         "ISADJUSTED",
         "ISDEPRECATED",
         "ISBASIC",
         "DESCRIPTION",
         "UPDATE_COMMENT",
         "HASH"
    FROM GV$PARAMETER

6、总结
x$(table)–>gv$(view)–>v$(view)–>v_$(view)–>v$(SYNONYM)
x$(table)–>gv$(view)–>gv_$(view)–>gv$(synonym)
1）sys的fixed table x$
2）sys的fixed view: GV$
3）sys的fixed view: V$
4）sys的普通view: V_$
5）public的synonym: v$
6) sys的fixed view: gv_$
7) public的synonym: gv$
如果用sys访问v$, 就是3=>2=>1,other user访问v$, 就是5=>4=>3=>2=>1.
如果用sys访问gv$, 就是6=>2=>1,other user访问gv$, 就是7=>6=>2=>1.

1、使用trace查找show parameter执行语句

alter session set events '10046 trace name context forever,level 12';
show parameter process;
 alter session set events '10046 trace name context off';

2、查找trace文件

SELECT    d.VALUE
           || '/'
           || LOWER (RTRIM (i.INSTANCE, CHR (0)))
           || '_ora_'
           || p.spid
          || '.trc' trace_file_name
      FROM (SELECT p.spid
              FROM v$mystat m, v$session s, v$process p
             WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
          (SELECT t.INSTANCE
             FROM v$thread t, v$parameter v
            WHERE v.NAME = 'thread'
              AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
          (SELECT VALUE
             FROM v$parameter
            WHERE NAME = 'user_dump_dest') d

3、格式化trace文件

tkprof D:\oracle\diag\rdbms\xff\xff\trace\xff_ora_4780.trc D:\oracle\diag\rdbms\xff\xff\trace\abc.txt

4、找出对应sql语句

SELECT NAME NAME_COL_PLUS_SHOW_PARAM,
       DECODE(TYPE,
              1,
              'boolean',
              2,
              'string',
              3,
              'integer',
              4,
              'file',
              5,
              'number',
              6,
              'big integer',
              'unknown') TYPE,
       DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM
  FROM V$PARAMETER
 WHERE UPPER(NAME) LIKE UPPER('%process%')
 ORDER BY NAME_COL_PLUS_SHOW_PARAM, ROWNUM;

5、查找上面语句发现使用v$parameter对象
6、查看第一种来源

select owner,object_name,object_type from dba_objects where object_name ='V$PARAMETER';
select  dbms_metadata.get_ddl(object_type=>'SYNONYM',NAME=>'V$PARAMETER',SCHEMA=>'PUBLIC') from dual;

7、无结果，查看第二种来源

select * from v$fixed_table where name ='V%PARAMETER%';
 select * from v$fixed_view_definition where view_name='GV$PARAMETER';

8、得出结果，从中学习到数据库中的对象有两种来源

1）sys的fixed table : x$ksppi x, x$ksppcv y
2）sys的fixed view: GV$PARAMETER
3）sys的fixed view: V$PARAMETER
4） sys的普通view: V_$PARAMETER
5） public的synonym: v$parameter
所以，如果用sys访问v$parameter, 就是3=>2=>1
other user访问v$parameter, 就是5=>4=>3=>2=>1.

需要显示授权
grand select on sys.V_$SESSION to xxx;
说明：不能直接授权给v$session
原因：v$session是公共同义词，不能被授权
(可以通过select * from dba_objects where object_name=’V$SESSION’;查询出来)
note：11gr2版本存在该问题，其他版本暂时未知