SELECT
  k.inst_id,                               -- INST_ID NUMBER
  k.FILENO_KSFDSTFILE,                     -- FILE_NO NUMBER
  2,                                       -- FILETYPE_ID NUMBER
  'Data File',                             -- FILETYPE_NAME VARCHAR2(28)
  round(k.SBRDATA_KSFDSTFILE / 2048),      -- SMALL_READ_MEGABYTES NUMBER
  round(k.SBWDATA_KSFDSTFILE / 2048),      -- SMALL_WRITE_MEGABYTES NUMBER
  round(k.MBRDATA_KSFDSTFILE / 2048),      -- LARGE_READ_MEGABYTES NUMBER
  round(k.MBWDATA_KSFDSTFILE / 2048),      -- LARGE_WRITE_MEGABYTES NUMBER
  k.SBRSREQS_KSFDSTFILE,                   -- SMALL_READ_REQS NUMBER
  k.SBWSREQS_KSFDSTFILE,                   -- SMALL_WRITE_REQS NUMBER
  k.SSBRREQS_KSFDSTFILE,                   -- SMALL_SYNC_READ_REQS NUMBER
  k.MBRSREQS_KSFDSTFILE,                   -- LARGE_READ_REQS NUMBER
  k.MBWSREQS_KSFDSTFILE,                   -- LARGE_WRITE_REQS NUMBER
  k.SBRSERV_KSFDSTFILE,                    -- SMALL_READ_SERVICETIME NUMBER
  k.SBWSERV_KSFDSTFILE,                    -- SMALL_WRITE_SERVICETIME NUMBER
  k.SSBRLATENCY_KSFDSTFILE,                -- SMALL_SYNC_READ_LATENCY NUMBER
  k.MBRSERV_KSFDSTFILE,                    -- LARGE_READ_SERVICETIME NUMBER
  k.MBWSERV_KSFDSTFILE,                    -- LARGE_WRITE_SERVICETIME NUMBER
  decode(                                  -- ASYNCH_IO VARCHAR2(9)
    bitand(k.FLAGS_KSFDSTFILE, 4), 0, 'ASYNC_OFF', 'ASYNC_ON'),
  decode(                                  -- ACCESS_METHOD VARCHAR2(11)
    k.ACCESS_KSFDSTFILE, 1,'OS_LIB', 2,'ODM_LIB',3, 'ASM_MANAGED',4,'DNFS_LIB'),
  k.RETRIES_KSFDSTFILE                     -- RETRIES_ON_ERROR NUMBER
FROM
  X$KSFDSTFILE k,
  x$kccfe f
where
  f.fedup <> 0 and f.fenum=k.FILENO_KSFDSTFILE and k.FILETYPE_KSFDSTFILE=2
union
SELECT
  k.inst_id,                               -- INST_ID NUMBER
  k.FILENO_KSFDSTFILE,                     -- FILE_NO NUMBER
  6,                                       -- FILETYPE_ID NUMBER
  'Temp File',                             -- FILETYPE_NAME VARCHAR2(28)
  round(k.SBRDATA_KSFDSTFILE / 2048),      -- SMALL_READ_MEGABYTES NUMBER
  round(k.SBWDATA_KSFDSTFILE / 2048),      -- SMALL_WRITE_MEGABYTES NUMBER
  round(k.MBRDATA_KSFDSTFILE / 2048),      -- LARGE_READ_MEGABYTES NUMBER
  round(k.MBWDATA_KSFDSTFILE / 2048),      -- LARGE_WRITE_MEGABYTES NUMBER
  k.SBRSREQS_KSFDSTFILE,                   -- SMALL_READ_REQS NUMBER
  k.SBWSREQS_KSFDSTFILE,                   -- SMALL_WRITE_REQS NUMBER
  k.SSBRREQS_KSFDSTFILE,                   -- SMALL_SYNC_READ_REQS NUMBER
  k.MBRSREQS_KSFDSTFILE,                   -- LARGE_READ_REQS NUMBER
  k.MBWSREQS_KSFDSTFILE,                   -- LARGE_WRITE_REQS NUMBER
  k.SBRSERV_KSFDSTFILE,                    -- SMALL_READ_SERVICETIME NUMBER
  k.SBWSERV_KSFDSTFILE,                    -- SMALL_WRITE_SERVICETIME NUMBER
  k.SSBRLATENCY_KSFDSTFILE,                -- SMALL_SYNC_READ_LATENCY NUMBER
  k.MBRSERV_KSFDSTFILE,                    -- LARGE_READ_SERVICETIME NUMBER
  k.MBWSERV_KSFDSTFILE,                    -- LARGE_WRITE_SERVICETIME NUMBER
  decode(                                  -- ASYNCH_IO VARCHAR2(9)
    bitand(k.FLAGS_KSFDSTFILE, 4), 0, 'ASYNC_OFF', 'ASYNC_ON'),
  decode(                                  -- ACCESS_METHOD VARCHAR2(11)
    k.ACCESS_KSFDSTFILE, 1,'OS_LIB', 2,'ODM_LIB',3, 'ASM_MANAGED',4,'DNFS_LIB'),
  k.RETRIES_KSFDSTFILE                     -- RETRIES_ON_ERROR NUMBER
FROM
  X$KSFDSTFILE k,
  x$kcctf f
where
  f.tfdup <> 0 and f.tfnum=k.FILENO_KSFDSTFILE and k.FILETYPE_KSFDSTFILE=6
