Andrew Fraser DBA

Application Express APEX datafile location for new schemas

How to change apex datafile location for new schemas?

Just change the datafile location for the flow tablespace – because the new ones all create in that same directory.

For example, in this database that is /ora1data/TEST/dbs/ :

SQL> select file_name from dba_data_files where tablespace_name like ‘FLOW%’;

FILE_NAME
——————————————————————————–
/ora1data/TEST/dbs/FLOW_1.dbf

You can change the datafile location with the alter database command, e.g.:

SQL> alter tablespace FLOW_1 offline ;
SQL> host mv /ora1data/TEST/dbs/FLOW_1.dbf /apexdbfiles/FLOW_1.dbf
SQL> alter database datafile ‘/ora1data/TEST/dbs/FLOW_1.dbf’ rename ‘/apexdbfiles/FLOW_1.dbf’ ;
SQL> alter tablespace FLOW_1 online ;

Alternatively, for oracle managed files, set parameter db_create_file_dest.

Easier still – reuse schemas and even workspaces in APEX, don’t create new ones continually.

Reference:

By default, workspace data files created in same directory as tablespace for APEX (FLOW_xxx.DBF)
Oracle Managed Files
Gives DBA complete control over file location
DB_CREATE_FILE_DEST init parameter
o1_mf_%t_%u_.dbf
/u03/oradata/marvlprd/MARVLPRD/datafile/o1_mf_flow_25_339bpw79_.dbf