168大数据

标题: 关于procedure中调用dbms_lock的package [打印本页]

---

作者: dbaxiaoyu    时间: 2014-8-13 15:25
标题: 关于procedure中调用dbms_lock的package

开发人员创建的procedure需要调用dbms_lock的package中的对象，调用时候出现了类似下列的问题。

[AppleScript] 纯文本查看 复制代码

SQL> create or replace procedure proc01 as

2 begin

3 dbms_lock.sleep(10);

4 end;

5 /

Procedure created.

SQL> show user;

USER is "SYS"

SQL> conn xiaoyu/xiaoyu

Connected.

SQL> create or replace procedure proc01 as

2 begin

3 dbms_lock.sleep(10);

4 end;

5 /

Warning: Procedure created with compilation errors.

SQL> show errors;

Errors for PROCEDURE PROC01:

LINE/COL ERROR

-------- -----------------------------------------------------------------

3/1 PL/SQL: Statement ignored

3/1 PLS-00201: identifier 'DBMS_LOCK' must be declared

SQL> conn / as sysdba

Connected.

这里用静态sql调用dbms_lock的package时，oracle报出了无法识别该package，这里需要单独授权这个package给用户，这个需要特别注意，因为正常的匿名块程序中我们是可以调用的，但是procedure中则不行了。

[AppleScript] 纯文本查看 复制代码

SQL> grant execute on sys.dbms_lock to xiaoyu;

Grant succeeded.

SQL> conn xiaoyu/xiaoyu

Connected.

SQL> create or replace procedure proc01 as

2 begin

3 dbms_lock.sleep(10);

4 end;

5 /

Procedure created.

但是这里并不是说所有的dbms开头的package下的对象都不能在procedure或者function中调用，比如dbms_stats的package在不单独授权的情况下就能够正常调用。

[AppleScript] 纯文本查看 复制代码

SQL> create or replace procedure proc03 as

2 begin

3 dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T_DYNAMIC01');

4 end;

5 /

Procedure created.

这里我们想想动态sql能否实现了：

[AppleScript] 纯文本查看 复制代码

SQL> revoke execute on sys.dbms_lock from xiaoyu;

Revoke succeeded.

SQL> create or replace procedure proc01 as

2 begin

3 execute immediate 'dbms_lock.sleep(10)';

4 end;

5 /

Procedure created.

SQL> exec proc01;

BEGIN proc01; END;

*

ERROR at line 1:

ORA-00900: invalid SQL statement

ORA-06512: at "SYS.PROC01", line 3

ORA-06512: at line 1

这里来看动态sql执行的时候出现了问题，oracle报出了在第三行出现了无效的语句，动态sql相比静态sql可以解决一些静态sql无法实现的问题，比如参数的不确定性造成没办法评估程序的具体操作，再比如在存储过程或者匿名块中实现ddl语句。

如下动态sql解决在procedure中实现ddl的示例：

[AppleScript] 纯文本查看 复制代码

SQL> create or replace procedure proc02 as

2 begin

3 create table t_dynamic01 as select * from dual;

4 end;

5 /

Warning: Procedure created with compilation errors.

SQL> show errors;

Errors for PROCEDURE PROC02:

LINE/COL ERROR

-------- -----------------------------------------------------------------

3/1 PLS-00103: Encountered the symbol "CREATE" when expecting one of

the following:

begin case declare exit for goto if loop mod null pragma

raise return select update while with 

[url=]<<

close current delete fetch lock insert open rollback

savepoint set sql execute commit forall merge pipe[/url]

[url=]

SQL> create or replace procedure proc02 as

2 begin

3 execute immediate 'create table t_dynamic01 as select * from dual';

4 end;

5 /

Procedure created.

SQL> exec proc02;

PL/SQL procedure successfully completed.

SQL> select * from t_dynamic01;

[/url][url=]

D

-

X

[/url]

---

作者: dbadoudou    时间: 2014-8-13 19:51
哇塞，开发。

很有挑战性，加油小鱼！

---

作者: dbdream    时间: 2014-8-20 10:06


这是权限问题，dbms_lock包默认普通用户是没权限调用的，而dbms_stats这个包也不是普通用户什么都能调用的，之所以普通用户可以调用这个包，是因为这个包不但可以收集数据库级别的统计信息，还可以收集用户和表级别的，那么用户收集自己的统计信息也就是理所当然的了。
看下面的测试
SQL> conn / as sysdba
Connected.
SQL> drop user dbdream cascade;

User dropped.
SQL> grant connect,resource to dbdream identified by dbdream;

Grant succeeded.

SQL> conn dbdream/dbdream
Connected.
SQL> desc dbms_lock
ERROR:
ORA-04043: object "SYS"."DBMS_LOCK" does not exist
desc描述dbms_stats就可以，日志太多这就不贴了，这个普通用户使用这个包收集用户级的统计信息是可以的，收集数据库级就没权限了。
SQL> exec dbms_stats.gather_database_stats();
BEGIN dbms_stats.gather_database_stats(); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Database
ORA-06512: at "SYS.DBMS_STATS", line 13197
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13700
ORA-06512: at "SYS.DBMS_STATS", line 13664
ORA-06512: at line 1
其他用户也是可以的，因为是对dbms_STATS.GATHER_SCHEMA_STATS有权限，但是表级的统计信息，就只能收集自己的，别人的表他没权限。
SQL>  exec dbms_stats.gather_table_stats('DBDREAM','T_TEST');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('STREAM','T_TEST');
BEGIN dbms_stats.gather_table_stats('STREAM','T_TEST'); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "STREAM"."T_TEST", insufficient privileges
or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 13046
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 1
也就是说，如果想用普通用户去收集其他用户下面的表的统计信息，使用schema级的是可以的。
我只是简要测试下，具体细节大家可以自己测试下

---

欢迎光临 168大数据 (http://www.bi168.cn/)

Powered by Discuz! X3.2