Manage directory objects

The Oracle mode of OceanBase Database 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.

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.

• You have the CREATE ANY DIRECTORY privilege.

• When you create a directory object, you must use the global variable secure_file_priv to specify the system path to the files to be accessed.

  The global variable secure_file_priv specifies the path that can be accessed during data import or export. For more information about secure_file_priv, see secure_file_priv.

  You can set the variable by using the following method:

  1. Log on as the administrator to an Oracle tenant in the cluster by using a local Unix socket on the OBServer node.

     Here is an example:

     obclient -S /home/admin/oceanbase/run/sql.sock -uroot@sys -p********
     

     For more information about how to connect to OceanBase Database by using a local Unix socket, see secure_file_priv.

  2. Set the accessible file system path to /usr/sqldump.

     SET GLOBAL secure_file_priv = "/usr/sqldump";
     

  After the statement is executed, you must restart the session for the modification to take effect.

Syntax and examples

The syntax for creating a directory object is as follows:

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

In the syntax, OR REPLACE indicates that if the corresponding directory object already exists, the original directory object definition is replaced with the new file system path.

Example: Create a directory object named sql_file_dir:

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. To drop a directory object, ensure that you have the DROP ANY DIRECTORY privilege.

Considerations

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.

• Do not drop a directory object when PL is accessing files in the shared file system.

Syntax and examples

The syntax for dropping a directory object is as follows:

DROP DIRECTORY <Directory object name>;

Example: Drop the directory object named sql_file_dir:

DROP DIRECTORY sql_file_dir;

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	Description
  OWNER	VARCHAR2(128)	The owner of the directory.
  DIRECTORY_NAME	VARCHAR2(128)	The name of the directory.
  DIRECTORY_PATH	VARCHAR2(4000)	The path of the directory.
  ORIGIN_CON_ID	NUMBER(38)	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	Description
  OWNER	VARCHAR2(128)	The owner of the directory.
  DIRECTORY_NAME	VARCHAR2(128)	The name of the directory.
  DIRECTORY_PATH	VARCHAR2(4000)	The path of the directory.
  ORIGIN_CON_ID	NUMBER(38)	The ID of the tenant.