查看Oracle数据文件和磁盘i/o情况
--查询当前用户使用了多少还原表空间
select d.username,c.name,b.writes
from v$transaction a,v$rollstat b,v$rollname c,v$session d
where d.taddr=a.addr
and a.xidusn=b.usn
and b.usn=c.usn
order by d.username;
对于DML事务繁忙的数据库,还原表空间数据文件应存放到单独的i/o位置;
---查看表空间的使用情况
SQL> col tablespace for a10
SQL> select ts.name tablespace,fs.phyrds "reads",fs.phywrts "writes",fs.phyblkrd ,fs.phyblkwrt,
2 fs.readtim "rtime",fs.writetim "wtime"
3 from v$tablespace ts,v$datafile df,v$filestat fs
4 where ts.ts#=df.ts#
5 and df.file#=fs.file#;
TABLESPACE reads writes PHYBLKRD PHYBLKWRT rtime wtime
---------- ---------- ---------- ---------- ---------- ---------- ----------
USERS 19 15 19