@@header /* * * Author : Vishal Gupta * Purpose : Display Active Service configuration * Parameters : NONE * * Revision History: * =================== * Date Author Description * --------- ------------ ----------------------------------------- * 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 * A C T I V E S E R V I C E S * PROMPT ************************************* COLUMN name HEADING "ServiceName" FORMAT a30 COLUMN network_name HEADING "NetworkName" FORMAT a30 COLUMN goal HEADING "Goal" FORMAT a12 COLUMN clb_goal HEADING "Connection|Load|Balancing|Goal" FORMAT a10 COLUMN dtp HEADING "DTP" FORMAT a3 COLUMN blocked HEADING "Blocked" FORMAT a7 COLUMN aq_ha_notification HEADING "AQ|HA|Notif" FORMAT a5 COLUMN creation_date HEADING "Creation|Date" FORMAT a18 COLUMN Inst_1 HEADING "1" FORMAT a2 COLUMN Inst_2 HEADING "2" FORMAT a2 COLUMN Inst_3 HEADING "3" FORMAT a2 COLUMN Inst_4 HEADING "4" FORMAT a2 COLUMN Inst_5 HEADING "5" FORMAT a2 COLUMN Inst_6 HEADING "6" FORMAT a2 COLUMN Inst_7 HEADING "7" FORMAT a2 COLUMN Inst_8 HEADING "8" FORMAT a2 COLUMN Inst_9 HEADING "9" FORMAT a2 COLUMN Inst_10 HEADING "10" FORMAT a2 COLUMN Inst_11 HEADING "11" FORMAT a2 COLUMN Inst_12 HEADING "12" FORMAT a2 COLUMN Inst_13 HEADING "13" FORMAT a2 COLUMN Inst_14 HEADING "14" FORMAT a2 COLUMN Inst_15 HEADING "15" FORMAT a2 COLUMN Inst_16 HEADING "16" FORMAT a2 SELECT s.name &&_IF_INST1_EXISTS , MAX(DECODE(s.inst_id, 1, 'X')) Inst_1 &&_IF_INST2_EXISTS , MAX(DECODE(s.inst_id, 2, 'X')) Inst_2 &&_IF_INST3_EXISTS , MAX(DECODE(s.inst_id, 3, 'X')) Inst_3 &&_IF_INST4_EXISTS , MAX(DECODE(s.inst_id, 4, 'X')) Inst_4 &&_IF_INST5_EXISTS , MAX(DECODE(s.inst_id, 5, 'X')) Inst_5 &&_IF_INST6_EXISTS , MAX(DECODE(s.inst_id, 6, 'X')) Inst_6 &&_IF_INST7_EXISTS , MAX(DECODE(s.inst_id, 7, 'X')) Inst_7 &&_IF_INST8_EXISTS , MAX(DECODE(s.inst_id, 8, 'X')) Inst_8 &&_IF_INST9_EXISTS , MAX(DECODE(s.inst_id, 9, 'X')) Inst_9 &&_IF_INST10_EXISTS , MAX(DECODE(s.inst_id, 10, 'X')) Inst_10 &&_IF_INST11_EXISTS , MAX(DECODE(s.inst_id, 11, 'X')) Inst_11 &&_IF_INST12_EXISTS , MAX(DECODE(s.inst_id, 12, 'X')) Inst_12 &&_IF_INST13_EXISTS , MAX(DECODE(s.inst_id, 13, 'X')) Inst_13 &&_IF_INST14_EXISTS , MAX(DECODE(s.inst_id, 14, 'X')) Inst_14 &&_IF_INST15_EXISTS , MAX(DECODE(s.inst_id, 15, 'X')) Inst_15 &&_IF_INST16_EXISTS , MAX(DECODE(s.inst_id, 16, 'X')) Inst_16 , MAX(s.clb_goal) clb_goal , MAX(s.goal) goal , MAX(s.dtp) dtp , MAX(s.blocked) blocked , MAX(s.aq_ha_notification) aq_ha_notification , TO_CHAR(MAX(s.creation_date),'DD-MON-YY HH24:MI:SS') creation_date FROM gv$active_services s GROUP BY s.name ORDER BY s.name ; @@footer