oracle授权同义词权限,【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 < 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>

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>

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.

这次可以了。。