Sqlplus spool to dynamic Log File Name

Through the SQL plus spool function, we output the daily O & M results of the database to the log file, and sometimes it needs to be output regularly to avoid repeated log file names, the output log file name can be dynamically named. This article provides an example for your reference.

 

1. Generate a dynamic Log File Script

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 OFFSET FEEDBACK OFFVARIABLE dt VARCHAR2(13);COL dt FORMAT a15COL report_name FORMAT a35BEGIN   SELECT TO_CHAR (SYSDATE, 'yyyymmdd_hh24mi') INTO :dt FROM DUAL;END;/PRINT dt;COL report_name NEW_VALUE rpt_nameSELECT 'dynamic_logfile_' || :dt || '.log' AS report_name FROM DUAL;PROMPT "variable rpt_name value is &rpt_name"SPOOL &rpt_nameALTER 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_nameEXIT;

2. Test script

robin@SZDB:~> sqlplus -S scott/tiger@cnmmbo @dynamic_logfile_name.sqlrobin@SZDB:~> dateFri Mar 15 11:13:04 CST 2013robin@SZDB:~> sqlplus -S scott/tiger@cnmmbo @dynamic_logfile_name.sqlrobin@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. Description
A. For more information about sqlplus commands, see SQL * Plus User's Guide and reference.
B. In the above script, the variable dt is defined to store the second half of the system time and date used for dynamic log files.
C. Variable usage: assign values by SQL queries in DT Mode
D. The begin end block cannot be omitted. Otherwise, the assignment fails.
E. You can remove some prompts, such as print DT and prompt.

 

For more information, see:

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)