# utlfixdirs.sql

It's a script that comes with every `$ORACLE_HOME` and it's located
in `$ORACLE_HOME/rdbms/admin/` folder. It recreates all `DBA_DIRECTORIES`
that are created by Oracle software and should be referencing the current `$ORACLE_HOME`.

We should use it every time we change `$ORACLE_HOME`. That means after migrations and after out-of-place upgrades, which is generally the preferred method of upgrading. You can read more on [downsides of in-place patching](https://mikedietrichde.com/2024/01/10/the-downsides-of-in-place-patching-and-a-patching-lab/) on Mike Dietrich's blog.

This script is available in versions >= 19c.

## OPatch

Interestingly, OPatch directories are fixed automatically on instance startup without running any scripts or commands other than `startup`.
This applies only to `OPATCH_%_DIR` directories. So, it should be safe to run OPatch even before you run `utlfixdirs.sql`.

Regardless, I still believe it to be a good practice to fix directories before datapatch because .sql scripts that are run by datapatch could, in theory, need something
from other, non-opatch directories (e.g. maybe from `SDO_DIR_ADMIN` directory).

## READ ONLY PDBs

All Oracle maintained directories that point to paths in `$ORACLE_HOME` are created/replaced by Oracle using `sharing=metadata`. So, after running `utlfixdirs.sql` you will have those directories fixed even in PDBs that are open as READ ONLY. That includes `PDB$SEED`.

## Example:

```
SQL> @?/rdbms/admin/utlfixdirs.sql

Container: CDB$ROOT

Current  ORACLE_HOME: /oracle/db_ee/19.24.0/dbhome_2
Original ORACLE_HOME: /oracle/db_ee/19.24.0/dbhome_1

DBMS_OPTIM_ADMINDIR
...OLD: /oracle/db_ee/19.24.0/dbhome_1/rdbms/admin
...NEW: /oracle/db_ee/19.24.0/dbhome_2/rdbms/admin
DBMS_OPTIM_LOGDIR
...OLD: /oracle/db_ee/19.24.0/dbhome_1/cfgtoollogs
...NEW: /oracle/db_ee/19.24.0/dbhome_2/cfgtoollogs
ORACLE_HOME
...OLD: /oracle/db_ee/19.24.0/dbhome_1
...NEW: /oracle/db_ee/19.24.0/dbhome_2
ORACLE_OCM_CONFIG_DIR
...OLD: /oracle/db_ee/19.24.0/dbhome_1/ccr/state
...NEW: /oracle/db_ee/19.24.0/dbhome_2/ccr/state
ORACLE_OCM_CONFIG_DIR2
...OLD: /oracle/db_ee/19.24.0/dbhome_1/ccr/state
...NEW: /oracle/db_ee/19.24.0/dbhome_2/ccr/state
SDO_DIR_ADMIN
...OLD: /oracle/db_ee/19.24.0/dbhome_1/md/admin
...NEW: /oracle/db_ee/19.24.0/dbhome_2/md/admin
XMLDIR
...OLD: /oracle/db_ee/19.24.0/dbhome_1/rdbms/xml
...NEW: /oracle/db_ee/19.24.0/dbhome_2/rdbms/xml
XSDDIR
...OLD: /oracle/db_ee/19.24.0/dbhome_1/rdbms/xml/schema
...NEW: /oracle/db_ee/19.24.0/dbhome_2/rdbms/xml/schema

PL/SQL procedure successfully completed.
```

## References
* Official doc: [Creating Additional Data Dictionary Structures](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/creating-additional-data-dictionary-structures.html#GUID-571DA1D1-5274-4763-99B4-B1FF60E79F9F)

The contents of this blog post were tested on database version 19.24. Feel free to leave a comment if you find that described behavior changes with newer versions.