Time difference

Query to get the time different between 2 date columns

select floor(((end_time - start_time)*24*60*60)/3600)
       || ' HOURS ' ||
       floor((((end_time - start_time)*24*60*60) -
       floor(((end_time - start_time)*24*60*60)/3600)*3600)/60)
       || ' MINUTES ' ||
       round((((end_time - start_time)*24*60*60) -
       floor(((end_time - start_time)*24*60*60)/3600)*3600 -
       (floor((((end_time - start_time)*24*60*60) -
       floor(((end_time - start_time)*24*60*60)/3600)*3600)/60)*60)))
       || ' SECS ' time_difference
from sub_email_jobs;

Excel Removing or adding hyperlinks

Useful code to remove and add hyperlinks in Excel using VB

Public Sub Convert_To_Hyperlinks()
    Dim Cell As Range
    For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
        If Cell <> "" Then
            ActiveSheet.Hyperlinks.Add Cell, Cell.Value
        End If
    Next
End Sub

Sub RemoveHyperlinks()

'Remove all hyperlinks from the active sheet
ActiveSheet.Hyperlinks.Delete

End Sub

Datafile Usage on an Oracle Database

Two useful queries to get datafile usage on an Oracle database

SELECT SUBSTR (df.tablespace_name, 1, 20) "Tablespace Name",
       SUBSTR (df.file_name, 1, 40) "File Name",
       ROUND (df.bytes / 1024 / 1024, 2) "Size (M)",
       ROUND (e.used_bytes / 1024 / 1024, 2) "Used (M)",
       ROUND (f.free_bytes / 1024 / 1024, 2) "Free (M)",
       RPAD (' ' || RPAD ('X', ROUND (e.used_bytes * 10 / df.bytes, 0), 'X'),
             11,
             '-')
           "% Used"
  FROM dba_data_files df,
       (SELECT file_id, SUM (DECODE (bytes, NULL, 0, bytes)) used_bytes
          FROM dba_extents
        GROUP BY file_id) e,
       (SELECT MAX (bytes) free_bytes, file_id
          FROM dba_free_space
        GROUP BY file_id) f
 WHERE e.file_id = df.file_id AND df.file_id = f.file_id
ORDER BY df.tablespace_name, df.file_name;

SELECT SUBSTR (df.name, 1, 40) file_name,
       df.bytes / 1024 / 1024 allocated_mb,
       ( (df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
           used_mb,
       NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
  FROM v$datafile df, dba_free_space dfs
 WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id,
         df.name,
         df.file#,
         df.bytes
ORDER BY file_name;