select dbid,
snap_id,
cell_hash,
to_number(substr(id,length(id)-8+1),'XXXXXXXX') disk_id,
disk_name,
disk,
decode(con_dbid, 0, dbid, con_dbid) con_dbid,
0 con_id
from (
select d.dbid, d.con_dbid,
d.snap_id,
d.cellhash cell_hash,
extractvalue(value(t),'/celldisk/id') id,
extractvalue(value(t),'/celldisk/name') disk_name,
extractvalue(value(t),'/celldisk/diskType') disk
from wrh$_cell_config_detail d,
wrh$_cell_config c,
table(xmlsequence(extract(xmltype(c.confval),
'/cli-output/celldisk'))) t
where d.dbid = c.dbid
and d.cellhash = c.cellhash
and d.conftype = c.conftype
and d.confval_hash = c.confval_hash
and c.conftype = 'CELLDISK')
SELECT DBID
,
SNAP_ID
,
CELL_HASH
,
TO_NUMBER(SUBSTR(ID
, LENGTH(ID)-8+1)
, 'XXXXXXXX') DISK_ID
,
DISK_NAME
,
DISK
,
DECODE(CON_DBID
, 0
, DBID
, CON_DBID) CON_DBID
,
0 CON_ID
FROM (
SELECT D.DBID
, D.CON_DBID
,
D.SNAP_ID
,
D.CELLHASH CELL_HASH
,
EXTRACTVALUE(VALUE(T)
, '/CELLDISK/ID') ID
,
EXTRACTVALUE(VALUE(T)
, '/CELLDISK/NAME') DISK_NAME
,
EXTRACTVALUE(VALUE(T)
, '/CELLDISK/DISKTYPE') DISK
FROM WRH$_CELL_CONFIG_DETAIL D
,
WRH$_CELL_CONFIG C
,
TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(C.CONFVAL)
,
'/CLI-OUTPUT/CELLDISK'))) T
WHERE D.DBID = C.DBID
AND D.CELLHASH = C.CELLHASH
AND D.CONFTYPE = C.CONFTYPE
AND D.CONFVAL_HASH = C.CONFVAL_HASH
AND C.CONFTYPE = 'CELLDISK')
|
|
|