最近想写个存储过程,定时调度,kill active session。脚本写的没问题,可以执行。用到了V$Process,放存储过程里面一直报表或视图不存在的错误,怎么也编译不通过。在网上搜了半天,终于搜到了下面这篇文章,把这个核心问题解决了。虽然存储过程还没完成,但是也差不多了。
收藏一下这篇文章。
要在非sys用户下存储过程中使用v$xxx视图,还是尽量使用动态sql,并设置AUTHID CURRENT_USER特征让本存储过程可以使用当前用户的角色权限。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
写一存储过程从v$datafile中获取记录数:
SQL> conn wacos/oss
Connected.
SQL> CREATE OR REPLACE PROCEDURE sp_test
2 IS
3 v_count PLS_INTEGER;
4 BEGIN
5 SELECT COUNT(*)
6 INTO v_count
7 FROM v$datafile;
8
9 DBMS_OUTPUT.put_line('v_count=' || v_count);
10 END;
11 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE SP_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5 PL/SQL: SQL Statement ignored
7/12 PL/SQL: ORA-00942: table or view does not exist
上面提示不存在该视图,现加上该视图的select权限:
SQL> conn /as sysdba
Connected.
SQL> grant select on v$datafile to wacos;
grant select on v$datafile to wacos
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
v$xxx只是对应v_$xxx的同义词,应当给对应v_$xxx加select权限:
SQL> grant select on v_$datafile to wacos;
Grant succeeded.
重建该过程,并修改为从v_$datafile获取数据:
SQL> conn wacos/oss
Connected.
SQL> CREATE OR REPLACE PROCEDURE sp_test
2 IS
3 v_count PLS_INTEGER;
4 BEGIN
5 SELECT COUNT(*)
6 INTO v_count
7 FROM v_$datafile;
8
9 DBMS_OUTPUT.put_line('v_count=' || v_count);
10 END;
11 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE SP_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5 PL/SQL: SQL Statement ignored
7/12 PL/SQL: ORA-00942: table or view does not exist
从上面看,还是提示该视图不存在,看看当前用户的表权限信息:
SQL> set lines 256
SQL> select * from user_tab_privs where table_name = 'V_$DATAFILE';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
WACOS SYS V_$DATAFILE SYS SELECT NO NO
再试试通过AUTHID CURRENT_USER使用户的角色可以用于存储过程中:
SQL> CREATE OR REPLACE PROCEDURE sp_test
2 AUTHID CURRENT_USER
3 IS
4 v_count PLS_INTEGER;
5 BEGIN
6 SELECT COUNT(*)
7 INTO v_count
8 FROM v_$datafile;
9
10 DBMS_OUTPUT.put_line('v_count=' || v_count);
11 END;
12 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE SP_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5 PL/SQL: SQL Statement ignored
8/12 PL/SQL: ORA-00942: table or view does not exist
SQL>
还是提示表不存在,说明即使赋予了v_$xxx视图select权限给用户,还是无法在plsql中直接调用v$视图。
现在看看能否用动态sql来获取:
SQL> CREATE OR REPLACE PROCEDURE sp_test
2 IS
3 v_count PLS_INTEGER;
4 BEGIN
5 EXECUTE IMMEDIATE ' SELECT count(*) FROM v$datafile '
6 INTO v_count;
7
8 DBMS_OUTPUT.put_line('v_count=' || v_count);
9 END;
10 /
Procedure created.
SQL> set serverout on
SQL> exec sp_test;
v_count=23
PL/SQL procedure successfully completed.
说明在v_$xxx被赋予了select权限后,可以使用动态sql来调用对应v$视图。
把v_$xxx视图的select权限回收:
SQL> conn /as sysdba
Connected.
SQL> revoke select on v_$datafile from wacos;
Revoke succeeded.
SQL> conn wacos/oss
Connected.
SQL> select * from user_tab_privs where table_name = 'V_$DATAFILE';
no rows selected
再试试通过AUTHID CURRENT_USER使用户的角色可以用于存储过程中:
SQL> CREATE OR REPLACE PROCEDURE sp_test
2 AUTHID CURRENT_USER
3 IS
4 v_count PLS_INTEGER;
5 BEGIN
6 EXECUTE IMMEDIATE ' SELECT count(*) FROM v$datafile '
7 INTO v_count;
8
9 DBMS_OUTPUT.put_line('v_count=' || v_count);
10 END;
11 /
Procedure created.
SQL> set serverout on
SQL> exec sp_test;
v_count=23
PL/SQL procedure successfully completed.
通过以上测试表明:要在非sys用户下使用v$xxx视图,还是尽量使用动态sql,并设置AUTHID CURRENT_USER特征让本存储过程可以使用当前用户的角色权限。