非sysdba用户对x$bh的查询

x$bh（buffer header）是一个非常有用的视图，在解决某些问题是可以给我们提供一些有用的信息（比如定位热点块），在oracle中一般用户（非sysdba）是不能直接对x$bh进行查询的,但我们可以通过建立视图，并提供视图的查询权限给其他用户来达到其他用户可以查询x$bh的能力。

 

 

1) You cannot grant a select privilege on X$* tables to another user due to they are internally
protected.

- If you try grant select on x$ objects then you will receive the following error:

SQL> grant select on sys.x$bh to scott;
grant select on sys.x$bh to scott
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


2) Alternatively, you can create a view and then grant a select privilege on that new view to
another user as follow:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "SYS"
SQL> create user scott identified by tiger default tablespace users temporary tablespace temp;

User created.

SQL> grant resource to scott;

Grant succeeded.

SQL> grant connect to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL> show user
USER is "SCOTT"

SQL> select * from sys.x$bh;
select * from sys.x$bh
*
ERROR at line 1:
ORA-00942: table or view does not exist

==================================================================

SQL> show user
USER is "SYS"
SQL> grant select on sys.x$bh to scott;
grant select on sys.x$bh to scott
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


SQL> create view vw_x$bh as select * from sys.x$bh; <(=== This works

View created.

SQL> grant select on sys.vw_x$bh to scott; <(=== This works

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> select * from sys.vw_x$bh; <(=== This works