76 lines
2.5 KiB
MySQL
76 lines
2.5 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Exadata IORM
|
|
* Parameters : 1 - Cell Name (Use % for wildcard)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 20-Feb-13 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE cell_name
|
|
|
|
DEFINE cell_name="&&1"
|
|
|
|
set term off
|
|
COLUMN _cell_name NEW_VALUE cell_name NOPRINT
|
|
|
|
SELECT DECODE('&&cell_name','','%','&&cell_name') "_cell_name"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
PROMPT
|
|
PROMPT ****************************************************
|
|
PROMPT * E X A D A T A I O R M
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * CellName = '&&cell_name'
|
|
PROMPT ****************************************************
|
|
|
|
COLUMN cellname HEADING "CellName" FORMAT a18
|
|
COLUMN iorm_name HEADING "InterDB PlanName" FORMAT a20
|
|
COLUMN status HEADING "Status" FORMAT a15
|
|
COLUMN objective HEADING "Objective" FORMAT a15
|
|
COLUMN catPlan HEADING "Category Plan" FORMAT a25
|
|
COLUMN dbPlan HEADING "DB Plan" FORMAT a25
|
|
|
|
BREAK ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF physicalSize FORMAT 99,999,999,999 ON REPORT
|
|
|
|
SELECT clioutput.cellname
|
|
, iorm.iorm_name
|
|
, iorm.status
|
|
, iorm.objective
|
|
, iorm.catPlan
|
|
, iorm.dbPlan
|
|
FROM v$cell_config cc
|
|
, XMLTable('/cli-output' PASSING XMLTYPE(confval)
|
|
COLUMNS
|
|
cellname VARCHAR2(100) PATH 'context/@cell'
|
|
, interdatabaseplan XMLTYPE PATH 'interdatabaseplan'
|
|
) clioutput
|
|
, XMLTable('/interdatabaseplan' PASSING clioutput.interdatabaseplan
|
|
COLUMNS
|
|
iorm_name VARCHAR2(100) PATH 'name'
|
|
, catPlan VARCHAR2(100) PATH 'catPlan'
|
|
, dbPlan VARCHAR2(100) PATH 'dbPlan'
|
|
, objective VARCHAR2(100) PATH 'objective'
|
|
, status VARCHAR2(100) PATH 'status'
|
|
) iorm
|
|
WHERE cc.conftype = 'IORM'
|
|
AND UPPER(clioutput.cellname) LIKE UPPER('&&cell_name')
|
|
ORDER BY clioutput.cellname
|
|
;
|
|
|
|
|
|
@@footer |