Just about everything you need is in the data dictionary, all you
need is to run a query against the DB to create the script. This will
take care of the bulk of the script generation but you should be
careful when running this script and be sure to do your due diligence
regarding ensuring everything is taken care of. You will need to backup
the control, have a list of the files and locations, ensure there are no
datafiles with the same name in different filesystems, ensure there are
no files with spaces or confusing/erroneous errors, etc.
A quick hot backup script to place each tablespace in backup mode, copy the files, take the tablespace out of backup mode....
select distinct a.dbcmd,''||chr(13)||oscmd,''||chr(13)||c.dbcmd from
(select 'alter tablespace '||tablespace_name||' begin
backup;'dbcmd,tablespace_name from dba_tablespaces group by
tablespace_name)a,
(select '! cp '||file_name||' /backup_dir'oscmd,tablespace_name from dba_data_files)b,
(select 'alter tablespace '||tablespace_name||' end
backup;'dbcmd,tablespace_name from dba_tablespaces group by
tablespace_name)c
where a.tablespace_name = b.tablespace_name
and c.tablespace_name = b.tablespace_name;
