5 Creating and Managing Directories

Oracle Autonomous Database provides you the ability to create directories in your dedicated database where you can store files, much like you can do in an OS file system. This ability to store files is especially useful when exporting and import data.

Create a Directory

To create directories use the database CREATE DIRECTORY command. Using CREATE DIRECTORY you specify the path as a relative path for the new directory.

CREATE DIRECTORY creates the database directory object and also creates the file system directory if it does not already exist. If the file system directory exists then CREATE DIRECTORY only creates the database directory object. For example, the following command creates the database directory named staging and creates the file system directory stage:

CREATE DIRECTORY staging AS 'stage';

You can also create subdirectories. For example, the following command creates the database directory object sales_staging and the file system directory stage/sales:

CREATE DIRECTORY sales_staging AS 'stage/sales';

When you create subdirectories you do not have to create the initial file system directory. For example, in the previous example if the directory stage does not exist then the CREATE DIRECTORY command creates both directories stage and stage/sales.

To add a directory, you must have the CREATE ANY DIRECTORY system privilege. The ADMIN user is granted the CREATE ANY DIRECTORY system privilege. The ADMIN user can grant CREATE ANY DIRECTORY system privilege to other users.

See CREATE DIRECTORY for more information.

Notes:

  • CREATE DIRECTORY creates the database directory object in the Autonomous Transaction Processing dedicated database and also creates the file system directory.

  • You can create a directory in the root file system to see all the files with the following commands:

    CREATE OR REPLACE DIRECTORY ROOT_DIR AS '';

    After you create the ROOT_DIR directory, use the following command to list all files:

    SELECT * FROM DBMS_CLOUD.list_files('ROOT_DIR');

    To run DBMS_CLOUD.LIST_FILES with a user other than ADMIN you need to grant read privileges on the directory to that user. See LIST_FILES Function for more information.

  • Space used by the directories you create and their contents is part of your dedicated database's data storage allocation.

Drop a Directory

Use the database DROP DIRECTORY command to drop a directory object.

For example, the following command drops the database directory object staging:

DROP DIRECTORY staging;

The DROP DIRECTORY command does not delete files in the directory. If you want to delete the directory and the files in the directory, first use the procedure DBMS_CLOUD.DELETE_FILE to delete the files. See DELETE_FILE Procedure for more information.

To drop a directory, you must have the DROP ANY DIRECTORY system privilege. The ADMIN user is granted the DROP ANY DIRECTORY system privilege. The ADMIN user can grant DROP ANY DIRECTORY system privilege to other users.

See DROP DIRECTORY for more information.

Notes:

  • If you just want to drop the directory and you do not remove the files in the directory, after you drop the directory you can view all the files in the file system, including any files that were in the directory you dropped, as follows:

    CREATE OR REPLACE DIRECTORY ROOT_DIR AS '';

    Then list the contents of ROOT_DIR with the following command:

    SELECT * FROM DBMS_CLOUD.LIST_FILES('ROOT_DIR');

    To run DBMS_CLOUD.LIST_FILES with a user other than ADMIN you need to grant read privileges on the directory to that user. See LIST_FILES Function for more information.

  • The DROP DIRECTORY command does not remove the underlying file system directory. The Autonomous Transaction Processing dedicated database manages the underlying file system directory; users do not remove the file system directory.

List the Contents of a Directory

Use the function DBMS_CLOUD.LIST_FILES to list the contents of a directory.

For example, to list the contents of the stage directory, run the following query:

SELECT * FROM DBMS_CLOUD.LIST_FILES('STAGE');

To run DBMS_CLOUD.LIST_FILES with a user other than ADMIN you need to grant read privileges on the directory to that user. See LIST_FILES Function for more information.

Copy Files Between Cloud Object Storage and a Directory

Use the procedure DBMS_CLOUD.PUT_OBJECT to copy a file from a directory to cloud object storage. Use the procedure DBMS_CLOUD.GET_OBJECT to copy a file from cloud object storage to a directory.

For example, to copy a file from cloud object storage to the stage directory, run the following command:

BEGIN
   DBMS_CLOUD.GET_OBJECT(
   credential_name => 'DEF_CRED_NAME',
   object_uri => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/idthydc0kinr/mybucket/cwallet.sso',
   directory_name => 'STAGE');
END;
/

In this example, object_uri is an Oracle Cloud Infrastructure Swift URI that specifies the cwallet.sso file in the mybucket bucket in the us-phoenix-1 region. (idthydc0kinr is the object storage namespace in which the bucket resides.) For information about the supported URI formats, see Cloud Object Storage URI Formats.

To run DBMS_CLOUD.GET_OBJECT with a user other than ADMIN you need to grant write privileges on the directory to that user.

To run DBMS_CLOUD.PUT_OBJECT with a user other than ADMIN you need to grant read privileges on the directory to that user.

See GET_OBJECT Procedure and PUT_OBJECT Procedure for more information.