REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_dbf_migrate.sql
REM
REM <SQLDIR_GRP>DBF TABSP REVERSE</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	When migrating to a different host sometimes
REM	it is necessary to balance datafiles on new
REM	file systems to make sure you do not go over
REM	the available space.
REM	--
REM	For instance if your original host had 20GB
REM	file systems and your new host is only 12GB
REM	you can find this script very handy.
REM	--
REM	This script should be ran on the sourse instance
REM	before the migration.  It will display a line "---"
REM	when SUM of all datafiles reached the limit you
REM	specified
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_dbf_migrate.sql
REM 
REM Example:
REM	s_dbf_migrate.sql
REM
REM
REM History:
REM	08-01-2001	VMOGILEV	Created
REM
REM

set lines 132
set trims on
set serveroutput on size 1000000

spool s_dbf_balance.log

declare
   cursor dbf_c is
      select file_name, bytes/1024 kbytes, substr(file_name,instr(file_name,'/',-1)) jfile
      from dba_data_files
      ;
   l_sum number := 0;
begin
   for dbf in dbf_c loop
      dbms_output.put_line(rpad(dbf.file_name,45,' ')||rpad(dbf.jfile,20,' ')||lpad(dbf.kbytes,25,' ')||' KB');
      l_sum := l_sum + dbf.kbytes;
      if l_sum > &gb_per_fs*1024*1024 then
         dbms_output.put_line(lpad(' new FS total: '||l_sum||' KB',73,'-'));
         l_sum := 0;
      end if;
   end loop;
end;
/

spool off