Manage Directories

Autonomous Database on Dedicated Exadata Infrastructure provides you the ability to create directories in your 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.

Related Topics

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 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.