133 lines
5.6 KiB
SQL
133 lines
5.6 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Service Distribution among RAC instance
|
|
* Parameters : NONE
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 30-Oct-12 Vishal Gupta Removed gv$services from query, as its not needed
|
|
* 20-Jun-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
set term on
|
|
|
|
|
|
define _IF_INST1_EXISTS="--"
|
|
define _IF_INST2_EXISTS="--"
|
|
define _IF_INST3_EXISTS="--"
|
|
define _IF_INST4_EXISTS="--"
|
|
define _IF_INST5_EXISTS="--"
|
|
define _IF_INST6_EXISTS="--"
|
|
define _IF_INST7_EXISTS="--"
|
|
define _IF_INST8_EXISTS="--"
|
|
define _IF_INST9_EXISTS="--"
|
|
define _IF_INST10_EXISTS="--"
|
|
define _IF_INST11_EXISTS="--"
|
|
define _IF_INST12_EXISTS="--"
|
|
define _IF_INST13_EXISTS="--"
|
|
define _IF_INST14_EXISTS="--"
|
|
define _IF_INST15_EXISTS="--"
|
|
define _IF_INST16_EXISTS="--"
|
|
|
|
col INST1_EXISTS new_value _IF_INST1_EXISTS noprint
|
|
col INST2_EXISTS new_value _IF_INST2_EXISTS noprint
|
|
col INST3_EXISTS new_value _IF_INST3_EXISTS noprint
|
|
col INST4_EXISTS new_value _IF_INST4_EXISTS noprint
|
|
col INST5_EXISTS new_value _IF_INST5_EXISTS noprint
|
|
col INST6_EXISTS new_value _IF_INST6_EXISTS noprint
|
|
col INST7_EXISTS new_value _IF_INST7_EXISTS noprint
|
|
col INST8_EXISTS new_value _IF_INST8_EXISTS noprint
|
|
col INST9_EXISTS new_value _IF_INST9_EXISTS noprint
|
|
col INST10_EXISTS new_value _IF_INST10_EXISTS noprint
|
|
col INST11_EXISTS new_value _IF_INST11_EXISTS noprint
|
|
col INST12_EXISTS new_value _IF_INST12_EXISTS noprint
|
|
col INST13_EXISTS new_value _IF_INST13_EXISTS noprint
|
|
col INST14_EXISTS new_value _IF_INST14_EXISTS noprint
|
|
col INST15_EXISTS new_value _IF_INST15_EXISTS noprint
|
|
col INST16_EXISTS new_value _IF_INST16_EXISTS noprint
|
|
|
|
set term off
|
|
SELECT MIN(DECODE(inst_id,1,' ', '--')) INST1_EXISTS
|
|
, MIN(DECODE(inst_id,2,' ', '--')) INST2_EXISTS
|
|
, MIN(DECODE(inst_id,3,' ', '--')) INST3_EXISTS
|
|
, MIN(DECODE(inst_id,4,' ', '--')) INST4_EXISTS
|
|
, MIN(DECODE(inst_id,5,' ', '--')) INST5_EXISTS
|
|
, MIN(DECODE(inst_id,6,' ', '--')) INST6_EXISTS
|
|
, MIN(DECODE(inst_id,7,' ', '--')) INST7_EXISTS
|
|
, MIN(DECODE(inst_id,8,' ', '--')) INST8_EXISTS
|
|
, MIN(DECODE(inst_id,9,' ', '--')) INST9_EXISTS
|
|
, MIN(DECODE(inst_id,10,' ', '--')) INST10_EXISTS
|
|
, MIN(DECODE(inst_id,11,' ', '--')) INST11_EXISTS
|
|
, MIN(DECODE(inst_id,12,' ', '--')) INST12_EXISTS
|
|
, MIN(DECODE(inst_id,13,' ', '--')) INST13_EXISTS
|
|
, MIN(DECODE(inst_id,14,' ', '--')) INST14_EXISTS
|
|
, MIN(DECODE(inst_id,15,' ', '--')) INST15_EXISTS
|
|
, MIN(DECODE(inst_id,16,' ', '--')) INST16_EXISTS
|
|
FROM gv$instance
|
|
GROUP BY version
|
|
;
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT *************************************************
|
|
PROMPT * S E R V I C E S D I S T R I B U T I O N *
|
|
PROMPT *************************************************
|
|
|
|
COLUMN name HEADING "Service Name" FORMAT a30
|
|
COLUMN Inst_1 HEADING " Inst1 " FORMAT a7
|
|
COLUMN Inst_2 HEADING " Inst2 " FORMAT a7
|
|
COLUMN Inst_3 HEADING " Inst3 " FORMAT a7
|
|
COLUMN Inst_4 HEADING " Inst4 " FORMAT a7
|
|
COLUMN Inst_5 HEADING " Inst5 " FORMAT a7
|
|
COLUMN Inst_6 HEADING " Inst6 " FORMAT a7
|
|
COLUMN Inst_7 HEADING " Inst7 " FORMAT a7
|
|
COLUMN Inst_8 HEADING " Inst8 " FORMAT a7
|
|
COLUMN Inst_9 HEADING " Inst9 " FORMAT a7
|
|
COLUMN Inst_10 HEADING " Inst10 " FORMAT a7
|
|
COLUMN Inst_11 HEADING " Inst11 " FORMAT a7
|
|
COLUMN Inst_12 HEADING " Inst12 " FORMAT a7
|
|
COLUMN Inst_13 HEADING " Inst13 " FORMAT a7
|
|
COLUMN Inst_14 HEADING " Inst14 " FORMAT a7
|
|
COLUMN Inst_15 HEADING " Inst15 " FORMAT a7
|
|
COLUMN Inst_16 HEADING " Inst16 " FORMAT a7
|
|
COLUMN end_column HEADING "" FORMAT a1
|
|
|
|
set colsep "|"
|
|
set recsepchar "|"
|
|
|
|
SELECT a.name
|
|
&&_IF_INST1_EXISTS , MAX(DECODE(a.inst_id, 1, ' X ')) Inst_1
|
|
&&_IF_INST2_EXISTS , MAX(DECODE(a.inst_id, 2, ' X ')) Inst_2
|
|
&&_IF_INST3_EXISTS , MAX(DECODE(a.inst_id, 3, ' X ')) Inst_3
|
|
&&_IF_INST4_EXISTS , MAX(DECODE(a.inst_id, 4, ' X ')) Inst_4
|
|
&&_IF_INST5_EXISTS , MAX(DECODE(a.inst_id, 5, ' X ')) Inst_5
|
|
&&_IF_INST6_EXISTS , MAX(DECODE(a.inst_id, 6, ' X ')) Inst_6
|
|
&&_IF_INST7_EXISTS , MAX(DECODE(a.inst_id, 7, ' X ')) Inst_7
|
|
&&_IF_INST8_EXISTS , MAX(DECODE(a.inst_id, 8, ' X ')) Inst_8
|
|
&&_IF_INST9_EXISTS , MAX(DECODE(a.inst_id, 9, ' X ')) Inst_9
|
|
&&_IF_INST10_EXISTS , MAX(DECODE(a.inst_id, 10, ' X ')) Inst_10
|
|
&&_IF_INST11_EXISTS , MAX(DECODE(a.inst_id, 11, ' X ')) Inst_11
|
|
&&_IF_INST12_EXISTS , MAX(DECODE(a.inst_id, 11, ' X ')) Inst_12
|
|
&&_IF_INST13_EXISTS , MAX(DECODE(a.inst_id, 11, ' X ')) Inst_13
|
|
&&_IF_INST14_EXISTS , MAX(DECODE(a.inst_id, 11, ' X ')) Inst_14
|
|
&&_IF_INST15_EXISTS , MAX(DECODE(a.inst_id, 11, ' X ')) Inst_15
|
|
&&_IF_INST16_EXISTS , MAX(DECODE(a.inst_id, 11, ' X ')) Inst_16
|
|
, '' end_column
|
|
FROM gv$active_services a
|
|
GROUP BY a.name
|
|
ORDER BY a.name
|
|
/
|
|
|
|
set colsep " "
|
|
set recsepchar " "
|
|
|
|
@@footer
|