Useful shortcuts for vi editor

Wednesday, 19 October 2016

How to drop invalid objects from database?

Specify invalid objects
SQL>
  SELECT *
    FROM dba_objects
   WHERE status = 'INVALID'
ORDER BY owner;

Drop invalid objects
SQL> 
  SELECT 'DROP ' || object_type || ' ' || owner || '.' || object_name || ';'
    FROM dba_objects
   WHERE status = 'INVALID'
ORDER BY owner;