8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
List Files in a Directory From PL/SQL and SQL : DBMS_BACKUP_RESTORE
This article shows how to list files in a directory on the database server using the DBMS_BACKUP_RESTORE package.
Related articles.
Setup
We connect to a privileged user, and create a new test user.
CONN sys/SysPassword1@//localhost:1521/pdb1 AS SYSDBA --DROP USER testuser1 CASCADE; CREATE USER testuser1 IDENTIFIED BY testuser1 QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE VIEW TO testuser1;
Create Pipelined Table Function
Whilst still connected to the privileged user, we create a table type and a pipelined table function using the DBMS_BACKUP_RESTORE
package. The call to the SEARCHFILES
procedure sets the starting point for the search. We query the file names from the X$KRBMSFT
table. By default this does a recursive search, so we have to limit the output to files in the requested directory. Finally, we pipe out the file names, removing the full path leading to it.
We can't grant privileges on the X$
tables, so this pipelined table function must be owned by the SYS user.
CREATE OR REPLACE TYPE sys.ob_varchar2_arr AS TABLE OF VARCHAR2(500); / CREATE OR REPLACE FUNCTION sys.ob_get_files (p_pattern IN VARCHAR2, p_file_separator IN VARCHAR2 := '/') RETURN sys.ob_varchar2_arr PIPELINED AS l_pattern VARCHAR2(32767); l_ns VARCHAR2(32767); BEGIN -- Make sure the pattern ends with the file separator. l_pattern := RTRIM(p_pattern, p_file_separator) || p_file_separator; sys.DBMS_BACKUP_RESTORE.searchfiles( pattern => l_pattern, ns => l_ns, onlyfnm => TRUE, normfnm => TRUE); -- Pull back all files directly under specified directory. -- WHERE filter removes recursion. FOR cur_rec IN (SELECT fname_krbmsft FROM sys.x$krbmsft WHERE INSTR(SUBSTR(fname_krbmsft, LENGTH(l_pattern)+1), p_file_separator) = 0) LOOP -- Display the file name without the preceding path. PIPE ROW(SUBSTR(cur_rec.fname_krbmsft, LENGTH(l_pattern)+1)); END LOOP; RETURN; END; /
We make sure our test user have access to the pipelined table function and the table type.
GRANT EXECUTE ON sys.ob_varchar2_arr TO testuser1; GRANT EXECUTE ON sys.ob_get_files TO testuser1;
Test It
We connect to the test user and run a query, passing the directory of interest. It doesn't matter if we use final "/" or not, as the code always adds one.
CONN testuser1/testuser1@//localhost:1521/pdb1 COLUMN file_name FORMAT A30 SELECT column_value AS file_name FROM TABLE(sys.ob_get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace')); FILE_NAME ------------------------------ cdb1_ora_15109.trc cdb1_ora_15109.trm alert_cdb1.log ... removed for brevity ... cdb1_ora_20934.trm cdb1_m004_20954.trc cdb1_m004_20954.trm 481 rows selected. SQL>
We can limit the output using a WHERE
clause in the normal way.
SELECT column_value AS file_name FROM TABLE(sys.ob_get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace')) WHERE column_value LIKE 'alert%'; FILE_NAME ------------------------------ alert_cdb1.log SQL>
These queries can be used in PL/SQL.
Using Views
You can hide the internal workings by creating views over the pipelined table function. The following views display all the files in the trace directory, and just the alert log respectively.
CREATE OR REPLACE VIEW trace_files_v AS SELECT column_value AS file_name FROM TABLE(sys.ob_get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace')); CREATE OR REPLACE VIEW alert_log_v AS SELECT column_value AS file_name FROM TABLE(sys.ob_get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace')) WHERE column_value LIKE 'alert%';
Pros and Cons
Pros of this method are.
- Doesn't give additional access to the files. It just lists them.
- Can list files from any directory on the DB server that is accessible to the "oracle" OS user.
Cons of this method are.
- We have to create objects in the SYS schema. This is not a great idea.
- The
DBMS_BACKUP_RESTORE
package is not documented, so it is not officially supported for our use. It's an internal package used by the database. - We don't get any additional details about the files. Just the file name.
- We have no control over the directories that are searched unless we code in some additional checks.
For more information see:
Hope this helps. Regards Tim...