linux下运行oracle脚本的例子

最新推荐文章于 2023-03-21 00:05:37 发布

xiongkunwei

最新推荐文章于 2023-03-21 00:05:37 发布

阅读量709

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle 文章标签： oracle

本文链接：https://blog.youkuaiyun.com/xiongkunwei/article/details/104863896

该篇博客展示了如何在Linux系统中通过.bash_profile设置环境变量并运行Oracle SQL脚本，实现数据归档和清理。脚本内容包括设置会话参数、数据统计、插入与删除操作，并使用异常处理确保过程的完整性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#Enviroment define.
. /home/oracle/.bash_profile
#following is parameter define .
#following is excution part
sqlplus xfin_arch/xxxxx<<eof

set timing on;
set serveroutput on;
alter session set sort_area_size=1000000000;
alter session enable parallel dml;

declare
v_count number := 0;
cur XFIN_ARCH.ARCH_AP_PO_ITEM_NUM_OF_DAY_CS%rowtype;
v_shouldArchive_count number:=0;
begin
–统计应归档的数据总数
select count(*) into v_shouldArchive_count
from FINEXT_USER.FIN_AP_PO_ITEM_NUM_OF_DAY_CS@FIN_LINK d
where po_date < add_months(trunc(sysdate, ‘mm’), -6);
dbms_output.put_line(‘FINEXT_USER.FIN_AP_PO_ITEM_NUM_OF_DAY_CS应归档数据总数:’ || to_char(v_shouldArchive_count));

for cur in (select *
from FINEXT_USER.FIN_AP_PO_ITEM_NUM_OF_DAY_CS@FIN_LINK d
where po_date < add_months(trunc(sysdate, ‘mm’), -6)) loop

–数据转移
insert into XFIN_ARCH.ARCH_AP_PO_ITEM_NUM_OF_DAY_CS
(ID,
PRODUCT_ID,
PRODUCT_CODE,
PRODUCT_NAME,
SUPPLIER_ID,
SUPPLIER_CODE,
SUPPLIER_NAME,
BEGIN_NUM,
END_NUM,
SO_NUM,
R_GRF_NUM,
D_GRF_NUM,
PO_NUM,
RTV_NUM,
SHORTAGES_NUM,
OVERAGES_NUM,
R_IT_NUM,
D_IT_NUM,
PO_DATE,
CREATE_DATE,
PO_ID,
PO_CODE,
PO_ASN_DATE,
COOPERATION_TYPE,
CONTRACT_ID,
ARCHIVE_DATE)
values
(cur.ID,
cur.PRODUCT_ID,
cur.PRODUCT_CODE,
cur.PRODUCT_NAME,
cur.SUPPLIER_ID,
cur.SUPPLIER_CODE,
cur.SUPPLIER_NAME,
cur.BEGIN_NUM,
cur.END_NUM,
cur.SO_NUM,
cur.R_GRF_NUM,
cur.D_GRF_NUM,
cur.PO_NUM,
cur.RTV_NUM,
cur.SHORTAGES_NUM,
cur.OVERAGES_NUM,
cur.R_IT_NUM,
cur.D_IT_NUM,
cur.PO_DATE,
cur.CREATE_DATE,
cur.PO_ID,
cur.PO_CODE,
cur.PO_ASN_DATE,
cur.COOPERATION_TYPE,
cur.CONTRACT_ID,
sysdate);
–清除原始表数据
delete from FINEXT_USER.FIN_AP_PO_ITEM_NUM_OF_DAY_CS@FIN_LINK d where d.id = cur.id;
v_count := v_count + 1;
if mod(v_count, 5000)=0 then
commit;
end if;
end loop;
commit;
dbms_output.put_line(‘FINEXT_USER.FIN_AP_PO_ITEM_NUM_OF_DAY_CS归档完毕！归档数量v_count=’ || v_count);
exception
when others then
dbms_output.put_line(‘FINEXT_USER.FIN_AP_PO_ITEM_NUM_OF_DAY_CS归档异常！已归档数量v_count=’ || v_count);
end;
/

exit
eof

附：/home/oracle/.bash_profile

.bash_profile

Get the aliases and functions

if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

User specific environment and startup programs

PATH= $P A T H :$ HOME/bin
export PATH

ORACLE_BASE=/u01/app
ORACLE_HOME=/u01/app/11.2.0/oracle/product/db_1
export ORACLE_BASE ORACLE_HOME
ORACLE_SID=user
export ORACLE_SID
ORACLE_TERM=xterm
export ORACLE_TERM
LD_LIBRARY_PATH=