sql muy utiles en migracion de bases

--cuantos indices invalidos
select owner,count(1)
from dba_indexes
where status = 'INVALID'
group by owner;
--cuales son los indices invalidos
select owner,
       index_name,
       index_type,
       table_owner,
       table_name,
       table_type,
       tablespace_name
from dba_indexes
where status = 'INVALID';

-- cuantos son los objetos invalidos
select owner,object_type,count(1)
from dba_objects
where status = 'INVALID'
group by owner,object_type
order by 1,2;

-- cuales son los objetos invalidos
select owner,
       object_name,
       created,
       last_ddl_time
from dba_objects
where status = 'INVALID'
order by 1,2;

-- Paquetes con bodies sin que no tengan sus correspondientes headers
select unique owner,name
from dba_source a
where type = 'PACKAGE BODY'
  and not exists (select null
                  from dba_source b
                  where a.owner = b.owner
                    and a.name = b.name
                    and b.type = 'PACKAGE') ;

-- Constraints deshabilitadas
select owner,
       case constraint_type
          when 'P' then 'PRIMARY_KEY'
          when 'R' then 'FOREIGN_KEY'
          when 'U' then 'UNIQUE'
          when 'C' then 'CHECK'
       end constraint_type, 
       count(1)
from dba_constraints
where status = 'DISABLED'
group by owner,constraint_type
order by 1,2;

select owner,
       constraint_name,
       constraint_type,
       table_name
from dba_constraints
where status = 'DISABLED'
order by 1,2;

--Triggers deshabilitados
select owner,
       trigger_name,
       trigger_type,
       triggering_event,
       table_owner,
       table_name
from dba_triggers
where status = 'DISABLED'
order by 1,2;

-- para contar registros de todas las tablas
SELECT 'SELECT '
       || q'{'}'
       ||OWNER
       || '.'
       || OBJECT_NAME
       || q'{'}'
       || ' as Origen, COUNT(*) as registros'
       || ' FROM '
       ||OWNER ||'.'
       || OBJECT_NAME
       || ' UNION'  --,
      -- OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_TYPE='TABLE'
   and owner not in  ('SYS','SYSTEM','DBSNMP');