Oracle ----数据文件---- 查询

--各种文件数量
select count(*) from v$tempfile;                       
select count(*) from v$datafile;

--数据文件状态
select  t.online_status,count(*)
from dba_data_files  t
group by  t.online_status ;

--临时段使用情况
COL username FORMAT a10;
COL segtype FORMAT a10;
SELECT username,
       segtype,
       extents "Extents Allocated",
       blocks "Blocks Allocated"
  FROM v$tempseg_usage;

---查看数据文件物理IO信息
SELECT fs.phyrds   "Reads",
       fs.phywrts  "Writes",
       fs.avgiotim "Average I/O Time",
       df.name     "Datafile"
  FROM v$datafile df, v$filestat fs
WHERE df.file# = fs.file#
/

--查看所有数据文件i/o情况

SELECT ts.name      AS ts,
       fs.phyrds    "Reads",
       fs.phywrts   "Writes",
       fs.phyblkrd  AS br,
       fs.phyblkwrt AS bw,
       fs.readtim/100   "RTime*s",
       fs.writetim/100  "WTime*s"
  FROM v$tablespace ts, v$datafile df, v$filestat fs
WHERE ts.ts# = df.ts#
   AND df.file# = fs.file#
UNION
SELECT ts.name      AS ts,
       ts.phyrds    "Reads",
       ts.phywrts   "Writes",
       ts.phyblkrd  AS br,
       ts.phyblkwrt AS bw,
       ts.readtim /100  "RTime*s",
       ts.writetim/100  "WTime*s"
  FROM v$tablespace ts, v$tempfile tf, v$tempstat ts
WHERE ts.ts# = tf.ts#
   AND tf.file# = ts.file#
ORDER BY 1;

---查看热点数据文件(从单块读取时间判断)
COL FILE_NAME FOR A30
COL TABLESPACE_NAME FOR A20
SELECT T.FILE_NAME,
       T.TABLESPACE_NAME,
       ROUND(S.SINGLEBLKRDTIM / S.SINGLEBLKRDS, 2) AS CS,
       S.READTIM/100 READTIME_S,
       S.WRITETIM/100 WIRTETIME_S
  FROM V$FILESTAT S, DBA_DATA_FILES T
WHERE S.FILE# = T.FILE_ID
   AND ROWNUM <= 10
ORDER BY CS DESC
/

select 'CREATE TABLESPACE ' || t.tablespace_name || ' datafile ''' ||
       t1.path || t.tablespace_name || '01.dbf' ||
       ''' SIZE 500M  autoextend on  next 50m ;'
  from dba_tablespaces t,
       (select substr(tt.file_name, 1, instr(tt.file_name, '/', '-1')) path
          from dba_data_files tt
         where rownum = 1) t1
where t.tablespace_name not in ( 'SYSTEM', 'SYSAUX')
   AND T.contents = 'PERMANENT';