非sys用户下的存储过程对v$视图的使用

1、赋予select any dictionary权限
(sys)
------------------------------------------------------
SQL> grant select any dictionary to scott;
------------------------------------------------------

(scott)
------------------------------------------------------
create or replace procedure pr_test is
  v_name varchar2(10);
begin
  select name into v_name from v$database;
  dbms_output.put_line('database name: ' || v_name);
end;
/
------------------------------------------------------

2、在视图上添加select权限，并使用动态SQL
(sys) v$xxx是v_$xxx的同义词，应给v_$xxx加select权限
------------------------------------------------------
SQL> grant select on v_$database to scott;
------------------------------------------------------

(scott)
--------------------------------------------------------------
create or replace procedure pr_test is
  v_name varchar2(10);
begin
  execute immediate 'select name from v_$database' into v_name;
  dbms_output.put_line('database name: ' || v_name);
end;
/
--------------------------------------------------------------

3、通过authid current_user使用户的角色可以用于存储过程中
(sys)
------------------------------------------------------
SQL> grant select_catalog_role to scott;
------------------------------------------------------

(scott)
--------------------------------------------------------------
create or replace procedure pr_test authid current_user is
  v_name varchar2(10);
begin
  execute immediate 'select name from v$database' into v_name;
  dbms_output.put_line('database name: ' || v_name);
end;
/
--------------------------------------------------------------