--调整dblink带域名问题
SYS@gbs1> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
GBS
SYS@gbs1> update global_name set global_name='GBS.WORLD';
1 row updated.
SYS@gbs1> commit;
Commit complete.
SYS@gbs1> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
GBS.WORLD
SYS@gbs1>
SYS@gbs1>
SYS@gbs1>
SYS@gbs1> conn gbspkg/Paic1234
Connected.
GBSPKG@gbs1> select sysdate from dual@egislink;
SYSDATE
-------------------
2022-05-24 18:12:08
--查询失效对象
col owner for a20
col object_name for a50
col object_type for a20
set pagesize 10000
set linesize 200
select owner,object_name,object_type from dba_objects where status='INVALID';
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;
--编译package body
select 'ALTER package '||owner||'.'||object_name||' compile BODY ;' from dba_objects where status='INVALID' and object_type='PACKAGE BODY';
--编译package
select 'ALTER package '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and owner='GBSPKG' and object_type='PACKAGE';
--编译view
select 'ALTER view '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='VIEW';
SELECT 'ALTER ' || object_type || ' ' || owner || '.' || object_name ||
' COMPILE;'
FROM dba_objects
WHERE status 'VALID' and object_type='TRIGGER';
重编译schema
execute utl_recomp.recomp_serial('SCOTT');
批量重新编译同义词
SELECT 'alter ' || decode(owner,
'PUBLIC',
'public synonym ',
'synonym ' || owner || '.') || object_name ||
' compile;'
FROM dba_objects
WHERE object_type = 'SYNONYM'
AND status = 'INVALID';
--单独处理的同义词
create or replace view pensintf.pts1_bargain_info as
select scheme_no,
fund_code,
price_date
from pts1intf.pts1_bargain_info@PENSLINK.WORLD;
PENSLINK失效需要重建
select PASSWORD FROM SYS.USER$ WHERE NAME='PTS1INTF';
paces这个dblink
---处理遗漏的trigger重建
GBSTRG TR_I_ABBS_AGENCY_RULE_CONFIG TRIGGER
GBSTRG TR_I_AGENT_MONTH_ACHIEVE TRIGGER
GBSTRG TR_U_ABBS_AGENCY_RULE_CONFIG TRIGGER
GBSTRG TR_U_AGENT_MONTH_ACHIEVE TRIGGER
ALTER TRIGGER GBSTRG.TR_U_ABBS_AGENCY_RULE_CONFIG COMPILE;
ALTER TRIGGER GBSTRG.TR_I_AGENT_MONTH_ACHIEVE COMPILE;
ALTER TRIGGER GBSTRG.TR_I_ABBS_AGENCY_RULE_CONFIG COMPILE;
ALTER TRIGGER GBSTRG.TR_U_AGENT_MONTH_ACHIEVE COMPILE;
select dbms_metadata.get_ddl('TRIGGER','TR_I_ABBS_AGENCY_RULE_CONFIG','GBSTRG') from dual;
CREATE OR REPLACE TRIGGER "GBSTRG"."TR_I_ABBS_AGENCY_RULE_CONFIG"
before insert on "ABBSDATA"."ABBS_AGENCY_RULE_CONFIG_0224"
for each row
declare
--通用变量定义
v_trigger_user varchar2(100);
v_trigger_date date;
v_sqlcode varchar2(6);
v_sqlerrm varchar2(200);
v_error_comment varchar2(300);
--针对审计字段更新功能定义的游标和变量
cursor c_switch(cp_switch gbs_tr_switch.switch_for%type) is
select status from gbs_tr_switch
where trigger_name='TR_I_ABBS_AGENCY_RULE_CONFIG' and switch_for =cp_switch;
v_status gbs_tr_switch.status%type;
begin
v_error_comment:='before get user';
v_trigger_user :=pub_sys_package.get_user;
v_trigger_date :=sysdate;
--需求来源：表中记录的审计字段信息更新
--功能描述：用于保证审计信息的完整性
v_error_comment:='before GBS_insert_4_audit_column';
open c_switch('GBS_CRE_4_AUD_COL');
fetch c_switch into v_status;
if c_switch%found and v_status ='1' then
:new.CREATED_BY:=v_trigger_user;
:new.CREATED_DATE:=v_trigger_date;
:new.UPDATED_BY:=v_trigger_user;
:new.UPDATED_DATE:=v_trigger_date;
end if;
close c_switch;
--需求来源：XXXX
--功能描述：XXXX
--错误处理
--触发器执行有误，将出错信息插入到GBS_tr_error_log表
exception
when others then
v_sqlcode :=sqlcode;
v_sqlerrm :=substr(sqlerrm,1,200);
insert into gbs_tr_error_log
(
error_no , --系统错误代码
error_message , --系统错误信息
trigger_name , --出错的trigger
trigger_user , --出错的用户
trigger_date , --出错的时间
error_comment --出错详细信息
)
values
(
v_sqlcode,
v_sqlerrm,
'TR_I_ABBS_AGENCY_RULE_CONFIG',
v_trigger_user,
v_trigger_date,
v_error_comment
);
end;
ALTER TRIGGER "GBSTRG"."TR_I_ABBS_AGENCY_RULE_CONFIG" ENABLE;
select dbms_metadata.get_ddl('TRIGGER','TR_I_AGENT_MONTH_ACHIEVE','GBSTRG') from dual;
CREATE OR REPLACE TRIGGER "GBSTRG"."TR_I_AGENT_MONTH_ACHIEVE"
before insert on "GBSMAN"."AGENT_MONTH_ACHIEVE"
for each row
declare
--通用变量定义
v_trigger_user varchar2(100);
v_trigger_date date;
v_sqlcode varchar2(6);
v_sqlerrm varchar2(200);
v_error_comment varchar2(300);
--针对审计字段更新功能定义的游标和变量
cursor c_switch(cp_switch gbs_tr_switch.switch_for%type) is
select status from gbs_tr_switch
where trigger_name='TR_I_AGENT_MONTH_ACHIEVE' and switch_for =cp_switch;
v_status gbs_tr_switch.status%type;
begin
v_error_comment:='before get user';
v_trigger_user :=pub_sys_package.get_user;
v_trigger_date :=sysdate;
--需求来源：表中记录的审计字段信息更新
--功能描述：用于保证审计信息的完整性
v_error_comment:='before GBS_insert_4_audit_column';
open c_switch('GBS_CRE_4_AUD_COL');
fetch c_switch into v_status;
if c_switch%found and v_status ='1' then
:new.created_by:=v_trigger_user;
:new.created_date:=v_trigger_date;
:new.updated_by:=v_trigger_user;
:new.updated_date:=v_trigger_date;
end if;
close c_switch;
--需球来源：自动插入seq号
--功能描述：自动插入seq号 ACHIEVE_ID
v_error_comment:='before GBS_INS_PK';
open c_switch('GBS_INS_PK');
fetch c_switch into v_status;
if c_switch%found and v_status ='1' then
select seq_AGENT_month_achieve.nextval into :new.ACHIEVE_ID from dual;
end if;
close c_switch;
--错误处理
--触发器执行有误，将出错信息插入到GBS_tr_error_log表
exception
when others then
v_sqlcode :=sqlcode;
v_sqlerrm :=substr(sqlerrm,1,200);
insert into gbs_tr_error_log
(
error_no , --系统错误代码
error_message , --系统错误信息
trigger_name , --出错的trigger
trigger_user , --出错的用户
trigger_date , --出错的时间
error_comment --出错详细信息
)
values
(
v_sqlcode,
v_sqlerrm,
'TR_I_AGENT_MONTH_ACHIEVE',
v_trigger_user,
v_trigger_date,
v_error_comment
);
end;
ALTER TRIGGER "GBSTRG"."TR_I_AGENT_MONTH_ACHIEVE" ENABLE;
select dbms_metadata.get_ddl('TRIGGER','TR_U_ABBS_AGENCY_RULE_CONFIG','GBSTRG') from dual;
CREATE OR REPLACE TRIGGER "GBSTRG"."TR_U_ABBS_AGENCY_RULE_CONFIG"
before update on "ABBSDATA"."ABBS_AGENCY_RULE_CONFIG_0224"
for each row
declare
--通用变量定义
v_trigger_user varchar2(100);
v_trigger_date date;
v_sqlcode varchar2(6);
v_sqlerrm varchar2(200);
v_error_comment varchar2(300);
--针对审计字段更新功能定义的游标和变量
cursor c_switch(cp_switch gbs_tr_switch.switch_for%type) is
select status from gbs_tr_switch
where trigger_name='TR_U_ABBS_AGENCY_RULE_CONFIG' and switch_for =cp_switch;
v_status gbs_tr_switch.status%type;
begin
v_error_comment:='before get user';
v_trigger_user :=pub_sys_package.get_user;
v_trigger_date :=sysdate;
--需求来源：表中记录的审计字段信息更新
--功能描述：用于保证审计信息的完整性
v_error_comment:='before GBS_update_2_audit_column';
open c_switch('GBS_UPD_2_AUD_COL');
fetch c_switch into v_status;
if c_switch%found and v_status ='1' then
:new.UPDATED_BY:=v_trigger_user;
:new.UPDATED_DATE:=v_trigger_date;
end if;
close c_switch;
--需求来源：XXXX
--功能描述：XXXX
--错误处理
--触发器执行有误，将出错信息插入到GBS_tr_error_log表
exception
when others then
v_sqlcode :=sqlcode;
v_sqlerrm :=substr(sqlerrm,1,200);
insert into gbs_tr_error_log
(
error_no , --系统错误代码
error_message , --系统错误信息
trigger_name , --出错的trigger
trigger_user , --出错的用户
trigger_date , --出错的时间
error_comment --出错详细信息
)
values
(
v_sqlcode,
v_sqlerrm,
'TR_U_ABBS_AGENCY_RULE_CONFIG',
v_trigger_user,
v_trigger_date,
v_error_comment
);
end;
ALTER TRIGGER "GBSTRG"."TR_U_ABBS_AGENCY_RULE_CONFIG" ENABLE;
select dbms_metadata.get_ddl('TRIGGER','TR_U_AGENT_MONTH_ACHIEVE','GBSTRG') from dual;
CREATE OR REPLACE TRIGGER "GBSTRG"."TR_U_AGENT_MONTH_ACHIEVE"
before update on "GBSMAN"."AGENT_MONTH_ACHIEVE"
for each row
declare
v_trigger_user varchar2(100);
v_trigger_date date;
v_sqlcode varchar2(6);
v_sqlerrm varchar2(200);
v_error_comment varchar2(300);
cursor c_switch(cp_switch gbs_tr_switch.switch_for%type) is
select status from gbs_tr_switch
where trigger_name='TR_U_AGENT_MONTH_ACHIEVE' and switch_for =cp_switch;
v_status gbs_tr_switch.status%type;
begin
v_error_comment:='before get user';
v_trigger_user :=pub_sys_package.get_user;
v_trigger_date :=sysdate;
--需求来源：表中记录的审计字段信息更新
--功能描述：用于保证审计信息的完整性
v_error_comment:='before GBS_update_2_audit_column';
open c_switch('GBS_UPD_2_AUD_COL');
fetch c_switch into v_status;
if c_switch%found and v_status ='1' then
:new.updated_by:=v_trigger_user;
:new.updated_date:=v_trigger_date;
end if;
close c_switch;
--需球来源：XXXX
--功能描述：XXXX
--错误处理
--触发器执行有误，将出错信息插入到GBS_tr_error_log表
exception
when others then
v_sqlcode :=sqlcode;
v_sqlerrm :=substr(sqlerrm,1,200);
insert into gbs_tr_error_log
(
error_no , --系统错误代码
error_message , --系统错误信息
trigger_name , --出错的trigger
trigger_user , --出错的用户
trigger_date , --出错的时间
error_comment --出错详细信息
)
values
(
v_sqlcode,
v_sqlerrm,
'TR_U_AGENT_MONTH_ACHIEVE',
v_trigger_user,
v_trigger_date,
v_error_comment
);
end;
ALTER TRIGGER "GBSTRG"."TR_U_AGENT_MONTH_ACHIEVE" ENABLE;
---job生效
begin
for cur in ( select * from dba_jobs j where j.BROKEN = 'N' ) loop
sys.dbms_ijob.next_date(job => cur.job,next_date=>to_date('2022-06-07 18:15:00','yyyy-mm-dd hh24:mi:ss')) ;
end loop ;
commit ;
end;
/
exec dbms_job.run(36967);
---批量调整job的next_date
set serveroutput on
declare
nexttime date;
BEGIN
FOR R IN (select job,INTERVAL from dba_jobs TT where schema_user ='GBSJOB' AND INTERVAL IS NOT NULL AND TT.BROKEN='N' ) LOOP
execute immediate 'select '||R.INTERVAL|| ' from dual' into nexttime ;
sys.dbms_ijob.broken(job => R.JOB, broken => TRUE) ;
sys.dbms_ijob.broken(job => R.JOB, broken => FALSE,NEXT_DATE=>nexttime) ;
commit;
DBMS_OUTPUT.put_line(R.JOB||' job nexttime:'||nexttime);
END LOOP;
commit;
END;
/
exec dbms_ijob.next_date(job => 171,next_date=>to_date('2022-10-26 05:00:00','yyyy-mm-dd hh24:mi:ss'));
commit;
exec DBMS_IJOB.INTERVAL (job => 9160,interval=>'NEXT_DAY(TRUNC(SYSDATE),''SAT'')+1/24');
exec DBMS_IJOB.INTERVAL (job => 9034,interval=>'NEXT_DAY(TRUNC(SYSDATE),''MON'')');
exec DBMS_IJOB.INTERVAL (job => 171,interval=>'ADD_MONTHS(TRUNC(SYSDATE, ''MM''),1)+25+5/24');
NEXT_DAY(TRUNC(SYSDATE ), '星期一' )
表列名乱码查询
select distinct owner,table_name from dba_tab_columns where length(column_name)lengthb(column_name)
EXCLUDE=trigger,TABLE:"in('GBS_20211215E')"
