【Oracle】v$表和v_$同義詞的通路權限

買了最新的11gr2tom kyte的書程式設計藝術，參考其中的例子做實驗，發現：

yang@RAC> create or replace view stats

  2  as select 'STAT...' || a.name name, b.value

  3        from v$statname a, v$mystat b

  4       where a.statistic# = b.statistic#

  5      union all

  6      select 'LATCH.' || name,  gets

  7        from v$latch

  8          union all

  9          select 'STAT...Elapsed Time', hsecs from v$timer;

      from v$statname a, v$mystat b

           *

ERROR at line 3:

ORA-01031: insufficient privileges

單獨查詢則是可以的。

yang@RAC> select 'STAT...' || a.name name, my.value

  2        from v$statname a, v$mystat my

  3       where a.statistic# = my.statistic# and rownum

NAME                                                                         VALUE

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

STAT...OS CPU Qt wait time                                                       0

STAT...logons cumulative                                                         1

STAT...logons current                                                            1

STAT...opened cursors cumulative                                               101

查詢之後，了解到對于v$對象，并不是視圖，而是指向v_$視圖的同義詞，而視圖是基于真正的v$視圖建立的，包括X$，這些對象是Oracle資料庫的運作基礎，在資料庫啟動時由Oracle應用程式動态建立。

這部分表對資料庫來說至關重要，是以Oracle不允許SYSDBA之外的使用者直接通路，顯示授權不被允許。

sys@RAC>  grant select on v_$statname to yang;

Grant succeeded.

sys@RAC> 

sys@RAC>    

sys@RAC> grant select on v_$latch to yang;

sys@RAC> grant select on v_$timer to yang;

sys@RAC> conn yang/yang

Connected.

yang@RAC> 

yang@RAC> drop table run_stats;

Table dropped.

yang@RAC> @runstats_pkg.sql

SP2-0310: unable to open file "runstats_pkg.sql"

yang@RAC> !ls

big_table.sql  ch00  ch02  ch04  ch06  ch08  ch10  ch12  ch14  ch16         login.sql    mystat.sql    show_space.sql

book.zip       ch01  ch03  ch05  ch07  ch09  ch11  ch13  ch15  demobld.sql  mystat2.sql  runstats.sql  tk.sql

yang@RAC> @runstats.sql

yang@RAC> set echo on

drop table run_stats

ERROR at line 1:

ORA-00942: table or view does not exist

yang@RAC> create global temporary table run_stats

  2  ( runid varchar2(15),

  3    name varchar2(80),

  4    value int )

  5  on commit preserve rows;

Table created.

yang@RAC> grant select any table to yang;

View created.

yang@RAC> delete from run_stats;

0 rows deleted.

yang@RAC> commit;

Commit complete.

yang@RAC> create or replace package runstats_pkg

  2  as

  3      procedure rs_start;

  4      procedure rs_middle;

  5      procedure rs_stop( p_difference_threshold in number default 0 );

  6  end;

  7  /

Package created.

yang@RAC> create or replace package body runstats_pkg

  3  

  4  g_start number;

  5  g_run1     number;

  6  g_run2     number;

  7  

  8  procedure rs_start

  9  is

 10  begin

 11      delete from run_stats;

 12  

 13      insert into run_stats

 14      select 'before', stats.* from stats;

 15  

 16      g_start := dbms_utility.get_cpu_time;

 17  end;

 18  

 19  procedure rs_middle

 20  is

 21  begin

 22      g_run1 := (dbms_utility.get_cpu_time-g_start);

 23  

 24      insert into run_stats

 25      select 'after 1', stats.* from stats;

 26      g_start := dbms_utility.get_cpu_time;

 27  

 28  end;

 29  

 30  procedure rs_stop(p_difference_threshold in number default 0)

 31  is

 32  begin

 33      g_run2 := (dbms_utility.get_cpu_time-g_start);

 34  

 35      dbms_output.put_line

 36      ( 'Run1 ran in ' || g_run1 || ' cpu hsecs' );

 37      dbms_output.put_line

 38      ( 'Run2 ran in ' || g_run2 || ' cpu hsecs' );

 39          if ( g_run2 0 )

 40          then

 41      dbms_output.put_line

 42      ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||

 43        '% of the time' );

 44          end if;

 45      dbms_output.put_line( chr(9) );

 46  

 47      insert into run_stats

 48      select 'after 2', stats.* from stats;

 49  

 50      dbms_output.put_line

 51      ( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) ||

 52        lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );

 53  

 54      for x in

 55      ( select rpad( a.name, 30 ) ||

 56               to_char( b.value-a.value, '999,999,999' ) ||

 57               to_char( c.value-b.value, '999,999,999' ) ||

 58               to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data

 59          from run_stats a, run_stats b, run_stats c

 60         where a.name = b.name

 61           and b.name = c.name

 62           and a.runid = 'before'

 63           and b.runid = 'after 1'

 64           and c.runid = 'after 2'

 65           -- and (c.value-a.value) > 0

 66           and abs( (c.value-b.value) - (b.value-a.value) )

 67                 > p_difference_threshold

 68         order by abs( (c.value-b.value)-(b.value-a.value))

 69      ) loop

 70          dbms_output.put_line( x.data );

 71      end loop;

 72  

 73      dbms_output.put_line( chr(9) );

 74      dbms_output.put_line

 75      ( 'Run1 latches total versus runs -- difference and pct' );

 76      dbms_output.put_line

 77      ( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) ||

 78        lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );

 79  

 80      for x in

 81      ( select to_char( run1, '999,999,999' ) ||

 82               to_char( run2, '999,999,999' ) ||

 83               to_char( diff, '999,999,999' ) ||

 84               to_char( round( run1/decode( run2, 0, to_number(0), run2) *100,2 ), '99,999.99' ) || '%' data

 85          from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,

 86                        sum( (c.value-b.value)-(b.value-a.value)) diff

 87                   from run_stats a, run_stats b, run_stats c

 88                  where a.name = b.name

 89                    and b.name = c.name

 90                    and a.runid = 'before'

 91                    and b.runid = 'after 1'

 92                    and c.runid = 'after 2'

 93                    and a.name like 'LATCH%'

 94                  )

 95      ) loop

 96          dbms_output.put_line( x.data );

 97      end loop;

 98  end;

 99  

100  end;

101  /

Package body created.

這次可以了。。