oracle

Renaming and Moving Data Files
=====================================

SELECT tablespace_name FROM dba_data_files WHERE file_name = '&datafile_name';

SELECT file_name, status FROM dba_data_files WHERE tablespace_name = '&tablespace_name';

SELECT status FROM dba_tablespaces WHERE tablespace_name = '&tablespace_name';

ALTER TABLESPACE &tablespace_name READ ONLY;

SELECT status FROM dba_tablespaces WHERE tablespace_name = '&tablespace_name';

cp datafile to target location 

ALTER TABLESPACE &tablespace_name OFFLINE;

ALTER DATABASE RENAME FILE '<old location/old file name>' TO '<new location/new file name>';

ALTER TABLESPACE &tablespace_name ONLINE;

ALTER TABLESPACE &tablespace_name READ WRITE;

SELECT status FROM dba_tablespaces WHERE tablespace_name = '&tablespace_name';

SELECT file_name, status FROM dba_data_files WHERE tablespace_name = '&tablespace_name';