How to transfer bulk datafiles online to Oracle database
Certifications:
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
- How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 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';