一、保证两台笔记本网络互通
关于连接,详情见之前写的:WIFI网络,两台笔记本互联Oracle,一台是11g,一台是12c
现在测试的ip地址:
- A: 10.65.252.84
- B: 10.82.156.248
二、用户简单架构图及权限设计
- A机的DBA账户是shj_dba,B机的DBA账户是c##hachi
- 中间的为连接名,标明连接的名字,对应权限的用户使用对方跟自己权限相同的账号,通过连接名访问对方数据库。
- 各个用户的权限
三、表结构
- 这里双方都采用以下语句创建表,表就建立在各自sys创建的DBA的模式下,不建立任何外键和索引,以免发生同步触发器更新异常,初学先入个门吧!
/*==============================================================*/
/* Table: "part" */
/*==============================================================*/
create table "part"
(
"p_id" INTEGER not null,
"p_name" VARCHAR2(10),
"p_size" VARCHAR2(10),
"p_price" FLOAT(10),
"p_desc" VARCHAR2(50),
constraint PK_PART primary key ("p_id")
);
/*==============================================================*/
/* Table: "project" */
/*==============================================================*/
create table "project"
(
"pj_id" INTEGER not null,
"pj_money" FLOAT(10),
"pj_date" DATE,
constraint PK_PROJECT primary key ("pj_id")
);
/*==============================================================*/
/* Table: "staff" */
/*==============================================================*/
create table "staff"
(
"sf_id" INTEGER not null,
"s_id" INTEGER,
"sta_sf_id" INTEGER,
"sf_name" VARCHAR2(10),
"sf_age" SMALLINT,
"sf_xname" VARCHAR2(10),
constraint PK_STAFF primary key ("sf_id")
);
/*==============================================================*/
/* Table: "storage" */
/*==============================================================*/
create table "storage"
(
"s_id" INTEGER not null,
"s_area" FLOAT(10),
"s_phone" VARCHAR2(11),
constraint PK_STORAGE primary key ("s_id")
);
/*==============================================================*/
/* Table: "storage_info" */
/*==============================================================*/
create table "storage_info"
(
"s_id" INTEGER not null,
"p_id" INTEGER not null,
"sf_num" INTEGER,
constraint PK_STORAGE_INFO primary key ("s_id", "p_id")
);
/*==============================================================*/
/* Table: "supplier" */
/*==============================================================*/
create table "supplier"
(
"sp_id" INTEGER not null,
"sp_name" VARCHAR2(10),
"sp_address" VARCHAR2(30),
"sp_phone" VARCHAR2(11),
"sp_acount" VARCHAR2(15),
constraint PK_SUPPLIER primary key ("sp_id")
);
/*==============================================================*/
/* Table: "supply_info" */
/*==============================================================*/
create table "supply_info"
(
"p_id" INTEGER not null,
"pj_id" INTEGER not null,
"sp_id" INTEGER not null,
"sp_num" INTEGER,
constraint PK_SUPPLY_INFO primary key ("p_id", "pj_id", "sp_id")
);
四、sys授予系统权限,创建DBA账户
-------------------------- 一些可能用得上的查询调试语句 ------------------------------------------
-- 命令行查看是否有远程连接权限
select * from user_sys_privs where privilege like upper('%DATABASE LINK%');
-- 命令行窗口使用system管理员去grant授权远程连接
grant CREATE DATABASE LINK to public;
-- 查看远程连接
select * from dba_db_links;
-- 创建同义词
create public synonym stu for student;
select * from stu;
--删除同义词
drop synonym remote ;
-- 删除远程连接
drop database link linkname;
-- ORACLE 查找所有同义词
SELECT * FROM SYS.ALL_SYNONYMS t WHERE t.owner in ('C##HACHI');
----------------------------------------------------------------------------------
------------------------------------ A机 ----------------------------------------------
conn sys/a123456 as sysdba;
-- 授予所有用户连接权限
grant connect to public;
-- 授予所有用户创建远程连接权限,这样所有用户都可以创建和删除远程连接 database link
grant CREATE DATABASE LINK to public;
-- 授予所有用户创建同义词的权限,这样所有用户都可以创建和删除同义词 synonym
grant CREATE synonym to public;
-- DBA用户创建及授权
create user shj_dba IDENTIFIED by a123456;
grant dba,connect,resource to shj_dba;
commit;
------------------------------------ B机 ----------------------------------------------
conn sys/a123456 as sysdba;
-- 授予所有用户连接权限
grant connect to public;
-- 授予所有用户创建远程连接权限,这样所有用户都可以创建和删除远程连接 database link
grant CREATE DATABASE LINK to public;
-- 授予所有用户创建同义词的权限,这样所有用户都可以创建和删除同义词 synonym
grant CREATE synonym to public;
-- DBA用户创建及授权
create user c##hachi IDENTIFIED by a123456;
grant dba,connect,resource to c##hachi;
五、双方各自创建的DBA账户再创建其他用户,以及私有同义词
------------------------------------ B机 ----------------------------------------------
conn c##hachi/a123456;
-- 供应商用户创建及授权
create user c##supplier IDENTIFIED by a123456 ;
grant SELECT,INSERT,DELETE,UPDATE on "supplier" to c##supplier;
grant SELECT,INSERT,DELETE,UPDATE on "supply_info" to c##supplier;
grant SELECT on "part" to c##supplier;
grant SELECT on "project" to c##supplier;
-- 领导用户创建及授权
create user c##leader IDENTIFIED by a123456 ;
grant SELECT,INSERT,DELETE,UPDATE on "staff" to c##leader;
grant SELECT on "supplier" to c##leader;
grant SELECT on "supply_info" to c##leader;
grant SELECT on "project" to c##leader;
grant SELECT,INSERT,DELETE,UPDATE on "part" to c##leader;
grant SELECT,UPDATE on "storage" to c##leader;
grant SELECT,INSERT,DELETE,UPDATE on "storage_info" to c##leader;
-- 职工用户创建及授权
create user c##staff IDENTIFIED by a123456;
grant SELECT on "staff" to c##staff;
grant SELECT on "storage" to c##staff;
-- 创建4个不同用户权限的远程连接
conn c##hachi/a123456;
-- 本机DBA用户c##hachi,使用对方的DBA账户登录远程连接
create database link shj0dba connect to shj_dba identified by "a123456" using '10.65.252.84/orcl';
-- 创建同义词
create synonym remote_part for "part"@shj0dba;
create synonym remote_project for "project"@shj0dba;
create synonym remote_staff for "staff"@shj0dba;
create synonym remote_storage for "storage"@shj0dba;
create synonym remote_storage_info for "storage_info"@shj0dba;
create synonym remote_supplier for "supplier"@shj0dba;
create synonym remote_supply_info for "supply_info"@shj0dba;
select * from remote_supply_info;
commit;
conn c##leader/a123456;
-- 本机领导用户c##leader,使用对方的领导账户登录远程连接
create database link shj0leader connect to shj_leader identified by "a123456" using '10.65.252.84/orcl';
create synonym remote_part for shj_dba."part"@shj0leader;
create synonym remote_project for shj_dba."project"@shj0leader;
create synonym remote_staff for shj_dba."staff"@shj0leader;
create synonym remote_storage for shj_dba."storage"@shj0leader;
create synonym remote_storage_info for shj_dba."storage_info"@shj0leader;
create synonym remote_supplier for shj_dba."supplier"@shj0leader;
create synonym remote_supply_info for shj_dba."supply_info"@shj0leader;
select * from remote_supply_info;
commit;
conn c##supplier/a123456;
-- 本机供应商用户c##supplier,使用对方的供应商账户登录远程连接
create database link shj0supplier connect to shj_supplier identified by "a123456" using '10.65.252.84/orcl';
create synonym remote_part for shj_dba."part"@shj0supplier;
create synonym remote_project for shj_dba."project"@shj0supplier;
create synonym remote_supplier for shj_dba."supplier"@shj0supplier;
create synonym remote_supply_info for shj_dba."supply_info"@shj0supplier;
select * from remote_supply_info;
commit;
conn c##staff/a123456;
-- 本机员工用户c##staff,使用对方的员工账户登录远程连接
create database link shj0staff connect to shj_staff identified by "a123456" using '10.65.252.84/orcl';
create synonym remote_staff for shj_dba."staff"@shj0staff ;
create synonym remote_storage for shj_dba."storage"@shj0staff;
select * from remote_staff;
commit;
------------------------------------ A机 ----------------------------------------------
conn shj_dba/a123456;
-- 供应商用户创建及授权
create user shj_supplier IDENTIFIED by a123456 ;
grant SELECT,INSERT,DELETE,UPDATE on "supplier" to shj_supplier;
grant SELECT,INSERT,DELETE,UPDATE on "supply_info" to shj_supplier;
grant SELECT on "part" to shj_supplier;
grant SELECT on "project" to shj_supplier;
-- 领导用户创建及授权
create user shj_leader IDENTIFIED by a123456 ;
grant SELECT,INSERT,DELETE,UPDATE on "staff" to shj_leader;
grant SELECT on "supplier" to shj_leader;
grant SELECT on "supply_info" to shj_leader;
grant SELECT on "project" to shj_leader;
grant SELECT,INSERT,DELETE,UPDATE on "part" to shj_leader;
grant SELECT,UPDATE on "storage" to shj_leader;
grant SELECT,INSERT,DELETE,UPDATE on "storage_info" to shj_leader;
-- 职工用户创建及授权
create user shj_staff IDENTIFIED by a123456;
grant SELECT on "staff" to shj_staff;
grant SELECT on "storage" to shj_staff;
commit;
-- 删除同名同义词与连接
DROP SYNONYM remote_part;
DROP SYNONYM remote_project;
DROP SYNONYM remote_staff;
DROP SYNONYM remote_storage;
DROP SYNONYM remote_storage_info;
DROP SYNONYM remote_supplier;
DROP SYNONYM remote_supply_info;
drop database link c##0hachi;
-- 创建4个不同用户权限的远程连接
conn shj_dba/a123456;
-- 本机DBA用户shj_dba,使用对方的DBA账户登录远程连接
create database link c##0hachi connect to "c##hachi" identified by "a123456" using '10.82.156.248/orcl';
-- 创建同义词
create synonym remote_part for "part"@c##0hachi;
create synonym remote_project for "project"@c##0hachi;
create synonym remote_staff for "staff"@c##0hachi;
create synonym remote_storage for "storage"@c##0hachi;
create synonym remote_storage_info for "storage_info"@c##0hachi;
create synonym remote_supplier for "supplier"@c##0hachi;
create synonym remote_supply_info for "supply_info"@c##0hachi;
select * from remote_supply_info;
commit;
conn shj_leader/a123456;
-- 本机领导用户shj_leader,使用对方的领导账户登录远程连接
create database link c##0leader connect to "c##leader" identified by "a123456" using '10.82.156.248/orcl';
create synonym remote_part for c##hachi."part"@c##0leader;
create synonym remote_project for c##hachi."project"@c##0leader;
create synonym remote_staff for c##hachi."staff"@c##0leader;
create synonym remote_storage for c##hachi."storage"@c##0leader;
create synonym remote_storage_info for c##hachi."storage_info"@c##0leader;
create synonym remote_supplier for c##hachi."supplier"@c##0leader;
create synonym remote_supply_info for c##hachi."supply_info"@c##0leader;
select * from remote_supply_info;
commit;
conn shj_supplier/a123456;
-- 本机供应商用户shj_supplier,使用对方的供应商账户登录远程连接
create database link c##0supplier connect to "c##supplier" identified by "a123456" using '10.82.156.248/orcl';
create synonym remote_part for c##hachi."part"@c##0supplier;
create synonym remote_project for c##hachi."project"@c##0supplier;
create synonym remote_supplier for c##hachi."supplier"@c##0supplier;
create synonym remote_supply_info for c##hachi."supply_info"@c##0supplier;
select * from remote_supply_info;
commit;
conn shj_staff/a123456;
-- 本机员工用户shj_staff,使用对方的员工账户登录远程连接
create database link c##0staff connect to "c##staff" identified by "a123456" using '10.82.156.248/orcl';
create synonym remote_staff for c##hachi."staff"@c##0staff ;
create synonym remote_storage for c##hachi."storage"@c##0staff;
select * from remote_staff;
commit;
因为使用的wifi网络,采用DHCP协议动态分配IP地址,所以每一次两台笔记本的IP地址是不一样的,所以连接得删了再重新建立,同义词不用删了重新建立,重新创建一下database link连接就行了。
我这里建的都是私有连接,私有同义词,只有创建该连接的用户才可以使用,其他用户没有使用权限,是透明的,所以设置同名的同义词不影响。
- 同义词创建后就可以使用同义词去增删改查对方的表了,这里贴两张图,各自私有建的连接,不影响各自的同名同义词。
六、创建同步触发器与存储过程
- 可能由于版本问题,B机12C可以创建触发器同步备份A机的上的表,而A机不行。同时B机创建存储过程,执行存储过程,会触发B机的触发器去同步更新A机上的表。
- A机可以使用连接更新B机的数据,同时触发触发器更新A机自己的表。
下面以图文结合方式说明以上两点。
这里触发器和存储过程都建在c##hachi用户模式下。
6.1 触发器
触发器代码:
------------------------------------ B机 c##hachi模式下创建 ----------------------------------------------
-- 增删改part
drop trigger insert_update_delete_part;
create or replace trigger insert_update_delete_part after insert or update or delete
on "part" for each row
begin
if inserting then
insert into remote_part("p_id","p_name","p_size","p_price","p_desc") values(:new."p_id",:new."p_name",:new."p_size",:new."p_price",:new."p_desc");
elsif updating then
update remote_part set remote_part."p_name"=:new."p_name",remote_part."p_size"=:new."p_size",remote_part."p_price"=:new."p_price",remote_part."p_desc"=:new."p_desc" where remote_part."p_id"=:new."p_id";
elsif deleting then
delete from remote_part where remote_part."p_id"=:old."p_id";
end if;
end;
insert into "part" values(9,'eeeee',22,12,'eeeeee');
update "part" set "p_name"='abc',"p_size"=11,"p_price"=11,"p_desc"='abc' where "p_id"=9;
delete from "part" where "p_id"=9;
-- 增删改project
drop trigger insert_update_delete_project;
create or replace trigger insert_update_delete_project after insert or update or delete
on "project" for each row
begin
if inserting then
insert into remote_project("pj_id","pj_money","pj_date") values(:new."pj_id",:new."pj_money",:new."pj_date");
elsif updating then
update remote_project set remote_project."pj_money"=:new."pj_money",remote_project."pj_date"=:new."pj_date" where remote_project."pj_id"=:new."pj_id";
elsif deleting then
delete from remote_project where remote_project."pj_id"=:old."pj_id";
end if;
end;
insert into "project" values(1,100,null);
update "project" set "pj_money"=200,"pj_date"=null where "pj_id"=1;
delete from "project" where "pj_id"=1;
-- 增删改staff
drop trigger insert_update_delete_staff;
create or replace trigger insert_update_delete_staff after insert or update or delete
on "staff" for each row
begin
if inserting then
insert into remote_staff("sf_id","s_id","sta_sf_id","sf_name","sf_age","sf_xname") values(:new."sf_id",:new."s_id",:new."sta_sf_id",:new."sf_name",:new."sf_age",:new."sf_xname");
elsif updating then
update remote_staff set remote_staff."sf_name"=:new."sf_name",remote_staff."sf_age"=:new."sf_age",remote_staff."sf_xname"=:new."sf_xname" where remote_staff."sf_id"=:new."sf_id";
elsif deleting then
delete from remote_staff where remote_staff."sf_id"=:old."sf_id";
end if;
end;
insert into "staff" values(1,1,1,'Hachi',10,'Bit');
update "staff" set "sf_name"='BitHachi',"sf_xname"='bithachi' where "sf_id"=1;
delete from "staff" where "sf_id"=1;
-- 增删改storage
drop trigger insert_update_delete_storage;
create or replace trigger insert_update_delete_storage after insert or update or delete
on "storage" for each row
begin
if inserting then
insert into remote_storage("s_id","s_area","s_phone") values(:new."s_id",:new."s_area",:new."s_phone");
elsif updating then
update remote_storage set remote_storage."s_area"=:new."s_area",remote_storage."s_phone"=:new."s_phone" where remote_storage."s_id"=:new."s_id";
elsif deleting then
delete from remote_storage where remote_storage."s_id"=:old."s_id";
end if;
end;
insert into "storage" values(1,100,'17683738511');
update "storage" set "s_area"=200,"s_phone"='17683838555' where "s_id"=1;
delete from "storage" where "s_id"=1;
-- 增删改storage_info
drop trigger insert_update_delete_storage_info;
create or replace trigger insert_update_delete_storage_info after insert or update or delete
on "storage_info" for each row
begin
if inserting then
insert into remote_storage_info("s_id","p_id","sf_num") values(:new."s_id",:new."p_id",:new."sf_num");
elsif updating then
update remote_storage_info set remote_storage_info."sf_num"=:new."sf_num" where remote_storage_info."s_id"=:new."s_id" and remote_storage_info."p_id"=:new."p_id";
elsif deleting then
delete from remote_storage_info where remote_storage_info."s_id"=:old."s_id" and remote_storage_info."p_id"=:old."p_id";
end if;
end;
insert into "storage_info" values(1,1,100);
update "storage_info" set "sf_num"=200 where "s_id"=1 and "p_id"=1;
delete from "storage_info" where "s_id"=1 and "p_id"=1;
-- 增删改supplier
drop trigger insert_update_delete_supplier;
create or replace trigger insert_update_delete_supplier after insert or update or delete
on "supplier" for each row
begin
if inserting then
insert into remote_supplier("sp_id","sp_name","sp_address","sp_phone","sp_acount") values(:new."sp_id",:new."sp_name",:new."sp_address",:new."sp_phone",:new."sp_acount");
elsif updating then
update remote_supplier set remote_supplier."sp_name"=:new."sp_name" where remote_supplier."sp_id"=:new."sp_id";
elsif deleting then
delete from remote_supplier where remote_supplier."sp_id"=:old."sp_id" ;
end if;
end;
insert into "supplier" values(1,'BitHachi','中国湖北','17683738511','1000001');
update "supplier" set "sp_name"='Hachi' where "sp_id"=1 ;
delete from "supplier" where "sp_id"=1;
-- 增删改supply_info
drop trigger insert_update_delete_supply_info;
create or replace trigger insert_update_delete_supply_info after insert or update or delete
on "supply_info" for each row
begin
if inserting then
insert into remote_supply_info("p_id","pj_id","sp_id","sp_num") values(:new."p_id",:new."pj_id",:new."sp_id",:new."sp_num");
elsif updating then
update remote_supply_info set remote_supply_info."sp_num"=:new."sp_num" where remote_supply_info."p_id"=:new."p_id" and remote_supply_info."pj_id"=:new."pj_id" and remote_supply_info."sp_id"=:new."sp_id";
elsif deleting then
delete from remote_supply_info where remote_supply_info."p_id"=:old."p_id" and remote_supply_info."pj_id"=:old."pj_id" and remote_supply_info."sp_id"=:old."sp_id";
end if;
end;
insert into "supply_info" values(1,1,1,100);
update "supply_info" set "sp_num"=200 where "p_id"=1 and "pj_id"=1 and "sp_id"=1 ;
delete from "supply_info" where "p_id"=1 and "pj_id"=1 and "sp_id"=1 ;
-
这里触发器很多,我就演示一个。见下图:
这张图是B机创建一个触发器实现remote_part同义词表示的A机的part表的同步更新。
现在我们在A机上使用同义词进行插入、修改、删除,会触发B机的触发器同步更新B机和A机的表数据。
6.3 存储过程
这里B机创建了一个存储过程,B机调用存储过程可以实现B机和A机的同步更新。
先贴代码再放截图流程
-- 查询数据
drop procedure select_data;
create procedure select_data
(var_datas out sys_refcursor) AS
begin
OPEN var_datas for select * from "part";
end select_data;
var datas refcursor;
exec select_data(:datas);
print:datas;
-- 增加数据
drop procedure insert_data;
create procedure insert_data
(var_id "part"."p_id"%TYPE,var_name "part"."p_name"%TYPE,var_size "part"."p_size"%TYPE,var_price "part"."p_price"%TYPE,var_desc "part"."p_desc"%TYPE)
AS
begin
INSERT INTO "part" values(var_id,var_name,var_size,var_price,var_desc);
end insert_data;
exec insert_data(2,'ANIVJO',22,15,'DFGDG');
-- 修改数据
drop procedure update_data;
create procedure update_data
(var_id "part"."p_id"%TYPE,var_name "part"."p_name"%TYPE)
AS
begin
UPDATE "part" SET "p_name"=var_name WHERE "p_id"=var_id;
end update_data;
exec update_data(2,'BitHachi');
-- 删除数据
drop procedure delete_data;
create procedure delete_data
(var_id "part"."p_id"%TYPE)
AS
begin
DELETE FROM "part" WHERE "p_id"=var_id;
end delete_data;
exec delete_data(2);