再建立一条私有的数据库链CREATE DATABASE LINK ServName CONNECT TO user IDENTIFIED BY password;
to maohaisheng(www.chinaspirit.net) 怎样建立这个私有连接呢? 是在存储过程中用一个动态SQL来连接吗?连接后需要断开吗?如何断开?谢谢。
vsqlstr := 'CREATE DATABASE LINK ServName CONNECT TO username IDENTIFIED BY passwd'; execute immediate vsqlstr;vsqlstr := 'update reg_user_info@ServName'|| ' set fsyncflag = 0'|| ' where fsyncflag = 2'; execute immediate vsqlstr;vsqlstr := 'disconnect'; execute immediate vsqlstr; 执行时,又来了个新问题,连接时错误如下: ORA-01031: insufficient privileges 权限不足,那些权限不足呢?
用户已经有这样的权限了,为什么还是错啊?-- Create the user create user SMS3 identified by default tablespace SMS3 temporary tablespace SMS3_TMP profile DEFAULT quota unlimited on sms3 quota unlimited on sms3_idx quota unlimited on sms3_tmp; -- Grant/Revoke object privileges grant select, insert, update, delete on SYS.DBA_JOBS to SMS3; -- Grant/Revoke role privileges grant connect to SMS3; grant dba to SMS3; grant resource to SMS3; -- Grant/Revoke system privileges grant create any table to SMS3; grant unlimited tablespace to SMS3;
我问一个比较弱的问题啊'CREATE DATABASE LINK ServName CONNECT TO username IDENTIFIED BY passwd';username和password你可别忘了改。 这是异地的表吧reg_user_info 在异地,把这个表的权限放开,试验一下GOOD LUCK
跟踪发现异常ORA-01031: insufficient privileges是 vsqlstr := 'CREATE DATABASE LINK ServName CONNECT TO username IDENTIFIED BY passwd'; execute immediate vsqlstr; 抛出来的,应该跟异地表reg_user_info开放什么权限无关的。
CONNECT TO user IDENTIFIED BY password;
是在存储过程中用一个动态SQL来连接吗?连接后需要断开吗?如何断开?谢谢。
execute immediate vsqlstr;vsqlstr := 'update reg_user_info@ServName'||
' set fsyncflag = 0'||
' where fsyncflag = 2';
execute immediate vsqlstr;vsqlstr := 'disconnect';
execute immediate vsqlstr;
执行时,又来了个新问题,连接时错误如下:
ORA-01031: insufficient privileges
权限不足,那些权限不足呢?
create user SMS3
identified by
default tablespace SMS3
temporary tablespace SMS3_TMP
profile DEFAULT
quota unlimited on sms3
quota unlimited on sms3_idx
quota unlimited on sms3_tmp;
-- Grant/Revoke object privileges
grant select, insert, update, delete on SYS.DBA_JOBS to SMS3;
-- Grant/Revoke role privileges
grant connect to SMS3;
grant dba to SMS3;
grant resource to SMS3;
-- Grant/Revoke system privileges
grant create any table to SMS3;
grant unlimited tablespace to SMS3;
这是异地的表吧reg_user_info
在异地,把这个表的权限放开,试验一下GOOD LUCK
vsqlstr := 'CREATE DATABASE LINK ServName CONNECT TO username IDENTIFIED BY passwd';
execute immediate vsqlstr;
抛出来的,应该跟异地表reg_user_info开放什么权限无关的。