Dynamic Tablespace Creation Script

This script generates a dynamic Tablespace Creation (with multiple datafiles) script from my target database for my RDS instance. It deals with smallfile tablespaces.

set serveroutput on
declare

v_knt number :=null;

-- Script to dynamically Generate Tablespace Creation in AWS
-- Nazim Merchant
-- Version 1.0
-- 28 Feb 2018

cursor tbsp is
select  a.tablespace_name
     ,  round(maxbytes/1048576) Max
     ,  round(increment_by/1048576) Inc
     ,  round((maxbytes/1048576)/32000,0) DF
from  ( select  f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes,
               sum(bytes) increment_by
        from dba_data_files f
        group by tablespace_name) a,
      ( select  f.tablespace_name,
               sum(f.bytes)  bytes_free
        from dba_free_space f
        group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
  and a.tablespace_name not in ('SYSTEM','SYSAUX','APEX','USERS')
  and a.tablespace_name not like '%UNDO%';
  

begin

dbms_output.enable(100000);

  for i in tbsp loop

     dbms_output.put_line('CREATE SMALLFILE TABLESPACE ' || i.tablespace_name || ' DATAFILE SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;');
   
     if i.df > 0 then
     
        for j in 1..i.df-1 loop
        
          dbms_output.put_line('ALTER TABLESPACE ' || i.tablespace_name || ' ADD DATAFILE SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;');
          
        end loop;
        
     end if;
     
     end loop;
    


end;
/