Setting up SQL*Plus Environment

When I connect to multiple databases in multiple machines, I need to know who I am and where I am connected to prevent any kind of accidental damage.

I took the following "login.sql" script from Tom Kyte's book (Expert Oracle) and modified according to my need-

#login.sql
define _editor=vi
set serverout on size 1000000
set trimspool on
set long 5000
set timing on
set linesize 200
set pagesize 1000
column plan_plus_exp format a80
column global_name new_value gname
set termout off

select lower(user) || '@' || UPPER(SUBSTR(global_name, 1, INSTR(global_name,'.')-1)) global_name from global_name;
set sqlprompt &gname>
set termout on

When I login (connect) to database, it gives me the user and machine information like -
prod7@TERMINUS>

Cool!

Spool with auto file name and current date-time

When I run the scheduled job to do manual cleanup in database - I need to spool those things for any future need. To do so, I have to dynamically generate spool file name to keep track while running the scripts by crontab. I modified my cleanup.sql file to generate spool file name-

#cleanup.sql
col sysdt noprint new_value sysdt
SELECT TO_CHAR(SYSDATE, 'yyyymmdd') sysdt FROM DUAL;
col systm noprint new_value systm
SELECT TO_CHAR(SYSDATE, 'hhmiam') systm FROM DUAL;

SPOOL /u/shawon/manual-cleanup/db-manual-cleanup-&sysdt-&systm..log
....
....

COMMIT;
SPOOL OFF

When this file is called by scheduler, it selects spool file name accordingly - "db-manual-cleanup-20071001-0354am.log"