====== SQL Skripte ======




===== ps.sql =====

<file>
set wrap off
SET LINE 2000 PAGESIZE 100 FEED ON;
COLUMN "sid_serial" FORMAT A14
COLUMN "user" FORMAT A14
COLUMN "st" FORMAT A2
COLUMN "schema" FORMAT A14
COLUMN "osuser" FORMAT A23
COLUMN "box" FORMAT A18
COLUMN "spid" FORMAT A6
COLUMN "prg" FORMAT A50
column module format a35
select /*+ RULE */ distinct * from (
SELECT /*+ RULE */
	''''
	|| s.sid
	|| ','
	|| s.serial#
	|| ''';'                       "sid_serial"
	, p.spid
	, s.username                    "user"
	, substr(s.status,1,2)                      "st"
	, s.schemaname                  "schema"
	, s.osuser                      "osuser"
	, s.machine                     "box"
	, substr(TO_CHAR(s.logon_time, 'DD HH24:MI:SS'),1,22) "logon_time"
	,to_char(floor(last_call_et/3600),'999')||':'||
	   to_char(floor(mod(last_call_et,3600)/60),'99')||':'||
	   to_char(mod(mod(last_call_et,3600),60),'99') IDLE
	, substr(NVL(s.program,p.program),1,50) "prg"
	, s.module
	, replace(replace(replace(replace(sa.sql_text,'  ',' '),'  ',' '),'  ',' '),chr(13),' ') v$SQLAREA_SQL
,p.LATCHWAIT,p.LATCHSPIN
,s.client_info,s.action
	,sio.Consistent_Gets, sio.Block_Gets, sio.Physical_Reads,
	decode((sio.Consistent_Gets+sio.Block_Gets),0,0,
	       (100*(sio.Consistent_Gets+sio.Block_Gets-sio.Physical_Reads)/
       		(sio.Consistent_Gets+sio.Block_Gets))) HitRatio
  FROM
       v$session s
       	, v$process p
       	, v$open_cursor o
	, v$sql  sa
	, v$sess_io sio
 WHERE
	s.paddr=p.addr(+) 
	and s.sid=o.sid(+)
	and s.sid=sio.sid(+)
	and s.sql_address=o.address(+)
	and s.sql_hash_value = sa.hash_value(+) 
)
order by module,"user"
/
set wrap on
</file>

===== prompt.sql =====

<file>
set termout off
spool off
-- doskey macros
host doskey /exename=sqlplus.exe dir=host dir $1
host doskey /exename=sqlplus.exe cd=host cd $1
host doskey /exename=sqlplus.exe e=host start Pfad_zum_Editor $1
set tab off
set trimout on
set time on
col prompt new_value prompt
col prompta new_value prompta
col promptb new_value promptb
col color new_value color
col logdatei new_value logdatei
col wta clear
col wta new_value wta
--select lower(user||'@'||instance_name||decode(instr(host_name,'t'),0,'>','_t>')) as prompt from v$instance;
select lower(user||'@'||instance_name||decode(instr(host_name,'t'),0,'>','_t>')) as prompt
	,(select distinct sid  from v$mystat)||':'||lower(user||'@'||instance_name||decode(instr(host_name,'t'),0,'@','_t@'))||host_name||' Start:'
	||sysdate||' Log: '||'C:\daten\scripte\log\'||to_char(sysdate,'YYMMDD_HH24MI')||'_'||instance_name||'.log' as prompta
--	,'x:\scripte\log\'||to_char(sysdate,'YYMMDD_HH24MI')||'_'||instance_name||'.log' as logdatei 
	,'c:\daten\scripte\log\'||instance_name||'\'||to_char(sysdate,'YYMMDD_HH24MI')||'_'||instance_name||'.log' as logdatei 
from v$instance;

select decode((instr(instance_name,'_T')),0,'','_T') as wta from v$instance;

--select substr(sys_context('USERENV','SESSION_USERID'),1,4)||':&prompta' as promptb from dual;

host title &prompta
--select substr(sys_context('USERENV','DB_NAME'),4,1)||'b' as color from dual;
--select power(substr(sys_context('USERENV','DB_NAME'),4,1),2)||'b' as color from dual;
--select mod(power(substr(sys_context('USERENV','DB_NAME'),4,1),2),10)||'b' as color from dual;
select decode ((sys_context('USERENV','DB_NAME')||'&wta'),
		 'INST1','1B'
		,'INST2','1F'
		,'INST3','1F'
                ....
		,'0B'
		) as color
from dual;
host color &color
set sqlprompt &prompt
alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';
alter session set sort_area_size=1000000;
set heading off
set feedback off
set termout on
select instance_name||'@'||host_name||' started: '||startup_time from v$instance;
set heading on
--spool &logdatei
set feedback on
</file>