Change default location for Oracle datafiles

Question

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';

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!

Answer 1

Directories like /u02, /u03/, etc. are typically mount points for disk partitions. If it "fills up" then creating a new directory won't help: you'll need another disk partition to point /u03 or some other mount point to. These directories, partitions and mount points are not created automatically, but are assigned by your system administrator.

That said, you can always spread your data files across physical disks by specifying the exact path in the CREATE or ALTER TABLESPACE commands. The db_create_file_dest parameter only specifies the default location in which files are created if you don't specify one explicitly, and it can only point to one place.

Note that there is no point to spreading files across directories only if those directories are all located on the same physical disk partition.