oracle database file mapping

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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29619999/viewspace-1146158/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29619999/viewspace-1146158/