migration_file_paths.sql

-- destination for datafile,control file, redolog file,temp file, dump file, dump locations
-- parameter file,
--
select distinct file_path
from
(select distinct substr(name,0,instr(name,'/',-1,1)) file_path from  v$datafile
 union all
 select distinct substr(name,0,instr(name,'/',-1,1)) file_path from  v$controlfile
 union all
 select distinct substr(member,0,instr(member,'/',-1,1)) file_path from  v$logfile
 union all
 select distinct substr(name,0,instr(name,'/',-1,1)) file_path from  v$tempfile
 union all
 select distinct substr(value,0,instr(value,'/',-1,1)) file_path from  v$parameter where name like '%dump%'
 union all
 select destination from v$archive_dest where destination is not null
 union all
 select decode(value,null,(select substr(file_spec,0,instr(file_spec,'/lib/libqsmashr.so' ))||'dbs/'
         from dba_libraries
         where library_name = 'DBMS_SUMADV_LIB'
       ),substr(value,0,instr(value,'/',-1,1))) file_path
 from sys.v_$parameter
 where name = 'spfile')
/
-- list listener log
--
lsnrctl status LISTENER_CBFCPPPM|grep -i file