买了最新的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.
这次可以了。。