Showing posts with label Get file list in SQLPlus. Show all posts
Showing posts with label Get file list in SQLPlus. Show all posts

Thursday, September 18, 2008

Get the list of all the files by using DBMS_BACKUP_RESTORE

DECLARE
pattern VARCHAR2(1024) := '/u01/oracle/admin/SID/udump';
ns VARCHAR2(1024);
BEGIN
SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);
FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT WHERE FNAME_KRBMSFT LIKE '%.trc')
LOOP
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;
END;
/


Note: it only available in 10g
Reference: http://www.chrispoole.co.uk/tips/plsqltip2.htm