通过移动数据文件来均衡文件I/O

v$filestat视图中可以看出数据文件的读写次数和频率，从而可以对比出数据文件所在磁盘的压力大小，将压力的文件转移到压力小的磁盘来进行优化

col PHYRDS format 999999999

col PHYWRTS format 999999999

col READTIM format 999999999

col WRITETIM format 999999999

col name for a45

select name,phyrds,phywrts,readtim,writetim

from v$filestat a,v$datafile b

where a.file#=b.file#

order by readtim desc

NAME                                              PHYRDS    PHYWRTS    READTIM    WRITETIM

--------------------------------------------- ---------- ---------- ---------- ----------

+DATA/ocm10g/datafile/system.262.865776743          5014       1170        199         21

+DATA/ocm10g/datafile/sysaux.266.865776743          1707       3419         82         36

+DATA/ocm10g/datafile/undotbs1.258.865776743        1017       1784         49         30

+DATA/ocm10g/datafile/example.264.865776777            6          1          0          0

+DATA/ocm10g/datafile/users.257.865776743              4          1          0          0

查看临时文件的视图

select name,PHYRDS,PHYWRTS,READTIM,WRITETIM from v$tempstat a,v$tempfile b where a.file#=b.file#;

假设案例：当表空间tbs1所对应的数据文件所在磁盘/11g/app/....读写繁重，可以迁移到/home/oracle下来减轻压力。这里只是一个示例，真实环境往往不会迁移到/home/oracle目录下

1. 查看表空间tbs1的文件所在目录

select name,phyrds,phywrts,readtim,writetim

from v$filestat a,v$datafile b

where a.file#=b.file#;

NAME                                                   PHYRDS    PHYWRTS    READTIM   WRITETIM

-------------------------------------------------- ---------- ---------- ---------- ----------

/11g/app/oracle/oradata/OCM11G/system01.dbf              5531         30        230          1

/11g/app/oracle/oradata/OCM11G/sysaux01.dbf               597         25         39          1

/11g/app/oracle/oradata/OCM11G/undotbs01.dbf               22         21          3          1

/11g/app/oracle/oradata/OCM11G/users01.dbf                  1          0          0          0

/11g/app/oracle/oradata/OCM11G/example01.dbf                6          0          1          0

/11g/app/oracle/oradata/OCM11G/tbs1.dbf                     0        126          0          1

/11g/app/oracle/oradata/OCM11G/tbs2.dbf                     0        126          0          1

2. 离线tbs1表空间

SYS@OCM11G > alter tablespace tbs1 offline;

3. cp 数据文件到新的路径下

SYS@OCM11G > !cp /11g/app/oracle/oradata/OCM11G/tbs1.dbf /home/oracle/tbs1.dbf

SYS@OCM11G >!ls -l /home/oracle/tbs1.dbf

-rw-r-----. 1 oracle oinstall 10493952 Jan  8 10:42 /home/oracle/tbs1.dbf

4. 重命名tbs1的数据文件

SYS@OCM11G > alter tablespace tbs1 rename datafile '/11g/app/oracle/oradata/OCM11G/tbs1.dbf' to '/home/oracle/tbs1.dbf';

Tablespace altered.

5. 上线tbs1表空间

SYS@OCM11G > alter tablespace tbs1 online;

6. 确认表空间tbs1新路径

SYS@OCM11G >select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/11g/app/oracle/oradata/OCM11G/system01.dbf

/11g/app/oracle/oradata/OCM11G/sysaux01.dbf

/11g/app/oracle/oradata/OCM11G/undotbs01.dbf

/11g/app/oracle/oradata/OCM11G/users01.dbf

/11g/app/oracle/oradata/OCM11G/example01.dbf

/home/oracle/tbs1.dbf

/11g/app/oracle/oradata/OCM11G/tbs2.dbf

7. 删除迁移前文件

SYS@OCM11G > !rm -rf /11g/app/oracle/oradata/OCM11G/tbs1.dbf

参考视图：

READTIM,WRITETIM的单位：in hundredths of second（ 百分之一秒 ）

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

转载于:http://blog.itpub.net/29047826/viewspace-1394361/