--------------------------------------------------------------------------------
--
-- Name:	init.sql
-- Purpose:	Initializes sqlplus variables for 156 character terminal
--		width and other settings.
--
-- Author:	Tanel Poder
-- Copyright:	(c) http://www.tanelpoder.com
-- 
-- Other:	Some settings Windows specific
--		Assumes SQLPATH variable set to point to TPT script directory
--
--------------------------------------------------------------------------------

-- this must be here to avoid logon problems when SQLPATH env variable is unset
def SQLPATH=""


-- set SQLPATH variable to either Unix or Windows format

-- def SQLPATH=$SQLPATH -- (Unix)
def SQLPATH=%SQLPATH%


def _start=start   -- Windows
-- def _start=firefox -- Unix/Linux
-- def _start=open -- MacOS

def _delete="del"

def _tpt_tempdir=&SQLPATH/tmp

-- some internal variables required for TPT scripts

	define _ti_sequence=0
	define _tptmode=normal
	define _xt_seq=0

  define all='"select /*+ no_merge */ sid from v$session"'

-- you should change linesize to match terminal width - 1 only 
-- if you don't have a terminal with horizontal scrolling
-- capability (cmd.exe and Terminator terminal do have horizontal scrolling)

	set linesize 999

-- set truncate after linesize on

    -- set truncate on

-- set pagesize larger to avoid repeting headings

	set pagesize 5000

-- fetch 10000000 bytes of long datatypes. good for
-- querying DBA_VIEWS and DBA_TRIGGERS

	set long 10000000
	set longchunksize 10000000

-- larger arraysize for faster fetching of data
-- note that arraysize can affect outcome of experiments
-- like buffer gets for select statements etc.

	set arraysize 500

-- normally I keep this commented out, otherwise
-- a DBMS_OUTPUT.GET_LINES call is made after all
-- PL/SQL executions from sqlplus. this may distort
-- execution statistics for experiments

	--set serveroutput on size unlimited

-- to have less garbage on screen

	set verify off

-- to trim trailing spaces from spool files

	set trimspool on

-- to trim trailing spaces from screen output

	set trimout on

-- don't use tabs instead of spaces for "wide blanks"
-- this can mess up the vertical column locations in output

	set tab off

-- this makes describe command better to read and more
-- informative in case of complex datatypes in columns
				
	set describe depth 1 linenum on indent on

-- you can make sqlplus run any command as your editor
-- I could use "start notepad" on windows if you want to 
-- return control back to sqlplus immediately after launching
-- notepad (so that you can continue typing in sqlplus

	define _editor="notepad"  
--	define _external_editor="/Applications/Terminator.app/Contents/MacOS/Terminator vi "  

-- assign the tracefile name to trc variable

    def trc=unknown

	column tracefile noprint new_value trc

	-- its nice to have termout off here as otherwise this would be
	-- displayed on the screen
	set termout off

	select value ||'/'||(select instance_name from v$instance) ||'_ora_'||
	       (select spid||case when traceid is not null then '_'||traceid else null end
                from v$process where addr = (select paddr from v$session
	                                         where sid = (select sid from v$mystat
	                                                    where rownum = 1
	                                               )
	                                    )
	       ) || '.trc' tracefile
	from v$parameter where name = 'user_dump_dest';

-- make default date format nicer

	alter session set nls_date_format = 'YYYYMMDD HH24:MI:SS';

-- include username and connect identifier in prompt

--	column pr new_value _pr
--	select initcap('&_user@&_connect_identifier> ') pr from dual;
--	set sqlprompt "&_pr"
--	column _pr clear


-- format some more columns for common DBA queries

	col first_change# for 99999999999999999
	col next_change# for 99999999999999999
	col checkpoint_change# for 99999999999999999
	col resetlogs_change# for 99999999999999999
	col plan_plus_exp for a100
	col value_col_plus_show_param ON HEADING  'VALUE'  FORMAT a100

-- set html format

@@htmlset nowrap "&_user@&_connect_identifier report"

-- set seminar logging file

DEF _tpt_tempfile=sqlplus_tmpfile

col seminar_logfile new_value seminar_logfile
col tpt_tempfile new_value _tpt_tempfile

select 
    to_char(sysdate, 'YYYYMMDD-HH24MISS') seminar_logfile 
  , instance_name||'-'||to_char(sysdate, 'YYYYMMDD-HH24MISS') tpt_tempfile
from v$instance;

def seminar_logfile=&SQLPATH/logs/&_tpt_tempfile..log

-- spool sqlplus output
-- spool &seminar_logfile append

set editfile afiedit.sql

-- reset termout back to normal

	set termout on