shell脚本访问oracle 如题,我要在linux上用crontab设置个定时任务,写个脚本执行一条SQL语句,谁能告诉我shell脚本中,连接数据库(oracle)那部分怎么写?或者给个相关的脚本,谢谢. 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 [oracle@hfcc scripts]$ more tablespace.sh##################################################################### ## tablespace.sh ## ##################################################################### #!/bin/ksh #EDITOR=vi; export EDITOR ORACLE_SID=newccs; export ORACLE_SID ORACLE_BASE=/dba/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME #LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH #TNS_ADMIN=/var/opt/oracle;export TNS_ADMIN #NLS_LANG=american; export NLS_LANG #NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'; export NLS_DATE_FORMAT #ORATAB=/var/opt/oracle/oratab;export ORATAB #PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/opt/bin:.; export PATH #PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/bin:.; export PATHsqlplus -s 'sys/newdishf as sysdba' << EOFset feed off set linesize 100 set pagesize 200 column "USED (MB)" format a10 column "FREE (MB)" format a10 column "TOTAL (MB)" format a10 column PER_FREE format a10 spool tablespace.log SELECT F.TABLESPACE_NAME, TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)", TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)", TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)", TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BLOCKS*(SELECT VALUE/1024FROM V\$PARAMETERWHERE NAME = 'db_block_size')/1024)) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES/1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 100; spool off exit; #EOF #cat tablespace.log > tablespace.alert /bin/mailx -s "TABLESPACE ALERT for NEWCCS" [email protected] < tablespace.log供参考...------------------------------------------------------------------------------Blog: http://blog.csdn.net/tianlesoftware网上资源: http://tianlesoftware.download.csdn.net相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspxQ Q 群:62697716 我把原来表空间的数据文件备份了,现在重装系统了,如何挂接表空间数据文件。 java source中javaclass调用问题 如何写一个带有复合类型的存储过程?? java如何获取SQL分析结果 exp备份参数问题 oracle9i 的db_files参数问题 问个定时自动导出数据的问题,分不够再加 请教proc 问题 急!!! pro*c的程序在大数据量时,常在执行EXEC SQL时出CORE,why??? 求一函数,急! 请问一下ORACLE的稳定性和MYSQL相比如何? 急急急!!!tar怎么过滤某个文件夹
#####################################################################
## tablespace.sh ##
#####################################################################
#!/bin/ksh #EDITOR=vi; export EDITOR
ORACLE_SID=newccs; export ORACLE_SID
ORACLE_BASE=/dba/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
#LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
#TNS_ADMIN=/var/opt/oracle;export TNS_ADMIN
#NLS_LANG=american; export NLS_LANG
#NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'; export NLS_DATE_FORMAT
#ORATAB=/var/opt/oracle/oratab;export ORATAB
#PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/
bin:/opt/bin:.; export PATH
#PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/bin:.; export PATHsqlplus -s 'sys/newdishf as sysdba' << EOF
set feed off
set linesize 100
set pagesize 200
column "USED (MB)" format a10
column "FREE (MB)" format a10
column "TOTAL (MB)" format a10
column PER_FREE format a10
spool tablespace.log
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM (
SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V\$PARAMETER
WHERE NAME = 'db_block_size')/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 100;
spool off
exit;
#EOF
#cat tablespace.log > tablespace.alert
/bin/mailx -s "TABLESPACE ALERT for NEWCCS" [email protected] < tablespace.log
供参考...
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716