Which Tablespaces Do Not Have Enough Free Space

Below script find out which tablespaces don't have space in the database.

column  Today NEW_VALUE mhoy noprint format a1 trunc
column  Time NEW_VALUE mhora noprint format a1 trunc
column  inst NEW_VALUE minst noprint format a1 trunc

select upper(instance) inst
  from v$thread;

set pagesize 53
set linesize 120
set feedback off
ttitle left 'DB Report' -
       right mtoday skip 1 -
       left minst -
       right mtime skip 1 -
       center 'DATAFILES AND SIZES OF TABLESPACES' skip 3;
btitle skip 2 center 'Page :' format 999 sql.pno;

break on Tablespace on report
compute sum of KBTotal on report 
compute sum of KBUsed  on report
compute sum of KBFree  on report

column Tablespace format a12
column File_name  format a32
column status     format a10
column KBTotal    format 99,999,990
column KBUsed     format 99,999,990
column KBFree     format 99,999,990
column %Used      format 990.99
column %Free      format 990.99
column Extents    format 990
column MaxExtentKB format 999,999 

spool tbsp_free

SELECT TO_CHAR(sysdate, 'DD/MM/YY')   Today
     , TO_CHAR(sysdate, 'hh24:mi:ss') Time
     , df.tablespace_name          "Tablespace"
     , df.file_name                "File_name"
     , count(*)                    "Extents"
     , NVL(df.bytes,0)/1024        "KBTotal"
     , (NVL(df.bytes,0) - SUM(NVL(fs.bytes,0)))/1024 "KBUsed"
     , SUM(NVL(fs.bytes,0))/1024   "KBFree"
     , (((NVL(df.bytes,0) - SUM(NVL(fs.bytes,0)))/1024)*100)/((NVL(df.bytes,0)
/1024)) "%Used"
     , ((SUM(NVL(fs.bytes,0))/1024)*100) / (NVL(df.bytes,0)/1024)  "%Free"
     , MAX(NVL(fs.bytes,0))/1024   "MaxExtentKB"
  FROM dba_data_files df
     , dba_free_space fs
 WHERE df.file_id= fs.file_id(+)
 GROUP BY df.tablespace_name
        , df.file_name
        , df.bytes
 ORDER BY df.tablespace_name
/
spool off
ttitle off
btitle off

Redo generated during an Online/Hot Backup

Q) Why is excessive redo generated during an Online/Hot Backup?

A) The excessive redo generated is because of additional information logged into the online redo log during a hot backup the first time a block is modified in a tablespace that is in hot backup mode. In hot backup mode only two things are different:

+ The first time a block is changed in a datafile that is in hot backup mode, the ENTIRE BLOCK is written to the redo log files, not just the changed bytes. Normally only the changed bytes (a redo vector) is written. In hot backup mode, the entire block is logged the FIRST TIME. 

This is because we can get into a situation where the process copying the datafile and DBWR are working on the same block simultaneously. Lets say they are and the OS blocking read factor is 512bytes (the OS reads 512 bytes from disk at a time). The backup program goes to read an 8k Oracle block. The OS gives it 4k. Meanwhile -- DBWR has asked to rewrite this block. the OS schedules the DBWR write to occur right now. The entire 8k block is rewritten. 

The backup program starts running again (multi-tasking OS here) and reads the last 4k of the block. The backup program has now gotten an impossible block, i.e the head and tail are from two points in time. We cannot deal with that during recovery. Hence, We log the entire block image so that during recovery, this block is totally rewritten from redo and is consistent with itself at least. We can recover it from there.

+ The datafile headers which contain the SCN of the last completed checkpoint are NOT updated while a file is in hot backup mode. This lets the recovery process understand what archive redo log files might be needed to fully recover this file. To limit the effect of this additional logging, we should ensure we only place one tablepspace at a time in backup mode and bring the tablespace out of backup mode as soon as we have backed it up. This will reduce the number of blocks that may have to be logged to the minimum possible.

Please note, When we place a tablespace in hot backup mode, Oracle will log extra information for a block the first time it is modified whilst the tablespace it belongs to is in hot backup mode.
Say tablespace X containing file 55 is put into hot backup mode.

We modify block 123 in file 55, this generates redo. Oracle will log the ENTIRE block image instead of just changed bytes.

We perform a commit.

Someone else modifies blocks 123 and 124 in file 55. Oracle will log just changed bytes for block 123 but a full block image copy for 124.

Oracle works with database blocks in 2, 4, 8, 16, or 32k pieces. Consider 8k is what we are using.

Operating systems read and write files using their own chunk size, Say 512 bytes (1/2 k) for example.

So, We are copying a datafile, We are using the OS to perform that. We are reading it in 512 byte chunks.

We start reading block 55 in file 5 using "copy". We got 1/2 of the way through it (meaning we read 4k of data). We get pre-empted and along comes DBWR who decides to lay down a new version of that block on disk (checkpoint, flush dirty buffers). DBWR was lucky enough to write all 8k. Our copy picks back up and reads the next 4k of the block but it is 4k of the block at a totally different point in time.
This is known as a fractured block which happens frequently on a system with many users reading and writing the same file at the same time.

Now, Consider restoring this copy we have a block that is half at time a and half at time b, We cannot recover that block UNLESS we just happened to keep the full block image from the first change after starting the backup elsewhere which is what we did. So, we are able to recover this block using that image.
Note that if we use RMAN, this isn't an issue. RMAN is not affected by OS vs DB block sizes it knows how to read an Oracle datafile safely, with RMAN backups, we don't put a tablespace into backup mode, it is not necessary.

Lets say "block 5" in file "55" was backed up "fractured".

Further, We know the first time block 5 was modified AFTER the backup began we logged the modified image of block 5 in the redo log buffer (and hence into the online redo log files upon commit and hence into the archives upon log switch).

Now, We fail and need to restore this file 55. We go to the backups and we lay down this old copy of file 55. We start processing the archives/online redo logs to catch this file up. We hit the first change for block 5 after the backup was taken and get the entire block image for block 5. So, even if block 5 was totally corrupt on disk say fractured, binary zeros we have block 5 as it should be after that first update. We lay down that entire block and then apply the incremental updates from the arch/redo log files to catch it up.