SELECT UNIQUE me.ELEM_NAME, mf.FILE_NAME
FROM V$MAP_FILE_IO_STACK fs, V$MAP_FILE mf, V$MAP_ELEMENT me
WHERE mf.FILE_MAP_IDX = fs.FILE_MAP_IDX
AND me.ELEM_IDX = fs.ELEM_IDX
AND me.ELEM_NAME = '/dev/vx/rdmp/c2t1d1s2';
-- query file_name, element_name
WITH fv AS
(SELECT FILE_MAP_IDX, FILE_NAME
FROM V$MAP_FILE
WHERE FILE_NAME = '/oracle/dbs/t_db1.f')
SELECT fv.FILE_NAME, LPAD(' ', 4 * (LEVEL - 1)) || el.ELEM_NAME ELEM_NAME
FROM V$MAP_SUBELEMENT sb,
V$MAP_ELEMENT el,
fv,
(SELECT UNIQUE ELEM_IDX
FROM V$MAP_FILE_IO_STACK io, fv
WHERE io.FILE_MAP_IDX = fv.FILE_MAP_IDX) fs
WHERE el.ELEM_IDX = sb.CHILD_IDX
AND fs.ELEM_IDX = el.ELEM_IDX
START WITH sb.PARENT_IDX IN
(SELECT DISTINCT ELEM_IDX
FROM V$MAP_FILE_EXTENT fe, fv
WHERE fv.FILE_MAP_IDX = fe.FILE_MAP_IDX)
CONNECT BY PRIOR sb.CHILD_IDX = sb.PARENT_IDX;
-- query object
SELECT io.OBJECT_NAME o_name,
io.OBJECT_OWNER o_owner,
io.OBJECT_TYPE o_type,
mf.FILE_NAME,
me.ELEM_NAME,
io.DEPTH,
(SUM(io.CU_SIZE *
(io.NUM_CU -
DECODE(io.PARITY_PERIOD,
0,
0,
TRUNC(io.NUM_CU / io.PARITY_PERIOD)))) / 2) o_size
FROM MAP_OBJECT io, V$MAP_ELEMENT me, V$MAP_FILE mf
WHERE io.OBJECT_NAME = 'BONUS'
AND io.OBJECT_OWNER = 'SCOTT'
AND io.OBJECT_TYPE = 'TABLE'
AND me.ELEM_IDX = io.ELEM_IDX
AND mf.FILE_MAP_IDX = io.FILE_MAP_IDX
GROUP BY io.ELEM_IDX,
io.FILE_MAP_IDX,
me.ELEM_NAME,
mf.FILE_NAME,
io.DEPTH,
io.OBJECT_NAME,
io.OBJECT_OWNER,
io.OBJECT_TYPE
ORDER BY io.DEPTH;
FROM V$MAP_FILE_IO_STACK fs, V$MAP_FILE mf, V$MAP_ELEMENT me
WHERE mf.FILE_MAP_IDX = fs.FILE_MAP_IDX
AND me.ELEM_IDX = fs.ELEM_IDX
AND me.ELEM_NAME = '/dev/vx/rdmp/c2t1d1s2';
-- query file_name, element_name
WITH fv AS
(SELECT FILE_MAP_IDX, FILE_NAME
FROM V$MAP_FILE
WHERE FILE_NAME = '/oracle/dbs/t_db1.f')
SELECT fv.FILE_NAME, LPAD(' ', 4 * (LEVEL - 1)) || el.ELEM_NAME ELEM_NAME
FROM V$MAP_SUBELEMENT sb,
V$MAP_ELEMENT el,
fv,
(SELECT UNIQUE ELEM_IDX
FROM V$MAP_FILE_IO_STACK io, fv
WHERE io.FILE_MAP_IDX = fv.FILE_MAP_IDX) fs
WHERE el.ELEM_IDX = sb.CHILD_IDX
AND fs.ELEM_IDX = el.ELEM_IDX
START WITH sb.PARENT_IDX IN
(SELECT DISTINCT ELEM_IDX
FROM V$MAP_FILE_EXTENT fe, fv
WHERE fv.FILE_MAP_IDX = fe.FILE_MAP_IDX)
CONNECT BY PRIOR sb.CHILD_IDX = sb.PARENT_IDX;
-- query object
SELECT io.OBJECT_NAME o_name,
io.OBJECT_OWNER o_owner,
io.OBJECT_TYPE o_type,
mf.FILE_NAME,
me.ELEM_NAME,
io.DEPTH,
(SUM(io.CU_SIZE *
(io.NUM_CU -
DECODE(io.PARITY_PERIOD,
0,
0,
TRUNC(io.NUM_CU / io.PARITY_PERIOD)))) / 2) o_size
FROM MAP_OBJECT io, V$MAP_ELEMENT me, V$MAP_FILE mf
WHERE io.OBJECT_NAME = 'BONUS'
AND io.OBJECT_OWNER = 'SCOTT'
AND io.OBJECT_TYPE = 'TABLE'
AND me.ELEM_IDX = io.ELEM_IDX
AND mf.FILE_MAP_IDX = io.FILE_MAP_IDX
GROUP BY io.ELEM_IDX,
io.FILE_MAP_IDX,
me.ELEM_NAME,
mf.FILE_NAME,
io.DEPTH,
io.OBJECT_NAME,
io.OBJECT_OWNER,
io.OBJECT_TYPE
ORDER BY io.DEPTH;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29619999/viewspace-1146158/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29619999/viewspace-1146158/