|
sqlplus spool 到动态日志文件名
通过sqlplus的spool功能我们将数据库日常运维的结果输出到日志文件，而有时候则需要定时输出，为避免日志文件名的重复，我们可以将输出的日志文件名采用动态命名方式来实现。本文则是针对这个问题给出一个示例，供大家参考。
1、生成动态日志文件的脚本
[sql] view plaincopyprint?
:~> more dynamic_logfile_name.sql 毕业论文
02.--+ ================================================================+
03.--| Filename:dynamic_logfile_name.sql |
04.--| Desc: |
05.--| This is only a sapmle to generate dynamic log file name |
06.--| Author: Robinson |
|
08.--+ ================================================================+
09.SET TERMOUT OFF ECHO OFF VERIFY OFF
10.SET FEEDBACK OFF
11.VARIABLE dt VARCHAR2(13);
12.COL dt FORMAT a15
13.COL report_name FORMAT a35
14.
15.BEGIN
16. SELECT TO_CHAR (SYSDATE, 'yyyymmdd_hh24mi') INTO :dt FROM DUAL;
17.END;
18./
19.
20.PRINT dt;
21.COL report_name NEW_VALUE rpt_name
22.
23.SELECT 'dynamic_logfile_' || :dt || '.log' AS report_name FROM DUAL;
24.
25.PROMPT "variable rpt_name value is &rpt_name"
26.SPOOL &rpt_name
27.ALTER SESSION SET nls_date_format='yyyymmdd hh24:mi:ss';
28.
29.SELECT SYSDATE FROM DUAL;
30.
31.SPOOL OFF;
32.SET TERMOUT ON ECHO ON VERIFY ON;
33.SET FEEDBACK ON;
34.UNDEFINE rpt_name
35.EXIT;
robin@SZDB:~> more dynamic_logfile_name.sql
--+ ================================================================+
--| Filename:dynamic_logfile_name.sql |
--| Desc: |
--| This is only a sapmle to generate dynamic log file name |
--| Author: Robinson |
--| Blog: http://blog.csdn.net.robinson_0612 |
--+ ================================================================+
SET TERMOUT OFF ECHO OFF VERIFY OFF
SET FEEDBACK OFF
VARIABLE dt VARCHAR2(13);
COL dt FORMAT a15
COL report_name FORMAT a35
BEGIN
SELECT TO_CHAR (SYSDATE, 'yyyymmdd_hh24mi') INTO :dt FROM DUAL;
END;
/
PRINT dt;
COL report_name NEW_VALUE rpt_name
SELECT 'dynamic_logfile_' || :dt || '.log' AS report_name FROM DUAL;
PROMPT "variable rpt_name value is &rpt_name"
SPOOL &rpt_name
ALTER SESSION SET nls_date_format='yyyymmdd hh24:mi:ss';
SELECT SYSDATE FROM DUAL;
SPOOL OFF;
SET TERMOUT ON ECHO ON VERIFY ON;
SET FEEDBACK ON;
UNDEFINE rpt_name
EXIT;
2、测试脚本
[sql] view plaincopyprint?
01.robin@SZDB:~> sqlplus -S scott/tiger@cnmmbo @dynamic_logfile_name.sql
02.robin@SZDB:~> date
03.Fri Mar 15 11:13:04 CST 2013
04.robin@SZDB:~> sqlplus -S scott/tiger@cnmmbo @dynamic_logfile_name.sql
05.robin@SZDB:~> ls -hltr dyna*.log
06.-rw-r--r-- 1 robin oinstall 45 2013-03-15 11:11 dynamic_logfile_20130315_1111.log
07.-rw-r--r-- 1 robin oinstall 45 2013-03-15 11:13 dynamic_logfile_20130315_1113.log
robin@SZDB:~> sqlplus -S scott/tiger@cnmmbo @dynamic_logfile_name.sql
robin@SZDB:~> date
Fri Mar 15 11:13:04 CST 2013
robin@SZDB:~> sqlplus -S scott/tiger@cnmmbo @dynamic_logfile_name.sql
robin@SZDB:~> ls -hltr dyna*.log
-rw-r--r-- 1 robin oinstall 45 2013-03-15 11:11 dynamic_logfile_20130315_1111.log
-rw-r--r-- 1 robin oinstall 45 2013-03-15 11:13 dynamic_logfile_20130315_1113.log
3、说明
a、有关sqlplus命令的具体用法请参考:SQL*Plus User's Guide and Reference
b、在上面的脚本中主要通过定义变量variable dt来存储系统时间日期用于动态日志文件的后半部分
c、变量使用:dt方式由sql查询进行赋值
d、begin end 块不可省略，否则，上面赋值不成功
e、一些提示信息可以自行移除，如print dt，prompt部分等
| 