How to Rename or Move Oracle Tablespace Datafile to Another Location

link

Oracle database does not provide an easy user interface to rename a datafile of tablespace, nor database administrator can easily move or relocate the datafile to another location or directory that different from original location on creation of database. The rename or move place task has to be performed via Oracle SQLPlus command line interface. However, if the operation is performed when the tablespace which owns the datefile is online, error will occur.

The error message may include the following:

ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file – file is in use or recovery
ORA-01110: data file : ‘datafile.dbf’

To properly move the datafile around or rename the datafile, follow this guide:

Login to SQLPlus.
Connect as SYS DBA with CONNECT / AS SYSDBA command.
Shutdown the database instance with SHUTDOWN command.
Rename or/and move the datafiles at operating system level.
Start Oracle database in mount state with STARTUP MOUNT command.
Modify the name or location of datafiles in Oracle data dictionary using following command syntax:
ALTER DATABASE RENAME FILE ‘’ TO ‘’;

Open Oracle database instance completely with ALTER DATABASE OPEN command.
If the datafiles that need to be changed or moved do not belong to SYSTEM tablespaces, and do not contain active rollback segments or temporary segments, there is another workaround that does not require database instance to be shutdown. Instead, only the particular tablespace that contains the date files is taken offline.

1. Login to SQLPlus.
2. Connect as SYS DBA with CONNECT / AS SYSDBA command.
3. Make offline the affected tablespace with ALTER TABLESPACE OFFLINE; command.
4. Modify the name or location of datafiles in Oracle data dictionary using following command syntax:
5. ALTER TABLESPACE RENAME DATAFILE ‘’ TO ‘’;

Bring the tablespace online again with ALTER TABLESPACE alter tablespace ONLINE; command.


------------
prompt Nastapi przeniesienie plikow danych Z -> DO
select SUBSTR(FILE_NAME,1,instr(FILE_NAME,'SYSTEM')-1) as "Z",
replace(SUBSTR(FILE_NAME,1,instr(FILE_NAME,'SYSTEM')-1),'C:\','D:\') as "DO"
from DBA_DATA_FILES where TABLESPACE_NAME = 'SYSTEM' ;
SET ECHO OFF; SET NEWPAGE 0;
SET LINESIZE 32767;
SET SPACE 0;
SET PAGESIZE 0;
SET FEEDBACK OFF;
SET HEADING OFF;
SET TRIMSPOOL ON;
SET TERMOUT ON;
prompt
prompt Generuje skrypty pomocnicze
prompt *************************************************************************
prompt . create_new_dest.bat
prompt . move_datafiles.bat
prompt . rename_datafiles.sql
set termout off;
spool create_new_dest.bat;
select '@echo off' from dual;
select 'mkdir "'|| replace(SUBSTR(FILE_NAME,1,instr(FILE_NAME,'SYSTEM')-1),'C:\','D:\') ||'"' from DBA_DATA_FILES where TABLESPACE_NAME = 'SYSTEM';
spool off;
set termout on;
set termout off;
spool move_datafiles.bat;
select '@echo off' from dual;
select 'move /Y "' || FILE_NAME ||'" "'||replace(FILE_NAME,'C:\','D:\')||'"' from DBA_DATA_FILES ;
spool off;
set termout on;
set termout off;
spool rename_datafiles.sql;
select 'set termout off;' FROM DUAL; -- wspolna
select 'ALTER DATABASE RENAME FILE ''' || FILE_NAME ||''' TO '''||replace(FILE_NAME,'C:\','D:\')||''';' from DBA_DATA_FILES ;
select 'set termout on;' FROM DUAL;
spool off;
set termout on;
--prompt *************************************************************************
--prompt Wygnerowane zostały trzy skrypty --prompt 1. create_new_dest.bat
--prompt 2. move_datafiles.bat
--prompt 3. rename_datafiles.sql
--prompt UWAGA: SPRAWDŹ ICH ZAWARTOŚĆ
--prompt *************************************************************************
--prompt Jeśli jest OK wykonaj kolejno:
--prompt 1. shutdown immediate
--prompt 2. host create_new_dest.bat
--prompt 3. host move_datafiles.bat
--prompt 4. startup mount;
--prompt 5. @rename_datafiles.sql
--prompt 6. alter database open;
prompt
prompt
prompt UWAGA: Wykonać wygenerowane skrypty?
prompt *************************************************************************
prompt
prompt Dalej? [Enter], Przerwij [Ctrl+C] ACCEPT dalej
prompt
prompt
prompt Zatrzymuje silnik bazy ORACLE
prompt *************************************************************************
shutdown immediate
prompt
prompt Tworze katalog na dysku D:
prompt *************************************************************************
host create_new_dest.bat
prompt
prompt Przenosze pliki do nowego miejsca
prompt *************************************************************************
host move_datafiles.bat
prompt
prompt Montuje baze ORACLE
prompt *************************************************************************
startup mount;
prompt
prompt Zmieniam konfiguracje bazy ORACLE
prompt *************************************************************************
@rename_datafiles.sql
prompt
prompt Startuje silnik bazy ORACLE
prompt *************************************************************************
alter database open;
prompt
prompt ZROBIONE
prompt *************************************************************************
prompt