shell脚本与oracle

blackfur

1、use shell script to execute sql statement and output to a local file with sqlplus;

1. #!/bin/sh
   
2. tempfile=./tmp/audit_locked_accounts_$ORACLE_SID.txt
   
3. # Start sqlplus and check for locked accounts
   
4. sqlplus -S users/password@dblink << EOF >$tempfile
   
5. 
   
6.         set pagesize
   
7.         select 'The following accounts were found to be unlocked and should not be'
   
8.         from dual;
   
9.         define exit_status = 0
   
10.         column xs new_value exit_status
    
11.         select username, account_status, 1 as xs from dba_users
    
12.         where account_status != 'LOCKED'
    
13.           and username in ('HR','SCOTT','OUTLN', 'MDSYS', 'CTXSYS');
    
14.         exit &exit_status
    
15. EOF
    
16. # If the exit status of sqlplus was not 0 then we will send an email
    
17. if [ $? != 0 ]
    
18. then
    
19.         mail -s "Accounts Unlocked in $ORACLE_SID" oracle < $tempfile
    
20. fi

复制代码