exec dbms_output.enable(10000);
set lines 130 pages 1000
set serveroutput on size 1000000
declare
cursor c1 is
select substr(name,1,61) complete_filename, bytes/(1024) KB,
substr(name, instr(name, ‘/’, -1)+1) only_filename
from v$datafile order by file#;
threshold_kb number(10) := 94000000 ;
size_counter number := 0;
mountpoint_counter number := 1;
mountpoint_prefix varchar2(10) := ‘/800t’;
common_mountpoint_prefix varchar2(15) := ‘/oradata/800T/’;
target_filename varchar2(61);
begin
for cur in c1 loop
if size_counter >= threshold_kb then
dbms_output.put_line (‘*************** Mountpoint ‘ || mountpoint_counter || ‘ Size reached ‘ || size_counter);
size_counter := 0;
mountpoint_counter := mountpoint_counter + 1;
end if;
select mountpoint_prefix || mountpoint_counter || common_mountpoint_prefix || cur.only_filename
into target_filename
from dual;
dbms_output.put_line(‘ panther1:’ || target_filename );
— dbms_output.put_line(‘scp -p ‘ || cur.complete_filename || ‘ 127.0.0.1:’ || target_filename || ‘;’);
— dbms_output.put_line(‘cp -p ‘ || cur.complete_filename || ‘ panther1:’ || target_filename || ‘;’);
size_counter := size_counter + cur.KB;
end loop;
dbms_output.put_line (‘*************** Mountpoint ‘ || mountpoint_counter || ‘ Size reached ‘ || size_counter);
end;
/