Rolling tablespace list


Nothing too spectacular about this one. I recently had a requirement to spread tablespace backups over a number of days (mainly due to tape and disk space). Rather than hardcode the tablespaces (which of course is asking for grief later when new tablespaces are added), the following simple script suffices

select ts#, name
from sys.ts$
where online$ != 3           --  (1=online/2=offline/4=readonly)
and not ( contents$ = 1 and
          dflogging = 0 and 
          bitmapped != 0 )   -- not a temporary tablespace
and mod(ts#,"cycle") = mod(to_char(sysdate,'D'),"cycle");

where "cycle" is the number of days in which all tablespaces must be backed up.