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

No comments: