1.操作系統(tǒng)環(huán)境變量
運(yùn)行tpt腳本之前，唯一需要設(shè)置的環(huán)境變量只有一個SQLPATH,使其指向tpt腳本的目錄路徑，如下所示
- Unix/Linux/MacOS
將下面的環(huán)境變量加入shell的對應(yīng)配置文件中，如.bash_profile,.profile,.zshrc等
export SQLPATH=~/tpt
- Windows
通過環(huán)境變量配置界面增加SQLPATH環(huán)境變量，命令方式如下
set SQLPATH=D:\tpt
==注意== 在Oracle 12c版本及以上的版本中，只設(shè)置SQLPATH環(huán)境變量是無法運(yùn)行tpt腳本的，還需要設(shè)置一個操作系統(tǒng) 環(huán)境變量，變量名ORACLE_PATH,使其指向tpt目錄的路徑，即
export ORACLE_PATH=~/tpt
or
set ORACLE_PATH=~/tpt
2.驗(yàn)證tpt腳本是否正常運(yùn)作
驗(yàn)證tpt腳本是否正常運(yùn)作的方法如下：
- 正常運(yùn)行
image-20180627113826579.png
通過sql plus登陸數(shù)據(jù)庫以后，會顯示whoami信息，即上圖中最后三行顯示的內(nèi)容
- 異常運(yùn)行
image-20180627114017305.png
在沒有設(shè)置好SQLPATH和ORACLE_PATH環(huán)境變量時(shí)，通過sqlplus命令登陸數(shù)據(jù)庫后，只顯示sqlplus版本信息，而不顯示whoami信息。這是因?yàn)閟qlplus登陸時(shí)默認(rèn)會到$SQLPATH目錄中查找并執(zhí)行l(wèi)ogin.sql文件，而login.sql文件會調(diào)用whoami腳本文件i.sql來顯示當(dāng)前會話信息，如果不設(shè)置SQLPATH環(huán)境變量，就找不到login.sql文件并執(zhí)行它。login.sql文件的文件內(nèi)容如下
-- calling init.sql which will set up sqlpus variables
@init.sql
-- i.sql is the "who am i" script which shows your session/instance info and
-- also sets command prompt window/xterm title
@i.sql
-- you can put your own login scripts here
Login.sql文件分別依次調(diào)用init.sql和i.sql文件，init.sql文件定義了SQL PLUS中常用的變量信息，i.sql文件則顯示whoami信息，并設(shè)置終端的提示信息。
3.SQL PLUS環(huán)境變量
SQL PLUS環(huán)境變量的初始工作主要集中在了init.sql中，下面詳細(xì)解釋一下這個腳本
- 定義SQLPATH環(huán)境變量
-- 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)
- 定義常量_start——默認(rèn)打開文件的工具,windows下為start命令，Unix/Linux為firefox瀏覽器，MacOS下為Open命令
-- def _start=start -- Windows
-- def _start=firefox -- Unix/Linux
def _start=open -- MacOS
- 定義常量_delete——默認(rèn)刪除文件的命令，Unix/MacOS下為
rm -rf命令，windows下為del命令
def _delete="rm -f" -- Unix/MacOSX
-- def _delete="del" -- Windows
- 定義常量_tpt_tempdir——tpt腳本的臨時(shí)目錄，當(dāng)前的值為
$SQLPATH/tmp目錄
def _tpt_tempdir=&SQLPATH/tmp
- 定義tpt腳本內(nèi)部需要使用的變量，_ti_sequence,_tptmode,_xt_seq，作用未知。
-- some internal variables required for TPT scripts
define _ti_sequence=0
define _tptmode=normal
define _xt_seq=0
- 定義常量all——數(shù)據(jù)庫中的所有會話ID，取自v$session視圖中的sid字段
define all='"select /*+ no_merge */ sid from v$session"'
- 定義常量prev——當(dāng)前會話的上一次執(zhí)行SQL的sql_id,取自v$session中的prev_sql_id字段
define prev="(select /*+ no_unnest */ prev_sql_id from v$session where sid = (select sid from v$mystat where rownum=1))"
- 定義用于ASH腳本的時(shí)間范圍常量，這些常量包括1分鐘、5分鐘、1小時(shí)、2小時(shí)、24小時(shí)、1天、當(dāng)天0點(diǎn)，這些時(shí)間范圍指的是指定時(shí)間到當(dāng)前時(shí)間的時(shí)間范圍 ，如1分鐘指的是1分鐘前～當(dāng)前時(shí)間，當(dāng)天0點(diǎn)指的當(dāng)天0點(diǎn)～當(dāng)前時(shí)間這個時(shí)間范圍，常量名分別為min,minute,5min,hour,2hours,24hours,day,today,請大家對號入坐
-- geeky shorcuts for producing date ranges for various ASH scripts
define min="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 2hours="sysdate-1/12 sysdate"
define 24hours="sysdate-1 sysdate"
define day="sysdate-1 sysdate"
define today="TRUNC(sysdate) sysdate"
- 設(shè)置 sqlplus的環(huán)境配置，如linesize,pagesize等
-- 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
- 設(shè)置describe命令的環(huán)境 ，使其更友好地顯示數(shù)據(jù)庫表的字段信息
-- 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
- 設(shè)置sqlplus中的默認(rèn)編輯器，當(dāng)前的值為vi編輯器
-- 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 "
- 定義trace文件路徑變量trc,并獲取當(dāng)前會話的trace文件路徑
-- 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';
- 設(shè)置默認(rèn)的日期格式為'YYYY-MM-DD HH24:MI:SS'
-- make default date format nicer
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
- 設(shè)置sqlplus中的prompt信息
-- 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
- 對一些常用字段的顯示進(jìn)行格式化
-- 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
col name_col_plus_show_param ON HEADING 'PARAMETER_NAME' FORMAT a60
- 設(shè)置html格式,通過調(diào)用腳本htmlset文件來進(jìn)行設(shè)置
-- set html format
@@htmlset nowrap "&_user@&_connect_identifier report"
- 設(shè)置日志文件的名稱，日志文件保存在logs目錄中，此文件包含了sqlplus 命令的輸入輸出內(nèi)容,文件命名格式為數(shù)據(jù)庫實(shí)例名-日期-時(shí)間.log,如
node80-20180424-143031.log
-- 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
- 將sqlplus命令的輸出以追加的方式保存在日志文件中。
-- 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
4. whoami腳本
whoami腳本i.sql定義我是誰信息，具體顯示信息如下
image-20180627141903699.png
顯示的信息包括當(dāng)前登陸的用戶名，數(shù)據(jù)庫實(shí)例名、主機(jī)名、SID及SERIAL#,數(shù)據(jù)庫版本、數(shù)據(jù)庫啟動時(shí)間、當(dāng)前會話進(jìn)程ID等信息，同時(shí)定義了一些關(guān)于當(dāng)前會話信息的常量信息，如mysid,_i_spid,_i_opid等，具體內(nèi)容可以參考i.sql文件內(nèi)容。最后i.sql腳本文件通過window操作系統(tǒng)的title和doskey命令以及Unix/MacOS的echo命令來設(shè)置終端的標(biāo)簽顯示信息，具體實(shí)現(xiàn)的腳本內(nèi)容如下
-- Windows CMD.exe specific stuff
--host title &_i_user@&_i_conn [sid=&mysid ser#=&_i_serial spid=&_i_spid inst=&_i_inst host=&_i_host cpid=&_i_cpid opid=&_i_opid]
--host doskey /exename=sqlplus.exe desc=set lines 80 sqlprompt ""$Tdescribe $*$Tset lines 299 sqlprompt "SQL> "
-- short xterm title
host echo -ne "\033]0;&_i_user@&_i_inst &mysid[&_i_spid]\007"
-- long xterm title
--host echo -ne "\033]0;host=&_i_
顯示效果如下顯示
image-20180627144217058.png
上面的標(biāo)簽內(nèi)容則依次顯示了當(dāng)前登陸的用戶名、實(shí)例名稱、當(dāng)前會話SID以及當(dāng)前會話的操作系統(tǒng)進(jìn)程ID。
5.相關(guān)參考及引用文檔
- tpt/login.sql
- tpt/init.sql
- tpt/i.sql
