text: -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
  -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
  
  --------------------------------------------------------------------------------
  --
  -- 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:       You need to comment out the right variable definitions for your client platform below.
  --              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/Mac OSX)
  --def SQLPATH=%SQLPATH% -- (Windows)
  
  
  -- def _start=start   -- Windows
  -- def _start=firefox -- Unix/Linux
  def _start=open -- MacOS
  
  def _delete="rm -f" -- Unix/MacOSX
  -- def _delete="del" -- Windows
  
  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"'
    define prev="(select /*+ no_unnest */ prev_sql_id from v$session where sid = (select sid from v$mystat where rownum=1))"
  
    -- geeky shorcuts for producing date ranges for various ASH scripts
    define     min="sysdate-1/24/60 sysdate"
    define    1min="sysdate-1/24/60 sysdate"
    define  minute="sysdate-1/24/60 sysdate"
    define    5min="sysdate-1/24/12 sysdate"
    define    hour="sysdate-1/24 sysdate"
    define   1hour="sysdate-1/24 sysdate"
    define   2hours="sysdate-1/12 sysdate"
    define  24hours="sysdate-1 sysdate"
    define      day="sysdate-1 sysdate"
    define    today="TRUNC(sysdate) sysdate"
  
  -- 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.
  -- 
  -- setting from 500 to 100 as otherwise snapper/dbms_output starts getting ORA-6502 errors due to 32k fetch limit of dbms_output
  
    set arraysize 100
  
  -- 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="vi -c 'set notitle'"  
  --  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';
  
    column tracefile print 
  
  -- make default date format nicer
  
    alter session set nls_date_format = 'YYYY-MM-DD 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 999999999999999999999
    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
    col name_col_plus_show_param ON HEADING 'PARAMETER_NAME' FORMAT a60
  
  -- oracle 12.2+ has too wide OWNER and OBJECT_NAME...
  
    col owner           for a30 wrap
    col object_name     for a30 wrap
    col subobject_name  for a30 wrap
    col segment_name    for a30 wrap
    col partition_name  for a30 wrap
  
  -- 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
  
  -- set up a default ref cursor for Snapper V4 begin/end snapshotting
  -- var snapper refcursor
  
  -- reset termout back to normal
  
    set termout on
  
  