How to list files on a directory from Oracle Database. 13 September 2011
Posted by David Alejo Marcos in Exadata, Oracle 11.2, RAC, SQL - PL/SQL.Tags: Exadata, Oracle 11.2, RAC, SQL, SQL - PL/SQL
trackback
Couple of days ago I had an interesting request, “How can I see the contents of nfs_dir”?
The problem:
We were using DBFS to store our exports. This was the perfect solution as the business could “see” the files on the destination folder, but it did not meet our requirements performance wise on our Exadata.
We have decided to mount NFS and performance did improve, but we had a different problem. NFS is mounted on the database server and business do not have access for security reasons and segregation of duties.
Since then, the export jobs run, but business could not “see” what files were created, so the question was asked.
The solution:
After some research I came across with the following package:
SYS.DBMS_BACKUP_RESTORE.searchFiles
I did have to play a little bit, and I finished with the following script:
1.- Create an Oracle type
create type file_array as table of varchar2(100) /
2.- Create the function as SYS:
CREATE OR REPLACE FUNCTION LIST_FILES (lp_string IN VARCHAR2 default null) RETURN file_array pipelined AS lv_pattern VARCHAR2(1024); lv_ns VARCHAR2(1024); BEGIN SELECT directory_path INTO lv_pattern FROM dba_directories WHERE directory_name = 'NFS_DIR'; SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns); FOR file_list IN (SELECT FNAME_KRBMSFT AS file_name FROM X$KRBMSFT WHERE FNAME_KRBMSFT LIKE '%'|| NVL(lp_string, FNAME_KRBMSFT)||'%' ) LOOP PIPE ROW(file_list.file_name); END LOOP; END; /
3.- Grant necessary permissions:
grant execute on LIST_FILES to public; create public synonym list_files for sys.LIST_FILES;
4.- Test without WHERE clause:
TESTDB> select * from table(list_files); COLUMN_VALUE ---------------------------------------------------------------------------------------------------- /nfs/oracle/TESTDB/pump_dir/expdp_TESTDB.log /nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece1.dmp /nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece2.dmp /nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece3.dmp /nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece4.dmp /nfs/oracle/TESTDB/pump_dir/imdp_piece_TESTDB_09092011.log /nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece1.dmp_old /nfs/oracle/TESTDB/pump_dir/imdp_piece_TESTDB_12092011.log 8 rows selected. Elapsed: 00:00:00.10
5.- Test with WHERE clause:
TESTDB> select * from table(list_files) where column_value like '%dmp%'; COLUMN_VALUE ---------------------------------------------------------------------------------------------------- /nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece1.dmp /nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece2.dmp /nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece3.dmp /nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece4.dmp /nfs/oracle/TESTDB/pump_dir/expdp_TESTDB_piece1.dmp_old Elapsed: 00:00:00.12
As always, comments are welcome.
David Alejo-Marcos.
David Marcos Consulting Ltd.
Comments
Sorry comments are closed for this entry
Hello David,
There are extra “space/nbsp” HTML characters in the source of LIST_FILES function. It’s better if you remove them.
Why do you use this query:
select * from table(list_files) where column_value like ‘%dmp%’;
while you can use the following one?
select * from table( list_files(‘dmp’));
Regards
Gokhan
Hello Gokhan,
first of all, thank you for your post. I have since amended the code.
No reason for select * from table(list_files) where column_value like ‘%dmp%’;
Select .. from .. where .. is, most of the times, more intuitive for SQL developers and DBAs.
Regards,
David Alejo-Marcos.
Interesting post. So this will list the files that exist in the directory.. but how to get the Unix file permissions on them, from the DB? I don’t think it is possible..
Hi Paul,
thank you for your comment. To move files accross from Oracle DB to Oracle DB I use dbms_file_transfer (using get_file, so I execute the following script on Destination DB):
dbms_file_transfer.get_file (‘NFS_DIR’,
‘expdp.dmp’,
,
‘NFS_DIR’,
‘expdp.dmp’);
I am in the process to create a script to copy file from Oracle DB to a different server. I will post the script once I have finished.
Thank you,
David Alejo-Marcos.
Great Post – Thanks a lot!
Heiko
Thank you. I will use this code. All work will be done through oracle. List files, rename…