sqlplus | add datafile to tablespace | sql script

set linesize 400
set serveroutput on ;
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "

DECLARE

tbs VARCHAR2(400);
sz NUMBER(10);


v2 VARCHAR(25);
v3 VARCHAR(400);
fsn VARCHAR(400);

mxno NUMBER(10);
F_no NUMBER (10);

CURSOR c1 IS
SELECT bytes,v1.bytes/(1024*1024*1024) as GB,v1.name AS DF_NM,v2.name TBS_NM FROM v$datafile v1 , v$tablespace v2 WHERE v1.ts#=v2.ts# and  v2.name like tbs;
CURSOR c2 IS
SELECT DISTINCT(SubStr(v1.name,1,(instr(v1.name,'/',2))-1)) AS FS FROM v$datafile v1 , v$tablespace v2 WHERE v1.ts#=v2.ts# and  v2.name like tbs;
lcn NUMBER(3);

BEGIN
Dbms_Output.put_line('Enter The Name Of Tablespace');
tbs:='&tbs';
Dbms_Output.put_line('Enter Size Of Datafiles To ADD | 2^');
sz:='&sz';
  IF Mod(sz,32) = 0
    THEN
Dbms_Output.put_line('----------------------------------------------------------------------------------------------');
Dbms_Output.put_line('BYTES --------- GBYTES ----------- FILE-------------------------------------------------------');

     FOR rec IN c1
      LOOP
        Dbms_Output.put_line(rec.bytes||' '||rec.GB||' '||rec.DF_NM||' ' ||rec.TBS_NM);
      END LOOP;

        Dbms_Output.put_line('Used filesystems:');
     FOR rec IN c2
      LOOP
        Dbms_Output.put_line(rec.fs);
      END LOOP;
        Dbms_Output.put_line('Enter Filesystem Name < /fsname >:');
fsn:='&fsn';
    ELSE
  Dbms_Output.put_line('Please Enter Correct Value In GB (mod(32)=0)') ;
    END IF;
lcn:=Round((sz/32),0) ;
 Dbms_Output.put_line(lcn) ;

SELECT col,file# INTO mxno,F_no
  FROM (
SELECT SubStr(col2,1,( InStr(col2,'.') )-1) AS col,creation_time,file# FROM (
SELECT SubStr(col1,(InStr(col1,'_',1))+1,20) AS col2,creation_time,file# FROM (
SELECT SubStr((v1.name),(instr(v1.name,'.dbf',1)-4),(10)) AS col1 ,creation_time,file# FROM  v$datafile v1 , v$tablespace v2 WHERE v1.ts#=v2.ts# and  v2.name like tbs ORDER BY creation_time DESC )  )
  ORDER BY LPAD(col, 10) )
    WHERE
      col = To_Char((
   SELECT Max((TO_NUMBER(col, '99'))) FROM (
      SELECT SubStr(col2,1,( InStr(col2,'.') )-1) AS col,creation_time,file# FROM (
        SELECT SubStr(col1,(InStr(col1,'_',1))+1,20) AS col2,creation_time,file# FROM (
          SELECT SubStr((v1.name),(instr(v1.name,'.dbf',1)-4),(10)) AS col1 ,creation_time,file# FROM  v$datafile v1 , v$tablespace v2 WHERE v1.ts#=v2.ts# and  v2.name like tbs ORDER BY creation_time DESC )  )
          ORDER BY LPAD(col, 10) DESC   ))
      )  ;
  FOR rec2 IN 1..lcn
    LOOP
      mxno:=mxno+1;
      v3:='ALTER TABLESPACE '||tbs||' ADD DATAFILE '''||fsn||'/oradata/'||tbs||'_'||mxno||'.dbf'' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 32000M';
        Dbms_Output.put_line(v3);
--EXECUTE IMMEDIATE v3;
END loop ;
END;
/