買了最新的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 < 5;

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> grant select on v_$latch to yang;

Grant succeeded.

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

Grant succeeded.

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

yang@RAC>

yang@RAC> drop table run_stats;

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>

yang@RAC> grant select any table to yang;

Grant succeeded.

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;

View created.

yang@RAC>

yang@RAC> delete from run_stats;

0 rows deleted.

yang@RAC> commit;

Commit complete.

yang@RAC>

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>

yang@RAC> create or replace package body runstats_pkg

2 as

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.

這次可以了。。

【Oracle】v$表和v_$同義詞的訪問許可權

相關文章