union
SELECT
  k.inst_id,                               -- INST_ID NUMBER
  k.FILENO_KSFDSTFILE,                     -- FILE_NO NUMBER
  k.FILETYPE_KSFDSTFILE,                   -- FILETYPE_ID NUMBER
  decode(                                  -- FILETYPE_NAME VARCHAR2(28)
    k.FILETYPE_KSFDSTFILE, 1,'Control File', 3,'Log File', 4,'Archive Log', 9,'Data File Backup', 10,'Data File Incremental Backup', 11,'Archive Log Backup', 12,'Data File Copy', 17,'Flashback Log', 18,'Data Pump Dump File', 'Other'),
  round(k.SBRDATA_KSFDSTFILE / 2048),      -- SMALL_READ_MEGABYTES NUMBER
  round(k.SBWDATA_KSFDSTFILE / 2048),      -- SMALL_WRITE_MEGABYTES NUMBER
  round(k.MBRDATA_KSFDSTFILE / 2048),      -- LARGE_READ_MEGABYTES NUMBER
  round(k.MBWDATA_KSFDSTFILE / 2048),      -- LARGE_WRITE_MEGABYTES NUMBER
  k.SBRSREQS_KSFDSTFILE,                   -- SMALL_READ_REQS NUMBER
  k.SBWSREQS_KSFDSTFILE,                   -- SMALL_WRITE_REQS NUMBER
  k.SSBRREQS_KSFDSTFILE,                   -- SMALL_SYNC_READ_REQS NUMBER
  k.MBRSREQS_KSFDSTFILE,                   -- LARGE_READ_REQS NUMBER
  k.MBWSREQS_KSFDSTFILE,                   -- LARGE_WRITE_REQS NUMBER
  k.SBRSERV_KSFDSTFILE,                    -- SMALL_READ_SERVICETIME NUMBER
  k.SBWSERV_KSFDSTFILE,                    -- SMALL_WRITE_SERVICETIME NUMBER
  k.SSBRLATENCY_KSFDSTFILE,                -- SMALL_SYNC_READ_LATENCY NUMBER
  k.MBRSERV_KSFDSTFILE,                    -- LARGE_READ_SERVICETIME NUMBER
  k.MBWSERV_KSFDSTFILE,                    -- LARGE_WRITE_SERVICETIME NUMBER
  decode(                                  -- ASYNCH_IO VARCHAR2(9)
    bitand(k.FLAGS_KSFDSTFILE, 4), 0, 'ASYNC_OFF', 'ASYNC_ON'),
  decode(                                  -- ACCESS_METHOD VARCHAR2(11)
    k.ACCESS_KSFDSTFILE, 1,'OS_LIB', 2,'ODM_LIB',3, 'ASM_MANAGED',4,'DNFS_LIB'),
  k.RETRIES_KSFDSTFILE                     -- RETRIES_ON_ERROR NUMBER
FROM
  X$KSFDSTFILE k
where
  filetype_ksfdstfile in (1,3,4,9,10,11,12, 17,18)
union
SELECT
  k.inst_id,                               -- INST_ID NUMBER
  0,                                       -- FILE_NO NUMBER
  0,                                       -- FILETYPE_ID NUMBER
  'Other',                                 -- FILETYPE_NAME VARCHAR2(28)
  round(sum(k.SBRDATA_KSFDSTFILE) / 2048), -- SMALL_READ_MEGABYTES NUMBER
  round(sum(k.SBWDATA_KSFDSTFILE) / 2048), -- SMALL_WRITE_MEGABYTES NUMBER
  round(sum(k.MBRDATA_KSFDSTFILE) / 2048), -- LARGE_READ_MEGABYTES NUMBER
  round(sum(k.MBWDATA_KSFDSTFILE) / 2048), -- LARGE_WRITE_MEGABYTES NUMBER
  sum(k.SBRSREQS_KSFDSTFILE),              -- SMALL_READ_REQS NUMBER
  sum(k.SBWSREQS_KSFDSTFILE),              -- SMALL_WRITE_REQS NUMBER
  sum(k.SSBRREQS_KSFDSTFILE),              -- SMALL_SYNC_READ_REQS NUMBER
  sum(k.MBRSREQS_KSFDSTFILE),              -- LARGE_READ_REQS NUMBER
  sum(k.MBWSREQS_KSFDSTFILE),              -- LARGE_WRITE_REQS NUMBER
  sum(k.SBRSERV_KSFDSTFILE),               -- SMALL_READ_SERVICETIME NUMBER
  sum(k.SBWSERV_KSFDSTFILE),               -- SMALL_WRITE_SERVICETIME NUMBER
  sum(k.SSBRLATENCY_KSFDSTFILE),           -- SMALL_SYNC_READ_LATENCY NUMBER
  sum(k.MBRSERV_KSFDSTFILE),               -- LARGE_READ_SERVICETIME NUMBER
  sum(k.MBWSERV_KSFDSTFILE),               -- LARGE_WRITE_SERVICETIME NUMBER
  'ASYNC_OFF',                             -- ASYNCH_IO VARCHAR2(9)
  'OS_LIB',                                -- ACCESS_METHOD VARCHAR2(11)
  sum(k.RETRIES_KSFDSTFILE)                -- RETRIES_ON_ERROR NUMBER
FROM
  X$KSFDSTFILE k
where
  filetype_ksfdstfile in (5,8,13,14,15,16, 19,20,21,22)
group by
  k.inst_id