163 lines
6.9 KiB
SQL
163 lines
6.9 KiB
SQL
@@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
|