v$fixed_table表研究

SQL>CONNECT SYS/PASS@SID AS SYSDBA

  1* select * from v$fixed_table where name='X$KZSRO'

SQL> /

NAME                                                          OBJECT_ID

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

TYPE        TABLE_NUM

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

X$KZSRO                                                      4294951024

TABLE             309

V$FIXED_TABLE的定义

SQL> select * from v$fixed_view_definition where view_name='V$FIXED_TABLE';

VIEW_NAME

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

VIEW_DEFINITION

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

V$FIXED_TABLE

select  NAME , OBJECT_ID , TYPE , TABLE_NUM from GV$FIXED_TABLE where inst_id =

USERENV('Instance')

 

  1* select * from v$fixed_view_definition where view_name='GV$FIXED_TABLE'

SQL> /

VIEW_NAME

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

VIEW_DEFINITION

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

GV$FIXED_TABLE

select inst_id,kqftanam, kqftaobj, 'TABLE', indx from x$kqfta union all select i

nst_id,kqfvinam, kqfviobj, 'VIEW', 65537 from x$kqfvi union all select inst_id,k

qfdtnam, kqfdtobj, 'TABLE', 65537 from x$kqfdt

 

v_$fixed_table 和v$fixed_table的区别

create or replace view v_$fixed_table as select * from v$fixed_table;

create or replace public synonym v$fixed_table for v_$fixed_table;

grant select on v_$fixed_table to select_catalog_role; 

也就是说v_$fixed_table 是v$fixed_table的视图，PUBLIC v$fixed_table是v_$fixed_table的同意词。

 

也就是说先有x$基表，再有GV$FIXED_TABLE 视图，再有V$FIXED_TABLE，再有v_$fixed_table 试图，再有v$fixed_table 公共同义词。

 

fixed_table可以SELECT ，DESC

SQL> select * from X$KZSRO;

ADDR           INDX    INST_ID   KZSROROL

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

00000000          0          1          1

00000008          1          1          0

 

SQL> desc X$KZSRO

 Name                                      Null?    Type

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

 ADDR                                               RAW(4)

 INDX                                               NUMBER

 INST_ID                                            NUMBER

 KZSROROL                                           NUMBER

 

fixed_table只在v$fixed_table存在

  1* select table_name from user_tables where table_name like 'X$KZSRO%'

SQL> /

no rows selected

SQL> select table_name from dba_tables where table_name='X$KZSRO'

  2  /

no rows selected

 

 

v$fixed_table的授权方式

  1* grant select  on v$fixed_table to sbtopt

SQL> /

grant select  on v$fixed_table to sbtopt

                 *

ERROR at line 1:

ORA-02030: can only select from fixed tables/views

SQL> grant select on v_$fixed_table to system

  2  /

Grant succeeded.

SQL> SELECT dbms_metadatA.get_ddl('VIEW','V$FIXED_TABLE') FROM DUAL;

ERROR:

ORA-31603: object "V$FIXED_TABLE" of type VIEW not found in schema "SYS"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105

ORA-06512: at "SYS.DBMS_METADATA", line 628

ORA-06512: at "SYS.DBMS_METADATA", line 1221

ORA-06512: at line 1

 

 

fixed_table的授权方式

SQL> grant select on SYS.X$KZSRO to system;

grant select on SYS.X$KZSRO to system

                    *

ERROR at line 1:

ORA-02030: can only select from fixed tables/views

直接授权不许可

SQL> create view v_x$kzsro as select * from sys.x$kzsro;

View created.

SQL> grant select on v_x$kzsro to system;

Grant succeeded.

间接授权可以

 

同v$fixed_table的授权方式一样，基本表或视图不能直接授权，只能间接授权

 

  1* select * from sys.v_x$kzsro

SQL> /

ADDR           INDX    INST_ID   KZSROROL

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

00000000          0          1          1

00000008          1          1          5

00000010          2          1         61

00000018          3          1          4

00000020          4          1          6

00000028          5          1         20

00000030          6          1          7

00000038          7          1          8

00000040          8          1          9

00000048          9          1         10

00000050         10          1         13

ADDR           INDX    INST_ID   KZSROROL

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

00000058         11          1         22

00000060         12          1         28

00000068         13          1         29

00000070         14          1         37

00000078         15          1         45

00000080         16          1         15

00000088         17          1         50

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12712263/viewspace-606661/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12712263/viewspace-606661/