由于项目需要,需在存储过程中连接远程数据库完成数据的抽取工作。做个记录^^
在此文件下
G:/oracle_test/product/10.2.0/db_1/NETWORK/ADMIN/tnsnames.ora
添加:
link =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = 数据库IP)(PORT = 1521)))(FAILOVER=yes)(LOAD_BALANCE=yes)
(CONNECT_DATA =(SERVER = DEDICATED)
(SERVICE_NAME = 数据库SID)(failover_mode=(type=select)(method=basic))
)
)
查看当前用户有哪些权限
select * from user_sys_privs;
使用DBA登陆
创建DBLINK的权限:create database link to sampledata;
删除DBLINK的权限:drop database link from sampledata;
用户操作存储过程权限:
grant execute any procedure to sampledata;
grant alter any procedure to sampledata;
grant create any procedure to sampledata;
grant drop any procedure to sampledata;
使用sampledata用户登陆创建DBLINK,不能sysdba