How to delete multiple files together from Oracle Autonomous Database Directory Object such as DATA_PUMP_DIR
BaskarThis article guides you with the commands for deleting multiple files together from your Oracle Autonomous Database Directory Object such as DATA_PUMP_DIR.
Remember that we don't have OS access for our Autonomous Serverless Database. So, to access or remove the files from Autonomous Database DATA_PUMP_DIR directory, use the package called "DBMS_CLOUD".
Let's first see the list the files stored in your Autonomous Database DATA_PUMP_DIR directory.
set lines 250 pages 5000 col object_name format a60 col created format a40 col last_modified format a40 SELECT object_name, bytes, created, last_modified FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR'); -- Output: OBJECT_NAME BYTES CREATED LAST_MODIFIED ------------------------------------------------------------ ---------- ---------------------------------------- ---------------------------------------- myschema1_ddl.sql 1126 06-JUL-24 10.36.30.629114 AM +00:00 06-JUL-24 10.36.30.645957 AM +00:00 myschemaddl.sql 1126 06-JUL-24 10.45.03.007798 AM +00:00 06-JUL-24 10.45.03.013881 AM +00:00 Oracle_Database_23ai_Concepts.pdf 10302049 27-JUN-24 04.52.18.691878 PM +00:00 27-JUN-24 04.52.20.759139 PM +00:00 Oracle_AI_Vector_Search_User_Guide.pdf 2956343 27-JUN-24 04.52.22.090975 PM +00:00 27-JUN-24 04.52.23.424262 PM +00:00 Oracle_Autonomous_Database.pdf 25701941 27-JUN-24 04.52.24.310775 PM +00:00 27-JUN-24 04.52.27.583341 PM +00:00 Installing_Oracle_Analytics_Server.pdf 638750 27-JUN-24 04.52.20.262216 PM +00:00 27-JUN-24 04.52.21.200813 PM +00:00 Sample-Sales-Data.pdf 122279 27-JUN-24 04.52.21.777760 PM +00:00 27-JUN-24 04.52.22.148246 PM +00:00 7 rows selected.Let's say I want to delete only one file called "Sample-Sales-Data.pdf". So, to delete this file from DATA_PUMP_DIR directory, you should use a procedure called "DBMS_CLOUD.DELETE_FILE"
BEGIN DBMS_CLOUD.DELETE_FILE( directory_name => 'DATA_PUMP_DIR', file_name => 'Sample-Sales-Data.pdf'); END; /Let's again verify the list of files from DATA_PUMP_DIR directory. You may observe, from the output, that the file "Sample-Sales-Data.pdf" is deleted.
set lines 250 pages 5000 col object_name format a60 col created format a40 col last_modified format a40 SELECT object_name, bytes, created, last_modified FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR');SQL> SQL> SQL> SQL> OBJECT_NAME BYTES CREATED LAST_MODIFIED ------------------------------------------------------------ ---------- ---------------------------------------- ---------------------------------------- myschema1_ddl.sql 1126 06-JUL-24 10.36.30.629114 AM +00:00 06-JUL-24 10.36.30.645957 AM +00:00 myschemaddl.sql 1126 06-JUL-24 10.45.03.007798 AM +00:00 06-JUL-24 10.45.03.013881 AM +00:00 Oracle_Database_23ai_Concepts.pdf 10302049 27-JUN-24 04.52.18.691878 PM +00:00 27-JUN-24 04.52.20.759139 PM +00:00 Oracle_AI_Vector_Search_User_Guide.pdf 2956343 27-JUN-24 04.52.22.090975 PM +00:00 27-JUN-24 04.52.23.424262 PM +00:00 Oracle_Autonomous_Database.pdf 25701941 27-JUN-24 04.52.24.310775 PM +00:00 27-JUN-24 04.52.27.583341 PM +00:00 Installing_Oracle_Analytics_Server.pdf 638750 27-JUN-24 04.52.20.262216 PM +00:00 27-JUN-24 04.52.21.200813 PM +00:00 6 rows selected.Let's now see how to remove one or more files together. Let's say we want to remove all the ".sql" files together. So, to do that, you can use the following PL/SQL code. In this code, we are creating a cursor and filtering only the .sql files from "DBMS_CLOUD.LIST_FILES" procedure. And then, for each '.sql' file that is obtained, it will start deleting it using "DBMS_CLOUD.DELETE_FILE" procedure.
SET SERVEROUTPUT ON DECLARE CURSOR flist IS SELECT object_name FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR') where object_name like '%sql'; fname VARCHAR2(4000); BEGIN DBMS_OUTPUT.PUT_LINE(CHR(10)); OPEN flist; LOOP FETCH flist INTO fname; EXIT WHEN flist%NOTFOUND; DBMS_CLOUD.DELETE_FILE(directory_name => 'DATA_PUMP_DIR', file_name => fname); DBMS_OUTPUT.PUT_LINE('Deleted file: ' || fname); END LOOP; DBMS_OUTPUT.PUT_LINE(CHR(10)); CLOSE flist; END; / -- Output: Deleted file: myschemaddl.sql Deleted file: myschema1_ddl.sql PL/SQL procedure successfully completed.Let's again check the list of files from DATA_PUMP_DIR directory. As you may observe, all the ".sql" files have been removed now.
set lines 250 pages 5000 col object_name format a60 col created format a40 col last_modified format a40 SELECT object_name, bytes, created, last_modified FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR'); -- Output: OBJECT_NAME BYTES CREATED LAST_MODIFIED ------------------------------------------------------------ ---------- ---------------------------------------- ---------------------------------------- Oracle_Database_23ai_Concepts.pdf 10302049 27-JUN-24 04.52.18.691878 PM +00:00 27-JUN-24 04.52.20.759139 PM +00:00 Oracle_AI_Vector_Search_User_Guide.pdf 2956343 27-JUN-24 04.52.22.090975 PM +00:00 27-JUN-24 04.52.23.424262 PM +00:00 Oracle_Autonomous_Database.pdf 25701941 27-JUN-24 04.52.24.310775 PM +00:00 27-JUN-24 04.52.27.583341 PM +00:00 Installing_Oracle_Analytics_Server.pdf 638750 27-JUN-24 04.52.20.262216 PM +00:00 27-JUN-24 04.52.21.200813 PM +00:00 4 rows selected.Thank you for reading this article. Hope it helped you to progress your work.
Subscribe to my newsletter
Read articles from Baskar directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by