Pl/Sql Script to generate DDL script to recreate all tablespaces in Oracle Database.

This Script is shared by my best colleague Ashfaq Ahmed.  

Login as DBA user and execute the following Script to generate DDL script to recreate all tablespaces in Oracle database.


set serveroutput on;

declare
  cursor c1 is select ts#,name from v$tablespace;
  a number;
  b varchar2(800);
  c number;
begin
  dbms_output.enable(60000);
  for c1_rec in c1 loop
      select count(*) into a from v$datafile where ts#=c1_rec.ts#;
        if a=1 then
           select name,round(bytes/1024/1024) into b,c from v$datafile where ts#=c1_rec.ts#;
           dbms_output.put_line('create tablespace '||c1_rec.name||' datafile '||''''||b||''''||' size '||c||'m autoextend on next 4m segment space management auto;');
        elsif a>1 then
           select name,round(bytes/1024/1024) into b,c from v$datafile where ts#=c1_rec.ts# and file#=(select min(file#) from v$datafile
                                                                          where ts#=c1_rec.ts#);
           dbms_output.put_line('create tablespace '||c1_rec.name||' datafile '||''''||b||''''||' size '||c||'m autoextend on next 4m segment space management auto;');
         
           for d_rec in (select name file_name,round(bytes/1024/1024) file_size from v$datafile where ts#=c1_rec.ts# )
           loop
           dbms_output.put_line('alter tablespace '||c1_rec.name||' add datafile '||''''||d_rec.file_name||''''||' size '||d_rec.file_size||'m;');      
           end loop;
         end if;
      end loop;
end;