Script - Clone

APPSPASS=`cat $PASSFILE |grep -v "#" |grep -i $ORASID | awk '{ print $4 }'`


set head off feed off pages 0 embedded off
spool /s01/oracle/aftqasdb/test-auto/mountpoin.sql
select distinct 'df -h '||rpad(NAME,23)from v$datafile;
spool off
!cat mountpoin.sql |grep -i s0 > /s01/oracle/aftqasdb/test-auto/mountpoin_1.sh
exit
sh /s01/oracle/aftqasdb/test-auto/mountpoin_1.sh > /s01/oracle/aftqasdb/test-auto/mountpoin_list.out

cat /s01/oracle/aftqasdb/test-auto/mountpoin_list.out |grep -v "#" |grep -i s0 | awk '{ print $1 $4 }'

cat /s01/oracle/aftqasdb/test-auto/mountpoin_list.out |grep -v "#" |grep -i s0 | awk '{ print $1" "$4 }'

cat /s01/oracle/aftqasdb/test-auto/mountpoin_list.out |grep -v "#" |grep -i s0 | awk '{ print $1","$4 }' > /s01/oracle/aftqasdb/test-auto/mp_list.txt

Create table used to store the mountpoint information:

sqlplus system/sysq4s
create table MP_L_AFTQAS(  MP varchar2(30),MP_FREE varchar2(30) );

Load the data:

sqlldr userid=system/sysq4s control=/s01/oracle/aftqasdb/test-auto/mp_loader.dat log=loader.log

Check the maximum free space in mountpoint
sqlplus system/sysq4s
select MP ||'/oracle/aftqasdata/' from MP_L_AFTQAS where MP_FREE=(select max(MP_FREE) from MP_L_AFTQAS);

Check the maximum free space and add the datafile

spool /s01/oracle/aftqasdb/test-auto/new_file.sql
select
(select ''''||MP ||'/oracle/aftqasdata/' from MP_L_AFTQAS where MP_FREE=(select max(MP_FREE) from MP_L_AFTQAS)) ||SUBSTR (a.file_name, 24)||''','
  from sys.dba_data_files@AFTQAS_AFTPRD.ARVINMERITOR.COM a
where substr(a.file_name,24) not in(select substr(file_name,24) from dba_data_files);
spool off



cat $AUTOMAGIC/config/pwdtab_v11i |grep -v "#" |grep -i aftqas | awk '{ print $4 }' --KK