connect system/manager
 
CREATE OR REPLACE PROCEDURE ADD_DATAFILE
is
   tbs_name VARCHAR2(30);
   
   MAX_FREE NUMBER;
   
   Cursor N1 is 
      Select 
         tablespace_name TBS,
         owner,segment_name SEG_NAME,
         segment_type SEG_TYPE,
         next_extent NEXT_EXT 
      from dba_segments
      where segment_type in ('TABLE','INDEX','CLUSTER','ROLLBACK')
      and owner not in ('SYS','SYSTEM')
      order by 1,4;
BEGIN
   dbms_output.enable;
   dbms_output.put_line(   
                        RPAD('TABLESPACE_NAME',15) ||' '||
                        RPAD('OWNER',12) ||' '||
                        RPAD('SEG_NAME',30) ||' '||
                        RPAD('TYPE',8) ||' '||
                        RPAD('NEXT_EXTENT',12) ||' '||
                        RPAD('MAX_FREE',12)
                        );
                           
   dbms_output.put_line(
                        RPAD('-',15,'-') ||' '||
                        RPAD('-',12,'-') ||' '||
                        RPAD('-',30,'-') ||' '||
                        RPAD('-',8,'-') ||' '||
                        RPAD('-',12,'-') ||' '||
                        RPAD('-',12,'-')
                        );
      
FOR a_rec in N1 LOOP
   tbs_name:=a_rec.TBS;
   
   Select max(bytes) into MAX_FREE from dba_free_space
      where tablespace_name=tbs_name;
 
   IF (MAX_FREE < a_rec.NEXT_EXT) THEN
      dbms_output.put_line(
                           RPAD(a_rec.TBS,15) ||' '||
                           RPAD(a_rec.owner,12) ||' '||
                           RPAD(a_rec.SEG_NAME,30) ||' '||
                           RPAD(a_rec.SEG_TYPE,8) ||' '||
                           RPAD(a_rec.NEXT_EXT,12) ||' '||
                           RPAD(MAX_FREE,12)
                           );
   END IF;
END LOOP;
 
END;
/
show err

<b> 
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/3      PL/SQL: SQL Statement ignored
13/8     PLS-00201: identifier 'SYS.DBA_SEGMENTS' must be declared
38/2     PL/SQL: Statement ignored
38/12    PLS-00364: loop index variable 'A_REC' use is invalid
40/2     PL/SQL: SQL Statement ignored
40/39    PLS-00201: identifier 'SYS.DBA_FREE_SPACE' must be declared
43/2     PL/SQL: Statement ignored
43/17    PLS-00364: loop index variable 'A_REC' use is invalid
</b> 
 
 
 
connect sys/change_on_install
 
grant select on DBA_SEGMENTS to system;
grant select on DBA_FREE_SPACE to system;
 
 
 
connect system/manager
 
CREATE OR REPLACE PROCEDURE ADD_DATAFILE
is
   tbs_name VARCHAR2(30);
   
   MAX_FREE NUMBER;
   
   Cursor N1 is 
      Select 
         tablespace_name TBS,
         owner,segment_name SEG_NAME,
         segment_type SEG_TYPE,
         next_extent NEXT_EXT 
      from dba_segments
      where segment_type in ('TABLE','INDEX','CLUSTER','ROLLBACK')
      and owner not in ('SYS','SYSTEM')
      order by 1,4;
BEGIN
   dbms_output.enable;
   dbms_output.put_line(   
                        RPAD('TABLESPACE_NAME',15) ||' '||
                        RPAD('OWNER',12) ||' '||
                        RPAD('SEG_NAME',30) ||' '||
                        RPAD('TYPE',8) ||' '||
                        RPAD('NEXT_EXTENT',12) ||' '||
                        RPAD('MAX_FREE',12)
                        );
                           
   dbms_output.put_line(
                        RPAD('-',15,'-') ||' '||
                        RPAD('-',12,'-') ||' '||
                        RPAD('-',30,'-') ||' '||
                        RPAD('-',8,'-') ||' '||
                        RPAD('-',12,'-') ||' '||
                        RPAD('-',12,'-')
                        );
      
FOR a_rec in N1 LOOP
   tbs_name:=a_rec.TBS;
   
   Select max(bytes) into MAX_FREE from dba_free_space
      where tablespace_name=tbs_name;
 
   IF (MAX_FREE < a_rec.NEXT_EXT) THEN
      dbms_output.put_line(
                           RPAD(a_rec.TBS,15) ||' '||
                           RPAD(a_rec.owner,12) ||' '||
                           RPAD(a_rec.SEG_NAME,30) ||' '||
                           RPAD(a_rec.SEG_TYPE,8) ||' '||
                           RPAD(a_rec.NEXT_EXT,12) ||' '||
                           RPAD(MAX_FREE,12)
                           );
   END IF;
END LOOP;
 
END;
/
show err
 
<b> 
Procedure created.
</b>