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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: