Sqlplus spool to dynamic Log File Name

Sqlplus spool to dynamic log file name through the sqlplus spool function we output the results of routine database O & M to the log file, and sometimes it needs timed output, in order to avoid repeated log file names, the output log file name can be dynamically named. This article provides an example for your reference. Www.2cto.com 1. The script for generating dynamic log files [SQL] robin @ SZDB: ~> More dynamic_logfile_name. SQL -- + ====================================== ===========================+ -- | Filename: dynamic_logfile_name. SQL | -- | Desc: | -- | This is only a sapmle to generate dynamic log file name | -- + ================== ========================================================== = + set termout off echo off verify off set feedback off variable dt VARCHAR2 (13 ); COL dt FORMAT a15 COL report_name MAT 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 FEEDBAC K on; UNDEFINE rpt_name EXIT; 2. Test script [SQL] 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 LS-DYNA *. log-rw-r -- 1 robin oinstall 45 2013-03-15 dynamic_logfile_20130315_1111.log-rw-r -- 1 robin oinstall 45 2013-03-15 intervals 3. describes a. usage of the sqlplus command see: SQL * Plus User's Guide and Reference) b. In the above script, the variable dt is defined to store the system time and date. It is used for the second half of the dynamic log file. c. Use the variable: the value of d and begin end blocks can be assigned by SQL queries in dt mode. Otherwise, the assignment fails. e. Some prompts can be removed by yourself, such as print dt and prompt.