Which Oracle tablespaces have the most write activity going on?

One of my customers is deploying a new driver for their VMWare Cluster SAN next week. The last time they did this (about a year ago), there were random disk corruptions on the database servers. Needless to say, this was bad.

This time the VMWare team is going to be on the lookout for corruption, but the database team wanted to also do an extra check and run an rman validate command on the “datafiles with the most write activity”.

The following query will give you a list of tablespaces with write activity over the last hour with the highest write activity.

  select tablespace_name
       , sum(physical_block_writes) as total_last_hour_block_writes
    from v$filemetric_history
    join dba_data_files using (file_id)
group by tablespace_name
  having sum(physical_block_writes) > 0
order by sum(physical_block_writes) desc;

Hopefully, this helps someone in the future.

Photo courtesy of Gratisography