Manage directory objects

OceanBase Database in Oracle mode allows you to map a folder on a shared file system, such as Network File System (NFS), to a database directory object. Directory objects are mainly used in the UTL_FILE system package.

Create a directory object

You can execute the CREATE DIRECTORY statement to create a directory object in OceanBase Database. A created directory object specifies an alias for a folder on the shared file system.

Syntax for creating a directory object:

CREATE [OR REPLACE] DIRECTORY <directory object name> AS <file system path>;

In the syntax:

• OR REPLACE specifies that if the specified directory object name already exists, a new file system path replaces the original directory definition.

Example of creating a directory object named sql_file_dir:

obclient> CREATE OR REPLACE DIRECTORY sql_file_dir AS '/usr/sqldump';

Drop a directory object

You can execute the DROP DIRECTORY statement to drop a directory object from OceanBase Database.

Syntax for dropping a directory object:

obclient> DROP DIRECTORY <directory object name>;

Example of dropping the directory object named sql_file_dir:

obclient> DROP DIRECTORY sql_file_dir;

Considerations

Take note of the following rules when you create a directory object:

• OceanBase Database does not automatically create folders in the specified file system path, such as /usr/sqldump. You must manually create folders. For example, you can run the mkdir command to create a folder.

• OceanBase Database does not check or determine whether the specified file system path exists or whether read/write permissions on this path are obtained.

• As OceanBase Database is a distributed database system, you must make sure that all OBServer nodes can access the same absolute path. In general, you can specify a path on a shared file system, such as NFS, as the file system path in the statement that is used to create a directory object.

• To create a directory object, you must have the CREATE ANY DIRECTORY system privilege.

Take note of the following rules when you drop a directory object:

• If a directory object is dropped, the folder that the directory object maps to on the shared file system is not dropped.

• To drop a directory object, you must have the DROP ANY DIRECTORY system privilege.

Directory object-related views

After a directory object is created, you can check the status of the directory object based on the following views:

• DBA_DIRECTORIES

  The DBA_DIRECTORIES view displays the definitions of all directory objects under a tenant. Only the sys user and users that have query permissions on this view can access this view.

  Field	Type	Nullable	Description
  OWNER	VARCHAR2(128)	NO	The owner of the directory.
  DIRECTORY_NAME	VARCHAR2(128)	NO	The name of the directory.
  DIRECTORY_PATH	VARCHAR2(4000)	NO	The path of the directory.
  ORIGIN_CON_ID	NUMBER(38)	NO	The ID of the tenant.

• ALL_DIRECTORIES

  The ALL_DIRECTORIES view displays the definitions of the directory objects that are accessible to the current user.

  Field	Type	Nullable	Description
  OWNER	VARCHAR2(128)	NO	The owner of the directory.
  DIRECTORY_NAME	VARCHAR2(128)	NO	The name of the directory.
  DIRECTORY_PATH	VARCHAR2(4000)	NO	The path of the directory.
  ORIGIN_CON_ID	NUMBER(38)	NO	The ID of the tenant.