Unix/Linux Oracle shell

1. Execute the stored procedure in the Oracle database

Proc. sh script content (in ibm aix environment)

ORACLE_BASE =/oracle; export ORACLE_BASE

ORACLE_HOME = $ ORACLE_BASE/product/10.2.0; export ORACLE_HOME

ORACLE_SID = commdb; export ORACLE_SID

$ ORACLE_HOME/bin/sqlplus cfa/cfa <

Exec sp_ B _20090827;

Exit

!

2. the backup file name is suffixed with date to perform exp backup on the tables in the database.

Exp_tab_perday.sh script content (in ibm aix environment)

DATE = 'date + % Y % m % d _ % T'; export date

ORACLE_BASE =/oracle; export ORACLE_BASE

ORACLE_HOME = $ ORACLE_BASE/product/10.2.0; export ORACLE_HOME

ORACLE_SID = commdb; export ORACLE_SID

$ ORACLE_HOME/bin/exp cfa/cfa file =/oracle/dlbk_table $ DATE. dmp log =/oracle/dlbk_table $ DATE. log tables = cfa_income_ent_new, cfa_income_inst_old, cfa_income_ent_old

3. Back up exp users and restore imp_user.sh script (in RedHat AS4 environment)

Export ORACLE_HOME =/opt/oracle/product/10g

$ ORACLE_HOME/bin/imp cfa/cfa @ orcl file =/home/oracle/dlbk_cfa2009-09-12.dmp log =/home/oracle/dlbk_cfa2009-09-12.dmp fromuser = cfa touser = cfa

4. Use spool to export data from tables in oracle databases into a standard txt format.

Content of the scheduling script spool_out.sh (in IBM's AIX environment ):

ORACLE_BASE =/oracle; export ORACLE_BASE

ORACLE_HOME = $ ORACLE_BASE/product/10.2.0; export ORACLE_HOME

ORACLE_SID = commdb; export ORACLE_SID

DATE = 'date + % Y % m % d _ % T'; export date

$ ORACLE_HOME/bin/sqlplus cfa/cfa @/oracle/cfaout/spool. SQL spool configuration script spool. SQL content

Set heading on;

Set feedback off;

Set pagesize 0;

Set linesize 8000;

Set trimout on;

Set trimspool on;

Set term off;

Set newpage 1;

Spool/oracle/cfaout/aix_ent_info_$DATE.txt; @/oracle/cfaout/select_ent_info. SQL;

Spool off;

Spool/oracle/cfaout/aix_report_record_1_date.txt; @/oracle/cfaout/select_report_record. SQL;

Spool off;

Exit;

The two select tables are script content in standard format:

Select_ent_info. SQL:

Select CUSTOMERID | '| CORPID |' | ENTERPRISENAME | '| ENGLISHNAME |' | fictiousperson | '| | ORGNATURE | '| FINANCETYPE |' | ENTERPRISEBELONG | '| INDUSTRYTYPE |' | INDUSTRYTYPE1 | '| | INDUSTRYTYPE2 | '| PRIVATE |' | economy type | '| ORGTYPE |' | MOSTBUSINESS | '| | BUDGETTYPE | '| RCCURRENCY |' | REGISTERCAPITAL | '| PCCURRENCY |' | paiclupcapcy | '| | FUNDSOURCE | '| TOTALASSETS |' | NETASSETS | '| ANNUALINCOME |' | SCOPE | '| | LIMIT | '| CREDITDATE |' | LICENSENO | '| LICENSEDATE |' | LICENSEMATURITY | '| | SETUPDATE | '| INSPECTIONYEAR |' | LOCKSITUATION | '| TAXNO |' | BANKLICENSE | '| | BANKID | '| MANAGEAREA |' | BANCHAMOUNT | '| EXCHANGEID |' | REGISTERADD | '| | CHARGEDEPARTMENT | '| OFFICEADD |' | OFFICEZIP | '| COUNTRYCODE |' | REGIONCODE | '| | languagecode | '| languagename |' | RELATIVETYPE | '| OFFICETEL |' | OFFICEFAX | '| | WEBADD | '| EMAILADD |' | EMPLOYEENUMBER | '| MAINPRODUCTION |' | NEWTECHCORPORNOT | '| | listingtransfernot | '| HASIERIGHT |' | HASDIRECTORATE | '| BASICBANK |' | BASICACCOUNT | '| | MANAGEINFO | '| CUSTOMERHISTORY |' | PROJECTFLAG | '| REALTYFLAG |' | WORKFIELDAREA | '| | workfield#| | '| ACCOUNTDATE |' | LOANCARDNO | '| LOANCARDPASSWORD |' | LOANCARDINSYEAR | '| | LOANCARDINSRESULT | '| LOANFLAG |' | FINANCEORNOT | '| FINANCEBELONG |' | CREDITBELONG | '| | CREDITLEVEL | '| EVALUATEDATE |' | OTHERCREDITLEVEL | '| OTHEREVALUATEDATE |' | OTHERORGNAME | '| | INPUTORGID | '| INPUTUSERID |' | INPUTDATE | '| UPDATEORGID |' | UPDATEUSERID | '| | UPDATEDATE | '| REMARK |' | TAXNO1 | '| fictiouspersonid |' | GROUPFLAG | '| | EVALUATELEVEL | '| MYBANK |' | MYBANKACCOUNT | '| OTHERBANK |' | OTHERBANKACCOUNT | '| | TEMPSAVEFLAG | '| FINANCEDEPTTEL |' | ECGROUPFLAG | '| SUPERCORPNAME |' | SUPERLOANCARDNO | '| | SUPERCERTTYPE | '| SMEINDUSTRYTYPE |' | SELLSUM | '| SUPERCERTID |' | ISVOUCHCORP from aix_ent_info;

Select_report_report. SQL:

Select REPORTNO | '| REPORTDATE |' | MODELNO | '| REPORTSCOPE |' | OBJECTNO from aix_report_record;