Oracle Find Invalid Objects (Packages, Views, Procedures, Functions...)

Working on database refreshes can be very time consuming to detect invalid objects when Oracle Database Links have been changed. 

The one that causes the most frustration is looking for Oracle Views that have an invalid database link, because the database link is stores as a LONG type. The queries below will find all the invalid objects including views and provide a list of objects to recompile.  I have a uses  global recompile script and will post at a later time. This will search objects looking for either invalid objects or specific values (such as database links in views) 
 
--------------------------------------------
--Get Invalid Objects
--------------------------------------------
select distinct o.OWNER, o.OBJECT_TYPE, o.OBJECT_NAME
from  dba_objects o
where o.OWNER in ('XXXX','XXXXX')
and o.OBJECT_TYPE in ('PACKAGE','PACKAGE BODY','TRIGGER','VIEW','PROCEDURE','FUNCTION')
and status != 'VALID'
order by 1;

--------------------------------------------
--Look for any code that has DB Link of name XXXXXX
--------------------------------------------
select *
from DBA_Source s
where lower(s.text) like '%XXXXXX%'

--VIEWS TWO OPTIONS 1: Dump to XLS or 2: Run a Cursor

--------------------------------------------
-- 1. Export to Excel and search the XLS Doc
--------------------------------------------
select v.owner, v.VIEW_NAME, v.text
from DBA_Views v
where OWNER in ('XXXXX','XXXXX')
order by 1,2;

 --------------------------------------------------
 -- 2. Run Cursor to DBMS Dump
 --------------------------------------------------
declare 
     cursor v_cur is select * from all_views where owner != 'SYS';
 begin
   for v_rec in v_cur
    loop
       if (instr(lower(v_rec.text), 'XXXXX') > 0) then
            dbms_output.put_line('Match found in ' || v_rec.owner || '.' || v_rec.view_name);
       end if;
   end loop;
end;

Oracle Create a List of Values in PL/SQL

I had a unique issue trying to determine values in an table, not in another, but in a hard coded list.

Using the Oracle COLUMN_VALUE Pseudocolumn did the trick.

select column_value as SOME_NUMBER
from table(sys.odcinumberlist(135074433,350776765,35047670))

Windows 10 and Windows.OLD Disk Space

Windows 10 left a Windows.OLD folder to revert the machine back. That folder was chewing up 20+ GB of disk.

Microsoft claims you can remove it under the Windows cleanup, but no luck it would not delete the folder. Try deleting it as Admin, nope still cannot.


I came across a post that showed how to delete this stubborn beast via a command prompt. You have run the command prompt as admin. I keep a short-cut on my desktop to cmd.exe and to run it as Admin I right-click > run as admin.

  • Take Ownership: takeown /F C:\Windows.old\* /R /A
  • Modify Access Control: cacls C:\Windows.old\*.* /T /grant administrators:F
  • Delete Folder: rmdir /S /Q C:\Windows.old\