Oracle的PDB数据库创建DIRECTORY时遇到ORA-65254 寻技术

在Oracle 19c多租户环境的PDB数据库下面创建一个DIRECTORY时，遇到了“ORA-65254: invalid path specified for the directory”，下面简单演示一下所遇到的这个案例

SQL> CREATE PLUGGABLE DATABASE PDB6 ADMIN USER pdbadmin IDENTIFIED BY hqLDJ7236#4U8JHD
  2  STORAGE (MAXSIZE 1G MAX_SHARED_TEMP_SIZE 2G)
  3  DEFAULT TABLESPACE TEST DATAFILE '/db19c/oracle_data/PDB6/test_data_01.dbf' SIZE 100M
  4  PATH_PREFIX ='/db19c/oracle_data/PDB6/'
  5  FILE_NAME_CONVERT=('/db19c/CTEST/pdbseed/','/db19c/oracle_data/PDB6/');

Pluggable database created.

SQL> 
SQL> alter pluggable database pdb6 open;

Pluggable database altered.

SQL> alter session set container=PDB6;

Session altered.

SQL> CREATE OR REPLACE DIRECTORY EXP_DUMP AS '/db19c/exp_dump';
CREATE OR REPLACE DIRECTORY EXP_DUMP AS '/db19c/exp_dump'
*
ERROR at line 1:
ORA-65254: invalid path specified for the directory

##注意，创建DIRECTORY时，这个目录/db19c/exp_dump已经存在。

SQL> 

SQL> !oerr ora 65254
65254, 00000, "invalid path specified for the directory"
// *Cause:   An absolute path was used in the CREATE DIRECTORY statement. 
//           Since the PDB has set the PATH_PREFIX property, only relative
//           path is allowed for directories.
// *Action:  Specify a relative path and reissue the statement.
//

SQL>

如上所示，数据库提示，出现这个错误的原因在于创建PDB的语句中指定了PATH_PREFIX参数，创建目录DIRECTORY时只能使用相对路径，而不能使用绝对路径。其实创建PDB时，如果设置了PATH_PREFIX参数，那么这个PDB中的所有对象只能限定在PATH_PREFIX指定的路径下，,只能指定基于PATH_PREFIX的相对路径。官方文档关于这个的解释如下所示：

Restrictions on PDB File Locations

The PATH_PREFIX clause of the CREATE PLUGGABLE DATABASE statement ensures that all directory object paths associated with the PDB are restricted to the specified directory or its subdirectories. This clause also ensures that the following files associated with the PDB are restricted to the specified directory: the Oracle XML repository for the PDB, files created with a CREATE PFILE statement, and the export directory for Oracle wallets. Use this clause when you want to ensure that a PDB's files reside in a specific directory and its subdirectories. You can use this clause to specify one of the following options:

An absolute path that is used as a prefix for all file paths associated with the PDB.

The name of a directory object that exists in the CDB root (CDB$ROOT). The directory object points to the absolute path to be used for PATH_PREFIX.

NONE to indicate that there are no restrictions for the file paths. Omitting the PATH_PREFIX clause is the same as specifying NONE.

After a PDB is created, its PATH_PREFIX setting cannot be modified.

也就是说，如果我要创建一个目录，只能使用相对路径的目录，而且必须在PATH_PREFIX指定的/db19c/oracle_data/PDB6/下面，想指定到其他路径，例如/db19c/exp_dump.此时就会抛出ORA-65254错误。

解决方法

SQL> CREATE OR REPLACE DIRECTORY EXP_DUMP AS 'exp_dump';

Directory created.

SQL> SET LINESIZE 1080;
SQL> COL OWNER FOR A16;
SQL> COL DIRECTORY_NAME FOR A30;
SQL> COL DIRECTORY_PATH FOR A64;
SQL> SELECT * FROM DBA_DIRECTORIES;

OWNER      DIRECTORY_NAME       DIRECTORY_PATH                 ORIGIN_CON_ID
------- ------------------ ----------------------------------- -------------
SYS              EXP_DUMP   /db19c/oracle_data/PDB6/exp_dump