- 浏览: 45699 次
- 性别:
- 来自: 北京
最新评论
(172)上配置用于源的UDB_V2和UDB的tnsname.ora,以便直接在172上分别操控源和目的:
UDB_V2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.91)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = smsc)
)
)
UDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = udb)
)
)
1,在源数据库上登录,查询
sqlplus udb_v2/udb@udb
执行:
select * from global_name;
得到
SMSC.REGRESS.RDBMS.DEV.US.ORACLE.COM , 记住数据库名:smsc
2,在目的数据库上登录:
sqlplus 'udb_v2/udb123#@UDB'
执行:
create database link smsc connect to udb_v2 identified by udb using 'test';
建立dblink
其中smsc为源数据库的数据库名,test为服务器端的tnsname!
test为tnsname.ora中连接源的服务名
检查下dblink是否可用:
select count(*) from device@smsc;
看是否能查到结果
3,再源数据库上登录
建立快照日志:
create snapshot log on userid;
create snapshot log on service;
4,在目的数据库上登录
建立快照,会很长时间,这时数据会导入
create snapshot sn_userid as select * from userid@smsc;
create snapshot sn_service as select * from service@smsc;
将快照插入userid和service表中
insert into userid select * from sn_userid;
insert into service select * from sn_service;
建立快照的触发器
sn_userid:
CREATE OR REPLACE TRIGGER UDB_V2.TRI_SN_USERID_AFR
AFTER DELETE OR INSERT OR UPDATE
ON UDB_V2.SN_USERID REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
tmp_id varchar2(40);
tmp_ss integer;
begin
dbms_output.put_line('begin triger on sn_userid');
if inserting then
dbms_output.put_line('insert');
for p in(
select user_id into tmp_id from userid where user_id=:new.user_id
)
loop
tmp_id:=p.user_id;
end loop;
if (tmp_id is null) then
INSERT INTO USERID
(
USER_ID, PASSWD, USERID_STATUS,
CREATE_TIME, EXT_PASSWD, TMP_PASSWD,
TMP_CREATE_TIME, TMP_EXIPRATION_TIME, OTP_TOKENID,
SEX, EMAIL, LAST_MODIFY_TIME,
PROVINCE_NO, CITY_NO, CERTIFICATE_TYPE,
CERTIFICATE_NO, PAY_TYPE, PRE_PAY_SYSTEM_NO,
PAY_EFFECT_MODE, PAY_EFFECT_TIME, SRC_DEVICE_NO,
ACTIVE_STATUS, UPDATE_FLAG, USERID_TYPE,
AREACODE, CUSTOMERID, USERNAME,
SERSET_TYPE, BINDING_TELE_NO, P_USERID,
ALIAS, ACCOUNT_ACT, ACCOUNT_TYPE,
ACCOUNT_STATE, ACCOUNT_RETRY, ACCOUNT_RETRYTIME,
PASSWD_ACT, USERID_LOGIN_STATUS, USERIDSTATUS_REFTIME,
MOBILE_PHONE, SSO_FLAG, UIM,
CUSTOMERFLAG, PASSWD2, P_USERID_PASSPORT,
USERID_STATUS_EXT
)
VALUES
(
:NEW.USER_ID, :NEW.PASSWD, :NEW.USERID_STATUS,
:NEW.CREATE_TIME, :NEW.EXT_PASSWD, :NEW.TMP_PASSWD,
:NEW.TMP_CREATE_TIME, :NEW.TMP_EXIPRATION_TIME, :NEW.OTP_TOKENID,
:NEW.SEX, :NEW.EMAIL, :NEW.LAST_MODIFY_TIME,
:NEW.PROVINCE_NO, :NEW.CITY_NO, :NEW.CERTIFICATE_TYPE,
:NEW.CERTIFICATE_NO, :NEW.PAY_TYPE, :NEW.PRE_PAY_SYSTEM_NO,
:NEW.PAY_EFFECT_MODE, :NEW.PAY_EFFECT_TIME, :NEW.SRC_DEVICE_NO,
:NEW.ACTIVE_STATUS, :NEW.UPDATE_FLAG, :NEW.USERID_TYPE,
:NEW.AREACODE, :NEW.CUSTOMERID, :NEW.USERNAME,
:NEW.SERSET_TYPE, :NEW.BINDING_TELE_NO, :NEW.P_USERID,
:NEW.ALIAS, :NEW.ACCOUNT_ACT, :NEW.ACCOUNT_TYPE,
:NEW.ACCOUNT_STATE, :NEW.ACCOUNT_RETRY, :NEW.ACCOUNT_RETRYTIME,
:NEW.PASSWD_ACT, :NEW.USERID_LOGIN_STATUS, :NEW.USERIDSTATUS_REFTIME,
:NEW.MOBILE_PHONE, :NEW.SSO_FLAG, :NEW.UIM,
:NEW.CUSTOMERFLAG, :NEW.PASSWD2,:NEW.P_USERID_PASSPORT,
:NEW.USERID_STATUS_EXT
);
end if;
end if;
if updating then
dbms_output.put_line('updated');
for p in(select * from userid where user_id=:OLD.user_id)
loop
if (
(p.PASSWD != :new.PASSWD)
OR (p.USERID_STATUS != :new.USERID_STATUS)
OR (p.CREATE_TIME != :new.CREATE_TIME)
OR (p.EXT_PASSWD != :new.EXT_PASSWD)
OR (p.TMP_PASSWD != :new.TMP_PASSWD)
OR (p.TMP_EXIPRATION_TIME != :new.TMP_EXIPRATION_TIME)
OR (p.OTP_TOKENID != :new.OTP_TOKENID)
OR (p.SEX != :new.SEX)
OR (p.EMAIL != :new.EMAIL)
OR (p.LAST_MODIFY_TIME != :new.LAST_MODIFY_TIME)
OR (p.PROVINCE_NO != :new.PROVINCE_NO)
OR (p.CITY_NO != :new.CITY_NO)
OR (p.CERTIFICATE_TYPE != :new.CERTIFICATE_TYPE)
OR (p.CERTIFICATE_NO != :new.CERTIFICATE_NO)
OR (p.PAY_TYPE != :new.PAY_TYPE)
OR (p.PRE_PAY_SYSTEM_NO != :new.PRE_PAY_SYSTEM_NO)
OR (p.PAY_EFFECT_MODE != :new.PAY_EFFECT_MODE)
OR (p.PAY_EFFECT_TIME != :new.PAY_EFFECT_TIME)
OR (p.SRC_DEVICE_NO != :new.SRC_DEVICE_NO)
OR (p.ACTIVE_STATUS != :new.ACTIVE_STATUS)
OR (p.UPDATE_FLAG != :new.UPDATE_FLAG)
OR (p.USERID_TYPE != :new.USERID_TYPE)
OR (p.AREACODE != :new.AREACODE)
OR (p.CUSTOMERID != :new.CUSTOMERID)
OR (p.USERNAME != :new.USERNAME)
OR (p.SERSET_TYPE != :new.SERSET_TYPE)
OR (p.BINDING_TELE_NO != :new.BINDING_TELE_NO)
OR (p.P_USERID != :new.P_USERID)
OR (p.ALIAS != :new.ALIAS)
OR (p.ACCOUNT_ACT != :new.ACCOUNT_ACT)
OR (p.ACCOUNT_TYPE != :new.ACCOUNT_TYPE)
OR (p.ACCOUNT_STATE != :new.ACCOUNT_STATE)
OR (p.ACCOUNT_RETRY != :new.ACCOUNT_RETRY)
OR (p.ACCOUNT_RETRYTIME != :new.ACCOUNT_RETRYTIME)
OR (p.PASSWD_ACT != :new.PASSWD_ACT)
OR (p.USERID_LOGIN_STATUS != :new.USERID_LOGIN_STATUS)
OR (p.USERIDSTATUS_REFTIME != :new.USERIDSTATUS_REFTIME)
OR (p.MOBILE_PHONE != :new.MOBILE_PHONE)
OR (p.SSO_FLAG != :new.SSO_FLAG)
OR (p.UIM != :new.UIM)
OR (p.CUSTOMERFLAG != :new.CUSTOMERFLAG)
OR (p.PASSWD2 != :new.PASSWD2)
OR (p.P_USERID_PASSPORT != :new.P_USERID_PASSPORT)
OR (p.USERID_STATUS_EXT != :new.USERID_STATUS_EXT)
) then
UPDATE USERID
SET USER_ID = :new.USER_ID,
PASSWD = :new.PASSWD,
USERID_STATUS = :new.USERID_STATUS,
CREATE_TIME = :new.CREATE_TIME,
EXT_PASSWD = :new.EXT_PASSWD,
TMP_PASSWD = :new.TMP_PASSWD,
TMP_CREATE_TIME = :new.TMP_CREATE_TIME,
TMP_EXIPRATION_TIME = :new.TMP_EXIPRATION_TIME,
OTP_TOKENID = :new.OTP_TOKENID,
SEX = :new.SEX,
EMAIL = :new.EMAIL,
LAST_MODIFY_TIME = :new.LAST_MODIFY_TIME,
PROVINCE_NO = :new.PROVINCE_NO,
CITY_NO = :new.CITY_NO,
CERTIFICATE_TYPE = :new.CERTIFICATE_TYPE,
CERTIFICATE_NO = :new.CERTIFICATE_NO,
PAY_TYPE = :new.PAY_TYPE,
PRE_PAY_SYSTEM_NO = :new.PRE_PAY_SYSTEM_NO,
PAY_EFFECT_MODE = :new.PAY_EFFECT_MODE,
PAY_EFFECT_TIME = :new.PAY_EFFECT_TIME,
SRC_DEVICE_NO = :new.SRC_DEVICE_NO,
ACTIVE_STATUS = :new.ACTIVE_STATUS,
UPDATE_FLAG = :new.UPDATE_FLAG,
USERID_TYPE = :new.USERID_TYPE,
AREACODE = :new.AREACODE,
CUSTOMERID = :new.CUSTOMERID,
USERNAME = :new.USERNAME,
SERSET_TYPE = :new.SERSET_TYPE,
BINDING_TELE_NO = :new.BINDING_TELE_NO,
P_USERID = :new.P_USERID,
ALIAS = :new.ALIAS,
ACCOUNT_ACT = :new.ACCOUNT_ACT,
ACCOUNT_TYPE = :new.ACCOUNT_TYPE,
ACCOUNT_STATE = :new.ACCOUNT_STATE,
ACCOUNT_RETRY = :new.ACCOUNT_RETRY,
ACCOUNT_RETRYTIME = :new.ACCOUNT_RETRYTIME,
PASSWD_ACT = :new.PASSWD_ACT,
USERID_LOGIN_STATUS = :new.USERID_LOGIN_STATUS,
USERIDSTATUS_REFTIME = :new.USERIDSTATUS_REFTIME,
MOBILE_PHONE = :new.MOBILE_PHONE,
SSO_FLAG = :new.SSO_FLAG,
UIM = :new.UIM,
CUSTOMERFLAG = :new.CUSTOMERFLAG,
PASSWD2 = :new.PASSWD2,
P_USERID_PASSPORT = :new.P_USERID_PASSPORT,
USERID_STATUS_EXT = :new.USERID_STATUS_EXT
WHERE USER_ID = :new.USER_ID
;
end if;
end loop;
end if;
if deleting then
dbms_output.put_line('deleted');
delete from service where user_id=:old.user_id;
delete from userid where user_id=:old.user_id;
end if;
dbms_output.put_line('end triger on sn_userid');
end TRI_SN_USERID_AFR;
/
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
service:
CREATE OR REPLACE TRIGGER UDB_V2.TRI_SN_SERVICE_AFR
AFTER DELETE OR INSERT OR UPDATE
ON UDB_V2.SN_SERVICE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
tmp_ss INTEGER;
begin
dbms_output.put_line('begin triger on sn_service');
if inserting then
dbms_output.put_line('insert');
for p in(
select SS_INDEX into tmp_ss from SERVICE where SS_INDEX=:new.SS_INDEX
)
loop
tmp_ss:=p.SS_INDEX;
end loop;
if (tmp_ss is null) then
dbms_output.put_line('in insert service');
INSERT INTO SERVICE (
SS_INDEX, USER_ID, SS_PASSWORD,
SS_STATUS, SRC_DEVICE_NO, CREATE_TIME,
LAST_MODIFY_TIME, AUTH_SRC, REG_EFFECT_MODE,
REG_EFFECT_TIME, BIND_TYPE, BIND_EFFECT_MODE,
BIND_EFFECT_TIME, SS_TYPE, USERID_SS_LOGIN_STATUS,
SS_ID, SS_PASSWD_ACT, SS_ACTIVE_FLAG
)
VALUES (
:NEW.SS_INDEX, :NEW.USER_ID, :NEW.SS_PASSWORD,
:NEW.SS_STATUS, :NEW.SRC_DEVICE_NO, :NEW.CREATE_TIME,
:NEW.LAST_MODIFY_TIME, :NEW.AUTH_SRC, :NEW.REG_EFFECT_MODE,
:NEW.REG_EFFECT_TIME, :NEW.BIND_TYPE, :NEW.BIND_EFFECT_MODE,
:NEW.BIND_EFFECT_TIME, :NEW.SS_TYPE, :NEW.USERID_SS_LOGIN_STATUS ,
:NEW.SS_ID, :NEW.SS_PASSWD_ACT, :NEW.SS_ACTIVE_FLAG
);
end if;
end if;
if updating then
dbms_output.put_line('updated');
for p in(select * from SERVICE where SS_INDEX=:OLD.SS_INDEX)
loop
if (
(P.USER_ID != :NEW.USER_ID)
OR (P.SS_PASSWORD != :NEW.SS_PASSWORD)
OR (P.SS_STATUS != :NEW.SS_STATUS)
OR (P.SRC_DEVICE_NO != :NEW.SRC_DEVICE_NO)
OR (P.CREATE_TIME != :NEW.CREATE_TIME)
OR (P.LAST_MODIFY_TIME != :NEW.LAST_MODIFY_TIME)
OR (P.AUTH_SRC != :NEW.AUTH_SRC)
OR (P.REG_EFFECT_MODE != :NEW.REG_EFFECT_MODE)
OR (P.REG_EFFECT_TIME != :NEW.REG_EFFECT_TIME)
OR (P.BIND_TYPE != :NEW.BIND_TYPE)
OR (P.BIND_EFFECT_MODE != :NEW.BIND_EFFECT_MODE)
OR (P.BIND_EFFECT_TIME != :NEW.BIND_EFFECT_TIME)
OR (P.SS_TYPE != :NEW.SS_TYPE)
OR (P.USERID_SS_LOGIN_STATUS != :NEW.USERID_SS_LOGIN_STATUS)
OR (P.SS_ID != :NEW.SS_ID)
OR (P.SS_PASSWD_ACT != :NEW.SS_PASSWD_ACT)
OR (P.SS_ACTIVE_FLAG != :NEW.SS_ACTIVE_FLAG)
)
then
UPDATE SERVICE
SET
SS_INDEX = :NEW.SS_INDEX,
USER_ID = :NEW.USER_ID,
SS_PASSWORD = :NEW.SS_PASSWORD,
SS_STATUS = :NEW.SS_STATUS,
SRC_DEVICE_NO = :NEW.SRC_DEVICE_NO,
CREATE_TIME = :NEW.CREATE_TIME,
LAST_MODIFY_TIME = :NEW.LAST_MODIFY_TIME,
AUTH_SRC = :NEW.AUTH_SRC,
REG_EFFECT_MODE = :NEW.REG_EFFECT_MODE,
REG_EFFECT_TIME = :NEW.REG_EFFECT_TIME,
BIND_TYPE = :NEW.BIND_TYPE,
BIND_EFFECT_MODE = :NEW.BIND_EFFECT_MODE,
BIND_EFFECT_TIME = :NEW.BIND_EFFECT_TIME,
SS_TYPE = :NEW.SS_TYPE,
USERID_SS_LOGIN_STATUS = :NEW.USERID_SS_LOGIN_STATUS,
SS_ID = :NEW.SS_ID,
SS_PASSWD_ACT = :NEW.SS_PASSWD_ACT,
SS_ACTIVE_FLAG = :NEW.SS_ACTIVE_FLAG
WHERE SS_INDEX = :new.SS_INDEX
;
end if;
end loop;
end if;
if deleting then
dbms_output.put_line('deleted');
delete from service where SS_INDEX=:old.SS_INDEX;
end if;
dbms_output.put_line('end triger on sn_service');
end TRI_SN_SERVICE_AFR;
/
修改快照刷新时间
Alter snapshot sn_userid refresh fast Start with sysdate+2/(24) next sysdate+1/(24);
Alter snapshot sn_service refresh fast Start with sysdate+2/(24) next sysdate+1/(24);
首次刷新在30秒之后,以后每分钟刷新一次
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
附件:
1.查看快照最后一次刷新时间
SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;
2.查看快照下次执行时间
select last_date,next_date,what from user_jobs order by next_date;
3.手动执行更新
EXEC DBMS_SNAPSHOT.REFRESH ('sn_service','F');
手动刷新方式1
begin
dbms_refresh.refresh('sn_service');
end;
手动刷新方式2
EXEC DBMS_SNAPSHOT.REFRESH('sn_service','F'); //第一个参数是快照名,第二个参数 F 是快速刷新 C 是完全刷新.
4.删除快照日志(在源上)
drop snapshot log on userid;
drop snapshot log on service;
5删除掉快照(在目的上)
drop snapshot sn_userid;
drop snapshot sn_service;
UDB_V2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.91)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = smsc)
)
)
UDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = udb)
)
)
1,在源数据库上登录,查询
sqlplus udb_v2/udb@udb
执行:
select * from global_name;
得到
SMSC.REGRESS.RDBMS.DEV.US.ORACLE.COM , 记住数据库名:smsc
2,在目的数据库上登录:
sqlplus 'udb_v2/udb123#@UDB'
执行:
create database link smsc connect to udb_v2 identified by udb using 'test';
建立dblink
其中smsc为源数据库的数据库名,test为服务器端的tnsname!
test为tnsname.ora中连接源的服务名
检查下dblink是否可用:
select count(*) from device@smsc;
看是否能查到结果
3,再源数据库上登录
建立快照日志:
create snapshot log on userid;
create snapshot log on service;
4,在目的数据库上登录
建立快照,会很长时间,这时数据会导入
create snapshot sn_userid as select * from userid@smsc;
create snapshot sn_service as select * from service@smsc;
将快照插入userid和service表中
insert into userid select * from sn_userid;
insert into service select * from sn_service;
建立快照的触发器
sn_userid:
CREATE OR REPLACE TRIGGER UDB_V2.TRI_SN_USERID_AFR
AFTER DELETE OR INSERT OR UPDATE
ON UDB_V2.SN_USERID REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
tmp_id varchar2(40);
tmp_ss integer;
begin
dbms_output.put_line('begin triger on sn_userid');
if inserting then
dbms_output.put_line('insert');
for p in(
select user_id into tmp_id from userid where user_id=:new.user_id
)
loop
tmp_id:=p.user_id;
end loop;
if (tmp_id is null) then
INSERT INTO USERID
(
USER_ID, PASSWD, USERID_STATUS,
CREATE_TIME, EXT_PASSWD, TMP_PASSWD,
TMP_CREATE_TIME, TMP_EXIPRATION_TIME, OTP_TOKENID,
SEX, EMAIL, LAST_MODIFY_TIME,
PROVINCE_NO, CITY_NO, CERTIFICATE_TYPE,
CERTIFICATE_NO, PAY_TYPE, PRE_PAY_SYSTEM_NO,
PAY_EFFECT_MODE, PAY_EFFECT_TIME, SRC_DEVICE_NO,
ACTIVE_STATUS, UPDATE_FLAG, USERID_TYPE,
AREACODE, CUSTOMERID, USERNAME,
SERSET_TYPE, BINDING_TELE_NO, P_USERID,
ALIAS, ACCOUNT_ACT, ACCOUNT_TYPE,
ACCOUNT_STATE, ACCOUNT_RETRY, ACCOUNT_RETRYTIME,
PASSWD_ACT, USERID_LOGIN_STATUS, USERIDSTATUS_REFTIME,
MOBILE_PHONE, SSO_FLAG, UIM,
CUSTOMERFLAG, PASSWD2, P_USERID_PASSPORT,
USERID_STATUS_EXT
)
VALUES
(
:NEW.USER_ID, :NEW.PASSWD, :NEW.USERID_STATUS,
:NEW.CREATE_TIME, :NEW.EXT_PASSWD, :NEW.TMP_PASSWD,
:NEW.TMP_CREATE_TIME, :NEW.TMP_EXIPRATION_TIME, :NEW.OTP_TOKENID,
:NEW.SEX, :NEW.EMAIL, :NEW.LAST_MODIFY_TIME,
:NEW.PROVINCE_NO, :NEW.CITY_NO, :NEW.CERTIFICATE_TYPE,
:NEW.CERTIFICATE_NO, :NEW.PAY_TYPE, :NEW.PRE_PAY_SYSTEM_NO,
:NEW.PAY_EFFECT_MODE, :NEW.PAY_EFFECT_TIME, :NEW.SRC_DEVICE_NO,
:NEW.ACTIVE_STATUS, :NEW.UPDATE_FLAG, :NEW.USERID_TYPE,
:NEW.AREACODE, :NEW.CUSTOMERID, :NEW.USERNAME,
:NEW.SERSET_TYPE, :NEW.BINDING_TELE_NO, :NEW.P_USERID,
:NEW.ALIAS, :NEW.ACCOUNT_ACT, :NEW.ACCOUNT_TYPE,
:NEW.ACCOUNT_STATE, :NEW.ACCOUNT_RETRY, :NEW.ACCOUNT_RETRYTIME,
:NEW.PASSWD_ACT, :NEW.USERID_LOGIN_STATUS, :NEW.USERIDSTATUS_REFTIME,
:NEW.MOBILE_PHONE, :NEW.SSO_FLAG, :NEW.UIM,
:NEW.CUSTOMERFLAG, :NEW.PASSWD2,:NEW.P_USERID_PASSPORT,
:NEW.USERID_STATUS_EXT
);
end if;
end if;
if updating then
dbms_output.put_line('updated');
for p in(select * from userid where user_id=:OLD.user_id)
loop
if (
(p.PASSWD != :new.PASSWD)
OR (p.USERID_STATUS != :new.USERID_STATUS)
OR (p.CREATE_TIME != :new.CREATE_TIME)
OR (p.EXT_PASSWD != :new.EXT_PASSWD)
OR (p.TMP_PASSWD != :new.TMP_PASSWD)
OR (p.TMP_EXIPRATION_TIME != :new.TMP_EXIPRATION_TIME)
OR (p.OTP_TOKENID != :new.OTP_TOKENID)
OR (p.SEX != :new.SEX)
OR (p.EMAIL != :new.EMAIL)
OR (p.LAST_MODIFY_TIME != :new.LAST_MODIFY_TIME)
OR (p.PROVINCE_NO != :new.PROVINCE_NO)
OR (p.CITY_NO != :new.CITY_NO)
OR (p.CERTIFICATE_TYPE != :new.CERTIFICATE_TYPE)
OR (p.CERTIFICATE_NO != :new.CERTIFICATE_NO)
OR (p.PAY_TYPE != :new.PAY_TYPE)
OR (p.PRE_PAY_SYSTEM_NO != :new.PRE_PAY_SYSTEM_NO)
OR (p.PAY_EFFECT_MODE != :new.PAY_EFFECT_MODE)
OR (p.PAY_EFFECT_TIME != :new.PAY_EFFECT_TIME)
OR (p.SRC_DEVICE_NO != :new.SRC_DEVICE_NO)
OR (p.ACTIVE_STATUS != :new.ACTIVE_STATUS)
OR (p.UPDATE_FLAG != :new.UPDATE_FLAG)
OR (p.USERID_TYPE != :new.USERID_TYPE)
OR (p.AREACODE != :new.AREACODE)
OR (p.CUSTOMERID != :new.CUSTOMERID)
OR (p.USERNAME != :new.USERNAME)
OR (p.SERSET_TYPE != :new.SERSET_TYPE)
OR (p.BINDING_TELE_NO != :new.BINDING_TELE_NO)
OR (p.P_USERID != :new.P_USERID)
OR (p.ALIAS != :new.ALIAS)
OR (p.ACCOUNT_ACT != :new.ACCOUNT_ACT)
OR (p.ACCOUNT_TYPE != :new.ACCOUNT_TYPE)
OR (p.ACCOUNT_STATE != :new.ACCOUNT_STATE)
OR (p.ACCOUNT_RETRY != :new.ACCOUNT_RETRY)
OR (p.ACCOUNT_RETRYTIME != :new.ACCOUNT_RETRYTIME)
OR (p.PASSWD_ACT != :new.PASSWD_ACT)
OR (p.USERID_LOGIN_STATUS != :new.USERID_LOGIN_STATUS)
OR (p.USERIDSTATUS_REFTIME != :new.USERIDSTATUS_REFTIME)
OR (p.MOBILE_PHONE != :new.MOBILE_PHONE)
OR (p.SSO_FLAG != :new.SSO_FLAG)
OR (p.UIM != :new.UIM)
OR (p.CUSTOMERFLAG != :new.CUSTOMERFLAG)
OR (p.PASSWD2 != :new.PASSWD2)
OR (p.P_USERID_PASSPORT != :new.P_USERID_PASSPORT)
OR (p.USERID_STATUS_EXT != :new.USERID_STATUS_EXT)
) then
UPDATE USERID
SET USER_ID = :new.USER_ID,
PASSWD = :new.PASSWD,
USERID_STATUS = :new.USERID_STATUS,
CREATE_TIME = :new.CREATE_TIME,
EXT_PASSWD = :new.EXT_PASSWD,
TMP_PASSWD = :new.TMP_PASSWD,
TMP_CREATE_TIME = :new.TMP_CREATE_TIME,
TMP_EXIPRATION_TIME = :new.TMP_EXIPRATION_TIME,
OTP_TOKENID = :new.OTP_TOKENID,
SEX = :new.SEX,
EMAIL = :new.EMAIL,
LAST_MODIFY_TIME = :new.LAST_MODIFY_TIME,
PROVINCE_NO = :new.PROVINCE_NO,
CITY_NO = :new.CITY_NO,
CERTIFICATE_TYPE = :new.CERTIFICATE_TYPE,
CERTIFICATE_NO = :new.CERTIFICATE_NO,
PAY_TYPE = :new.PAY_TYPE,
PRE_PAY_SYSTEM_NO = :new.PRE_PAY_SYSTEM_NO,
PAY_EFFECT_MODE = :new.PAY_EFFECT_MODE,
PAY_EFFECT_TIME = :new.PAY_EFFECT_TIME,
SRC_DEVICE_NO = :new.SRC_DEVICE_NO,
ACTIVE_STATUS = :new.ACTIVE_STATUS,
UPDATE_FLAG = :new.UPDATE_FLAG,
USERID_TYPE = :new.USERID_TYPE,
AREACODE = :new.AREACODE,
CUSTOMERID = :new.CUSTOMERID,
USERNAME = :new.USERNAME,
SERSET_TYPE = :new.SERSET_TYPE,
BINDING_TELE_NO = :new.BINDING_TELE_NO,
P_USERID = :new.P_USERID,
ALIAS = :new.ALIAS,
ACCOUNT_ACT = :new.ACCOUNT_ACT,
ACCOUNT_TYPE = :new.ACCOUNT_TYPE,
ACCOUNT_STATE = :new.ACCOUNT_STATE,
ACCOUNT_RETRY = :new.ACCOUNT_RETRY,
ACCOUNT_RETRYTIME = :new.ACCOUNT_RETRYTIME,
PASSWD_ACT = :new.PASSWD_ACT,
USERID_LOGIN_STATUS = :new.USERID_LOGIN_STATUS,
USERIDSTATUS_REFTIME = :new.USERIDSTATUS_REFTIME,
MOBILE_PHONE = :new.MOBILE_PHONE,
SSO_FLAG = :new.SSO_FLAG,
UIM = :new.UIM,
CUSTOMERFLAG = :new.CUSTOMERFLAG,
PASSWD2 = :new.PASSWD2,
P_USERID_PASSPORT = :new.P_USERID_PASSPORT,
USERID_STATUS_EXT = :new.USERID_STATUS_EXT
WHERE USER_ID = :new.USER_ID
;
end if;
end loop;
end if;
if deleting then
dbms_output.put_line('deleted');
delete from service where user_id=:old.user_id;
delete from userid where user_id=:old.user_id;
end if;
dbms_output.put_line('end triger on sn_userid');
end TRI_SN_USERID_AFR;
/
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
service:
CREATE OR REPLACE TRIGGER UDB_V2.TRI_SN_SERVICE_AFR
AFTER DELETE OR INSERT OR UPDATE
ON UDB_V2.SN_SERVICE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
tmp_ss INTEGER;
begin
dbms_output.put_line('begin triger on sn_service');
if inserting then
dbms_output.put_line('insert');
for p in(
select SS_INDEX into tmp_ss from SERVICE where SS_INDEX=:new.SS_INDEX
)
loop
tmp_ss:=p.SS_INDEX;
end loop;
if (tmp_ss is null) then
dbms_output.put_line('in insert service');
INSERT INTO SERVICE (
SS_INDEX, USER_ID, SS_PASSWORD,
SS_STATUS, SRC_DEVICE_NO, CREATE_TIME,
LAST_MODIFY_TIME, AUTH_SRC, REG_EFFECT_MODE,
REG_EFFECT_TIME, BIND_TYPE, BIND_EFFECT_MODE,
BIND_EFFECT_TIME, SS_TYPE, USERID_SS_LOGIN_STATUS,
SS_ID, SS_PASSWD_ACT, SS_ACTIVE_FLAG
)
VALUES (
:NEW.SS_INDEX, :NEW.USER_ID, :NEW.SS_PASSWORD,
:NEW.SS_STATUS, :NEW.SRC_DEVICE_NO, :NEW.CREATE_TIME,
:NEW.LAST_MODIFY_TIME, :NEW.AUTH_SRC, :NEW.REG_EFFECT_MODE,
:NEW.REG_EFFECT_TIME, :NEW.BIND_TYPE, :NEW.BIND_EFFECT_MODE,
:NEW.BIND_EFFECT_TIME, :NEW.SS_TYPE, :NEW.USERID_SS_LOGIN_STATUS ,
:NEW.SS_ID, :NEW.SS_PASSWD_ACT, :NEW.SS_ACTIVE_FLAG
);
end if;
end if;
if updating then
dbms_output.put_line('updated');
for p in(select * from SERVICE where SS_INDEX=:OLD.SS_INDEX)
loop
if (
(P.USER_ID != :NEW.USER_ID)
OR (P.SS_PASSWORD != :NEW.SS_PASSWORD)
OR (P.SS_STATUS != :NEW.SS_STATUS)
OR (P.SRC_DEVICE_NO != :NEW.SRC_DEVICE_NO)
OR (P.CREATE_TIME != :NEW.CREATE_TIME)
OR (P.LAST_MODIFY_TIME != :NEW.LAST_MODIFY_TIME)
OR (P.AUTH_SRC != :NEW.AUTH_SRC)
OR (P.REG_EFFECT_MODE != :NEW.REG_EFFECT_MODE)
OR (P.REG_EFFECT_TIME != :NEW.REG_EFFECT_TIME)
OR (P.BIND_TYPE != :NEW.BIND_TYPE)
OR (P.BIND_EFFECT_MODE != :NEW.BIND_EFFECT_MODE)
OR (P.BIND_EFFECT_TIME != :NEW.BIND_EFFECT_TIME)
OR (P.SS_TYPE != :NEW.SS_TYPE)
OR (P.USERID_SS_LOGIN_STATUS != :NEW.USERID_SS_LOGIN_STATUS)
OR (P.SS_ID != :NEW.SS_ID)
OR (P.SS_PASSWD_ACT != :NEW.SS_PASSWD_ACT)
OR (P.SS_ACTIVE_FLAG != :NEW.SS_ACTIVE_FLAG)
)
then
UPDATE SERVICE
SET
SS_INDEX = :NEW.SS_INDEX,
USER_ID = :NEW.USER_ID,
SS_PASSWORD = :NEW.SS_PASSWORD,
SS_STATUS = :NEW.SS_STATUS,
SRC_DEVICE_NO = :NEW.SRC_DEVICE_NO,
CREATE_TIME = :NEW.CREATE_TIME,
LAST_MODIFY_TIME = :NEW.LAST_MODIFY_TIME,
AUTH_SRC = :NEW.AUTH_SRC,
REG_EFFECT_MODE = :NEW.REG_EFFECT_MODE,
REG_EFFECT_TIME = :NEW.REG_EFFECT_TIME,
BIND_TYPE = :NEW.BIND_TYPE,
BIND_EFFECT_MODE = :NEW.BIND_EFFECT_MODE,
BIND_EFFECT_TIME = :NEW.BIND_EFFECT_TIME,
SS_TYPE = :NEW.SS_TYPE,
USERID_SS_LOGIN_STATUS = :NEW.USERID_SS_LOGIN_STATUS,
SS_ID = :NEW.SS_ID,
SS_PASSWD_ACT = :NEW.SS_PASSWD_ACT,
SS_ACTIVE_FLAG = :NEW.SS_ACTIVE_FLAG
WHERE SS_INDEX = :new.SS_INDEX
;
end if;
end loop;
end if;
if deleting then
dbms_output.put_line('deleted');
delete from service where SS_INDEX=:old.SS_INDEX;
end if;
dbms_output.put_line('end triger on sn_service');
end TRI_SN_SERVICE_AFR;
/
修改快照刷新时间
Alter snapshot sn_userid refresh fast Start with sysdate+2/(24) next sysdate+1/(24);
Alter snapshot sn_service refresh fast Start with sysdate+2/(24) next sysdate+1/(24);
首次刷新在30秒之后,以后每分钟刷新一次
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
附件:
1.查看快照最后一次刷新时间
SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;
2.查看快照下次执行时间
select last_date,next_date,what from user_jobs order by next_date;
3.手动执行更新
EXEC DBMS_SNAPSHOT.REFRESH ('sn_service','F');
手动刷新方式1
begin
dbms_refresh.refresh('sn_service');
end;
手动刷新方式2
EXEC DBMS_SNAPSHOT.REFRESH('sn_service','F'); //第一个参数是快照名,第二个参数 F 是快速刷新 C 是完全刷新.
4.删除快照日志(在源上)
drop snapshot log on userid;
drop snapshot log on service;
5删除掉快照(在目的上)
drop snapshot sn_userid;
drop snapshot sn_service;
相关推荐
正在看的ORACLE教程是:Oracle数据库快照的使用。oracle数据库的快照是一个表,它包含有对一个本地或远程数据库上一个或多个表或视图的查询的结果。正因为快照是一个主表的查询子集,使用快照可以加快数据的查询速度;...
利用Oracle数据库快照实现数据双向复制.pdf
有时候我们不小心把数据删掉,或者数据莫名其妙的丢失,这时候我们可以借助Oracle本身自带的快照来进行数据的恢复。
sql查询oracle时间段操作,通过oracle数据库快照来实现恢复数据
sql恢复oracle数据库delete的数据,oracle数据库快照实现恢复数据sql
oracle 数据恢复 误删恢复 111111111111111111111111111111111111111111111111111111111111111111111111
oracle数据库的快照是一个表,它包含有对一个本地或远程数据库上一个或多个表或 视图的查询的结果。正因为快照是一个主表的查询子集,使用快照可以加快数据的查询速度; 在保持不同数据库中的两个表的同步中,利用...
11.5.2将物理Standby数据库转换为快照Standby数据库 11.5.3将快照Standby数据库转换为物理Standby数据库 11.6主备数据库切换 11.6.1 Switchover切换 11.6.2 Failover切换 11.7本章小结 第12章 RAC备份 12.1...
利用ORACLE的快照功能实现远程数据库的复制.pdf
一不小心听说了oracle 数据库快照,这个词我当初一听就楞了,不知道这个是什么玩意,然后花了点时间研究了下,以下是我通过网络学习总结的 快照主要是用于分布式数据库,我们有数据库A,A中有个表a,我们在数据库B中...
第十章 监控数据库的表、索引、簇、快照和对象 第十一章 监控用户和其他数据库对象 第十二章 Oracle的性能优化 第十三章 数据库的内部优化 第十四章 管理分布式数据库 第十五章 Oracle的备份和恢复
利用oracle快照dblink解决数据库表同步问题.txt
1.4.21 快照和显形图 14 1.5 内部存储结构 15 1.5.1 系统全局区 15 1.5.2 环境区 17 1.5.3 程序全局区 17 1.6 后台进程 18 1.7 外部结构 20 1.7.1 重做日志 20 1.7.2 控制文件 21 1.7.3 跟踪文件与警告日志 21 1.8 ...
Oracle作为数据库平台,并且有的项目使用到了Oracle的数据库复制技术,其中也遇到了一些问题,因此在这里,我对Oracle的复制技术谈一下我个人的理解,希望能够对采用Oracle数据库的项目组有所帮助。其中在文中使用到...
ORACLE数据快照技术在数据交换中的应用.pdf
优化sql语句 1)减少对数据库的查询次数,即减少对系统资源的请求,使用快照和显形图等分布式数据库对象可以减少对数据库的查询次数。 2)尽量使用相同的或非常类似的SQL语句进行查询,这样不仅充分利用SQL共享池中...
该文档主要目的是降低现场实施人员及用户Oracle数据库的管理难度,提高Oracle数据库技术能力,文档针对Oracle9i、10g两个版本提供了一套完整的Oracle数据库监控、管理的思路、方法步骤,依照该手册进行Oracle数据库...