How to transfer bulk datafiles online to Oracle database
Certifications:
- How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
- How do we find what permissions a user has on an Oracle database - 1 November 2024
- How we enable Unified Auditing in Oracle Database - 7 October 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';