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

advertisements

_____________________________________________________________________________________________________________________

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