How to move online tablespace in Oracle 19c

A few days ago, when the team performed some application deployment tasks, we noticed that the different schemas created in the datafiles, which belong to a TBS, were created at the destination of the CDB or PDBs.

We start with the first task of verifying that the db_create_file_dest parameter was configured.

This parameter which differs in container paths from PDB is the one that guarantees that, through the OMF utility, the datafiles are created in the correct paths.

A demo was made, to understand the problem with developers, DevOps and dbas.
So that the importance of setting these values and the impact of not having their configuration is understood.

Likewise, we proceeded to move the data files, from the wrong paths to the correct paths where the data files should be created.

Hands-on

Check and configure the db_create_file_dest.

Check the default configuration

show parameter db_create_file_dest
SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string
SQL>

It’s empty.

Set the configuration for Container Database (CDB)

ALTER SYSTEM SET db_create_file_dest='/opt/oracle/oradata/ORCLCDB';
Seguir leyendo «How to move online tablespace in Oracle 19c»