隱藏引數查詢和dictionary view

tolilong發表於2012-10-16
View
1.查詢隱藏引數
SQL> alter session set sql_trace=true;
Session altered.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_name string test
SQL> alter session set sql_trace=false;
Session altered.
檢視trace檔案
D:appdiagrdbmstesttesttrace>tkprof test_ora_5816.trc abcd.txt
TKPROF: Release 11.2.0.1.0 - Development on 星期一 8月 27 10:32:34 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
檔案中顯示show parameter的查詢語句
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(:NMBIND_SHOW_OBJ) ORDER BY
NAME_COL_PLUS_SHOW_PARAM,ROWNUM
看以看出show parmaeter是查詢了v$parameter，
SQL> select * from v$fixed_view_definition where view_name='V$PARAMETER';
VIEW_NAME VIEW_DEFINITION
-------------------- --------------------------------------------------------------------------------
V$PARAMETER select NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE
, ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECAT
ED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASH from GV$PARAMETER where inst_id
= USERENV('Instance')
SQL> select * from v$fixed_view_definition where view_name='GV$PARAMETER';
VIEW_NAME VIEW_DEFINITION
-------------------- --------------------------------------------------------------------------------
GV$PARAMETER 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,'I
MMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE'),
decode(bitand(ksppiflg,4),4,'FALSE', decod
e(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(ksppi
lrmflg/268435456, 1), 1, 'TRUE', 'FALSE'), ksppdesc, ksppstcmnt, ksppihash fro
m 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)))
從中，我們可以得出，主要是查詢x$ksppi,和x$ksppcv兩個表，可以直接查詢這兩個table
select a.ksppinm,b.ksppstvl,b.ksppstdvl,a.ksppdesc from x$ksppi a,x$ksppcv b where a.indx=b.indx and a.ksppinm like '%spin%'
或者:
create or replace view my_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 x.inst_id,x.indx+1 indx,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,'_','#') like '#%') or (ksppstdf = 'FALSE') or (bitand(ksppstvf,5) > 0) ) )
CREATE or replace VIEW MY_V$PARAMETER AS
select NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECATED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASH from my_GV$PARAMETER where inst_id = USERENV('Instance')
SQL> grant select on my_v$parameter to test;
Grant succeeded.
在test使用者下：
CREATE or replace SYNONYM V$PARMETER for sys.MY_V$PARAMETER
然後test使用者下，可以查詢隱藏引數：
SQL> show parameter spin
NAME TYPE VALUE
------------------------------------ ---------------------- ----------------
_kgx_spin_count integer 255
_spin_count integer 2000
2.查詢資料字典的順序。
synonym --&gtgv_$型別view --&gt v$型別view--&gt x$型別table
(1)SQL> select object_name,object_type from dba_objects where object_name='V$PARAMETER' and owner='PUBLIC';
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------------------------
V$PARAMETER SYNONYM
SQL> select * from dba_synonyms where synonym_name='V$PARAMETER' AND OWNER='PUBLIC';
OWNER SYNONYM_NAME TABLE_OWNE TABLE_NAME DB_LINK
---------- ------------------ ---------- ------------------- ----------
PUBLIC V$PARAMETER SYS V_$PARAMETER
(2)SQL> select object_name,object_type from dba_objects where object_name='V_$PARAMETER';
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------------------------
V_$PARAMETER VIEW
檢視dba_view中，可以得出，text是查詢v$parameter檢視
(3)SQL> select * from v$fixed_view_definition where view_name='V$PARAMETER';
VIEW_NAME VIEW_DEFINITION
-------------------- --------------------------------------------------------------------------------
V$PARAMETER select NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE
, ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECAT
ED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASH from GV$PARAMETER where inst_id
= USERENV('Instance')
(4)SQL> select * from v$fixed_view_definition where view_name='GV$PARAMETER';
VIEW_NAME VIEW_DEFINITION
-------------------- --------------------------------------------------------------------------------
GV$PARAMETER 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,'I
MMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE'),
decode(bitand(ksppiflg,4),4,'FALSE', decod
e(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(ksppi
lrmflg/268435456, 1), 1, 'TRUE', 'FALSE'), ksppdesc, ksppstcmnt, ksppihash fro
m 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)))
[@more@]

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

相關文章