@@header /* * * Author : Vishal Gupta * Purpose : Display Service configuration in the database * 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 * S E R V I C E S * PROMPT ********************** COLUMN name HEADING "ServiceName" FORMAT a30 COLUMN network_name HEADING "NetworkName" FORMAT a30 COLUMN enabled HEADING "Enabled" FORMAT a7 COLUMN failover_method HEADING "Failover|Method" FORMAT a10 COLUMN failover_type HEADING "Failover|Type" FORMAT a10 COLUMN failover_retries HEADING "Failover|Retries" FORMAT 999 COLUMN failover_delay HEADING "Failover|Delay" FORMAT 999 COLUMN min_cardinality HEADING "Min|Card" FORMAT 999 COLUMN max_cardinality HEADING "Max|Card" FORMAT 999 COLUMN goal HEADING "Goal" FORMAT a15 COLUMN clb_goal HEADING "CLB|Goal" 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 --, s.network_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, 12, 'X')) Inst_12 &&_IF_INST13_EXISTS , MAX(DECODE(a.inst_id, 13, 'X')) Inst_13 &&_IF_INST14_EXISTS , MAX(DECODE(a.inst_id, 14, 'X')) Inst_14 &&_IF_INST15_EXISTS , MAX(DECODE(a.inst_id, 15, 'X')) Inst_15 &&_IF_INST16_EXISTS , MAX(DECODE(a.inst_id, 16, 'X')) Inst_16 , s.enabled --(This column is reserved by Oracle for internal use, checked in 11.2.0.2) , s.failover_method , s.failover_type , s.failover_retries , s.failover_delay , s.min_cardinality , s.max_cardinality , s.goal , s.clb_goal , s.aq_ha_notifications --, s.edition , TO_CHAR(s.creation_date,'DD-MON-YY HH24:MI:SS') creation_date FROM dba_services s LEFT OUTER JOIN gv$active_services a ON a.name = s.name GROUP BY s.name , s.network_name , s.enabled , s.failover_method , s.failover_type , s.failover_retries , s.failover_delay , s.min_cardinality , s.max_cardinality , s.goal , s.clb_goal , s.aq_ha_notifications --, s.edition , TO_CHAR(s.creation_date,'DD-MON-YY HH24:MI:SS') ORDER BY s.name / @@footer