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.
- Drop a Directory
Use the databaseDROP DIRECTORY
command to drop a directory object. - List the Contents of a Directory
Use the functionDBMS_CLOUD.LIST_FILES
to list the contents of a directory. - Copy Files Between Cloud Object Storage and a Directory
Use the procedureDBMS_CLOUD.PUT_OBJECT
to copy a file from a directory to cloud object storage. Use the procedureDBMS_CLOUD.GET_OBJECT
to copy a file from cloud object storage to a directory.
Parent topic: Development Workflow
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.
-
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.
Parent topic: Manage Directories
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.
Parent topic: Manage Directories
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.
Parent topic: Manage Directories