We have Oracle running on a Unix box enviroment. I have a SQL script that is build dynamically with DDL commands to analyzse schemas. I have already figured out how to create a dynamic name for the SPOOL file that will hold the trace information that is generated when the script runs. My questions are as follows:
1) how do i generate within SQLPLUS a timetamp (ccyymmddhhmmss, without passing it as a parameters) so that I can append it to the spool file name?
2) my spool file is not capturing All the trace that is being generated. I've used SET SERVEROUTPUT ON SIZE 1000000 but it still not capturing all the trace information.
3) If I define a variabe DEFINE xyz = "abc", what is the command to see/show all variables that I have define within SQLPLUS?
Thanks for your help.
For 1) and 2) please send more info and/or your script since
the task you described can be done in many ways.
SQL> help DEF
Specifies a user variable and assigns it a CHAR value, or
lists the value and variable type of a single variable or all
DEF[INE] [variable]|[variable = text]
For detailed information on this command, see the SQL*Plus User's
Guide and Reference.
Try this sql to get the time stamp to your spool file.
set echo off
set feedback off
set heading off
set verify off
set termout off
select 'set echo off' from dual;
select 'set feedback off' from dual;
select 'set heading off' from dual;
select 'spool final_spool_file_name_'||&FILE_DATE from dual;
select '@d:\temp\' from dual;
select 'spool off' from dual;
set echo on
set feedback on
set heading on
set verify on
set termout on
Hope this will help you
Click Here to Expand Forum to Full Width