How to transfer bulk datafiles online to Oracle database
Certifications:
- How to create users in databases that belong to a SQL Server Always On Availability Group - 10 February 2025
- How to create a Logon Trigger to control which users are allowed to connect to an Oracle Database - 13 January 2025
- How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
In this article we will see a script with which we can mass transfer datafiles online that we may have created them in the wrong location or we want to change their storage in an Oracle database.
The code
By running the following code the alter commands will be created which if we execute them will carry out the transfer to the new location online.
We only need to define the destination folder and the source folder where the datafiles are located, as well as we can choose to transfer only those datafiles that belong to a specific tablespace only:
--need to change the to destination and filters
select
('alter database move datafile '''||file_name ||''' to ''/oracle/oradata/'|| substr(file_name,instr(file_name, '/', -1)) ||''';') move_command --need to change the destination folder
from dba_data_files
where 1=1
and lpad(file_name, instr(file_name, '/', -1)) = '/oracle/logs/' --old source datafile location
--and tablespace_name = 'USERS' --filter with tablespace name
;
When running the script, alter commands like the following will be created that will contain the previous location and the new one. To carry out the transfer, we should execute these created alter statements:
alter database move datafile '/oracle/logs/users_19.DBF' to '/oracle/oradata/users_19.DBF';
alter database move datafile '/oracle/logs/users_20.DBF' to '/oracle/oradata/users_20.DBF';
alter database move datafile '/oracle/logs/users_21.DBF' to '/oracle/oradata/users_21.DBF';