Tuesday, June 30, 2009

PL/SQL script for finding invalid oracle directories

-- Script for Finding Invalid Directories.
-- Create a test file and check for the status

declare
f utl_file.file_type;
BEGIN

FOR X IN ( SELECT * FROM DBA_DIRECTORIES WHERE OWNER= 'SYS' ) LOOP
BEGIN
f := utl_file.fopen(x.directory_name,'madhu_test.txt','W');
IF utl_file.is_open(f) THEN
UTL_FILE.fclose(f);
UTL_FILE.fremove(x.directory_name,'madhu_test.txt');
end if;
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(x.directory_name || ' -- ' || x.directory_path || ' is invalid');
END;
END LOOP;

END;
/

No comments: