Monthly Archives: November 2021

Pretty Dates in Oracle

The default display for the database for date columns is often DD-MON-RR. So Januray 1st, 2021 would display as 01-JAN-21. This is horrible. The time portion of the date is missing from the display (an Oracle date is always composed of at least the century, year, month, day, hour, minute, and second) and the RR format will lead to issues in 2050 which isn’t that far away.

To get your Oracle tools (like sqlplus and rman for example) on your Unix/Linux server to show dates in a better format, the following will help.

export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"


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