Change default location for Oracle datafiles

User_9GNI8

I wanted to change the default location for Oracle datafiles and spread data across different physical drives using below command. This way u02, u03, u04, u05, ... will automatically point to directory2.

alter system set db_create_file_dest = '/path/to/directory2' scope=both;

Here are the questions.

1. Once u02 is full, is that correct that u03/u04/u05/u06/... will be created automatically in the new directory?
2. What should be done if I want u04 and u05 to point to directory3 and u06/u07/... to point to directory4?

Thanks!

asahide

Hi,

Once u02 is full, is that correct that u03/u04/u05/u06/... will be created automatically in the new directory?

Unfortunately, No.

What should be done if I want u04 and u05 to point to directory3 and u06/u07/... to point to directory4?

You can specify the datafile directly when you create the tablespace.

Regards,