SQLPLUS: Create Spool File with DB Schema Name and Current Time and Date/Sysdate



The Eucharistic Miracles of the World

Sometimes it is better to give database/schema and timestamp for the spool files which get created from the sqlplus prompt. If you are keeping all the spool files in single directory this method will be useful to pick up the right spool file out from all files.

Here is the method to spool a file with database name, schema name and current timestamp.

column timendate new_value spooltime noprint
select SYS_CONTEXT('USERENV', 'DB_NAME')||'_'||SYS_CONTEXT('USERENV', 'SESSION_USER')||'_'||to_char(sysdate,'ddmmyyyyhh:mi') timeNdate from dual;
spool &spooltime-output.txt
select * from dept;
spool off

-rw-r--r-- 1 oracle oinstall  528 Feb  6 11:51 ORCL_STHOMAS_0602201411:50-output.txt


Website Stats


Post a Comment


Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2)

acehints.com Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer