Oracle 时间转换,在脚本中执行sql语句

原创
2015/06/03 13:43
阅读数 17

例1:字符串时间转换成字符串

SQL> select username,account_status,expiry_date from dba_users;

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE
------------------------------ -------------------------------- ------------
MGMT_VIEW                      OPEN                             18-SEP-14
SYS                            OPEN                             17-SEP-14
SYSTEM                         OPEN                             21-JUN-15
DBSNMP                         OPEN                             17-SEP-14
SYSMAN                         OPEN                             18-SEP-14

SQL> select username,account_status,to_char(expiry_date,'yy-mm-dd') from dba_users;

USERNAME                       ACCOUNT_STATUS                   TO_CHAR(
------------------------------ -------------------------------- --------
MGMT_VIEW                      OPEN                             14-09-18
SYS                            OPEN                             14-09-17
SYSTEM                         OPEN                             15-06-21
DBSNMP                         OPEN                             14-09-17
SYSMAN                         OPEN                             14-09-18

例2:在脚本中执行sql语句


#!/bin/sh
# author: shenxiaoran

prog_home=/zlrt/oracle
log="/tmp/oracle_user_expiry.log"
mail=$prog_home/mail_telnet.sh

#su - oracle -c "echo $ORACLE_SID"
#select username,account_status,expiry_date from dba_users;

su - oracle -c "sqlplus / as sysdba" << EOF | awk '/USERNAME/,/rows selected/ {print}> $log
select username,to_char(expiry_date,'yyyy-mm-dd') from dba_users;
exit;
EOF

$mail "Oracle User expiry_date list" "$log"




展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
在线直播报名
返回顶部
顶部