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


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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google