linux 脚本启动oracle,linux下运行oracle脚本的例子

#Enviroment define.

. /home/oracle/.bash_profile

#following is parameter define .

#following is excution part

sqlplus xfin_arch/xxxxx<

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=PATH:PATH:PATH: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=ORACLEHOME/lib:/lib:/usr/libexportLDLIBRARYPATHNLSLANG=AMERICANAMERICA.utf8exportNLSLANGCLASSPATH=ORACLE_HOME/lib:/lib:/usr/lib

export LD_LIBRARY_PATH

NLS_LANG=AMERICAN_AMERICA.utf8

export NLS_LANG

CLASSPATH=ORACLEH​OME/lib:/lib:/usr/libexportLDL​IBRARYP​ATHNLSL​ANG=AMERICANA​MERICA.utf8exportNLSL​ANGCLASSPATH=ORACLE_HOME/JRE:ORACLEHOME/jlib:ORACLE_HOME/jlib:ORACLEH​OME/jlib:ORACLE_HOME/rdbms/jlib

export CLASSPATH

#export JAVA_HOME=/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64/jre

export PATH=/u01/app/11.2.0/oracle/product/db_1/perl/bin:ORACLEHOME/bin:ORACLE_HOME/bin:ORACLEH​OME/bin:HOME/bin:HOME/tools:HOME/tools:HOME/tools:PATH

stty erase ^h

#alias tailalert=‘tail -n200 -f /u01/app/diag/rdbms/user01/user/trace/alert_user.log’

alias tailalert=‘tail -n200 -f /u01/app/diag/rdbms/userjq29/user/trace/alert_user.log’

#alias tailogg=‘tail -n300 -f /home/oracle/oggs/ggserr.log’

alias dbs=‘cd $ORACLE_HOME/dbs’

标签：count,cur,ORACLE,NUM,例子,linux,oracle,HOME,PO

来源： https://blog.csdn.net/xiongkunwei/article/details/104863896