SET SQLN OFF
SET VERIFY       off
set term ON

DEFINE InputES     = ' '

COLUMN eess NOPRINT NEW_VALUE InputES

SELECT nvl(trim(UPPER('&&esquema')), '%') eess from dual;


SELECT decode('&InputES','107','%','&InputES') eess from dual;

SPOOL &&ruta_fichero

set term OFF


SET LINESIZE     3500
REM SET PAGESIZE     0
REM SET TRIMSPOOL    ON
SET SERVEROUTPUT ON SIZE 500000 FORMAT WORD_WRAPPED
REM SET FEEDBACK     OFF

alter session set nls_numeric_characters = ',.';

@@header_dbinfo 107 DC




PROMPT
PROMPT
PROMPT Lista de tablas con columnas lob almacenadas en el mismo tablespace que su tabla propietaria
PROMPT --------------------------------------------------------------------------------------------

CLEAR COLUMNS

COLUMN OWNER              FORMAT A15         HEADING "OWNER"
COLUMN TABLE_NAME         FORMAT A35         HEADING "TABLE_NAME"
COLUMN COLUMN_NAME        FORMAT A35         HEADING "COLUMN_NAME"
COLUMN TABLESPACE_COLUMNA FORMAT A30         HEADING "TABLESPACE_COLUMNA"
COLUMN TABLESPACE_TABLA   FORMAT A30         HEADING "TABLESPACE_TABLA"

--TABLAS CON COLUMNAS LOB ALMACENADAS EN EL MISMO TABLESPACE QUE SU TABLA PROPIETARIA
SELECT DL.OWNER OWNER, DL.TABLE_NAME TABLE_NAME, DL.COLUMN_NAME COLUMN_NAME, DL.TABLESPACE_NAME TABLESPACE_COLUMNA, DT.TABLESPACE_NAME TABLESPACE_TABLA
FROM DBA_LOBS DL, DBA_TABLES DT
WHERE DL.OWNER NOT IN
('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
AND DL.TABLE_NAME NOT LIKE '%PLAN_TABLE%'
AND DL.OWNER LIKE '&InputES'
AND DL.OWNER = DT.OWNER
AND DL.TABLE_NAME = DT.TABLE_NAME
AND DL.TABLESPACE_NAME = DT.TABLESPACE_NAME
ORDER BY DL.OWNER, DL.TABLE_NAME;



PROMPT
PROMPT
PROMPT Lista de tablas con columnas LONG (ilegales)
PROMPT --------------------------------------------

CLEAR COLUMNS

COLUMN OWNER            FORMAT A15         HEADING "OWNER"
COLUMN TABLE_NAME       FORMAT A30         HEADING "TABLE_NAME"
COLUMN COLUMN_NAME      FORMAT A30         HEADING "COLUMN_NAME"
COLUMN DATA_TYPE        FORMAT A10         HEADING "DATA_TYPE"

--TABLAS CON COLUMNAS LONG ILEGALES
SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM DBA_TAB_COLUMNS
WHERE DATA_TYPE = 'LONG'
AND OWNER NOT IN
('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
AND TABLE_NAME NOT LIKE '%PLAN_TABLE%'
AND OWNER LIKE '&InputES'
ORDER BY OWNER, TABLE_NAME, COLUMN_NAME;

PROMPT
PROMPT
PROMPT Lista de tablas sin PK
PROMPT -----------------------

CLEAR COLUMNS

COLUMN OWNER           FORMAT A15         HEADING "OWNER"
COLUMN TABLE_NAME      FORMAT A30         HEADING "TABLE_NAME"
COLUMN TABLESPACE_NAME FORMAT A20         HEADING "TABLESPACE_NAME"
COLUMN NUM_ROWS        FORMAT 99G999G999  HEADING "NUM_ROWS"
COLUMN LAST_ANALYZED   FORMAT A20         HEADING "LAST_ANALYZED"

--TABLAS SIN PK 
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, NUM_ROWS, TO_CHAR(LAST_ANALYZED, 'DD/MM/YYYY HH24:MI:SS') AS LAST_ANALYZED
FROM DBA_TABLES DT
WHERE OWNER NOT IN
('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
--AND NOT EXISTS(
--SELECT 1 FROM DBA_CONSTRAINTS DC
--WHERE DC.OWNER = DT.OWNER
--AND DC.TABLE_NAME = DT.TABLE_NAME
--AND CONSTRAINT_TYPE = 'P'
--)
AND NOT EXISTS(
SELECT 1 FROM DBA_INDEXES DI
WHERE DI.TABLE_OWNER = DT.OWNER
AND DI.TABLE_NAME = DT.TABLE_NAME
AND UNIQUENESS = 'UNIQUE'
)
AND OWNER LIKE '&InputES'
ORDER BY DT.OWNER, DT.TABLE_NAME;

PROMPT
PROMPT
PROMPT Lista de tablas con mas de 7 indices
PROMPT -------------------------------------

--TABLAS CON MAS DE 7 INDICES 
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, NUM_ROWS, TO_CHAR(LAST_ANALYZED, 'DD/MM/YYYY HH24:MI:SS') AS LAST_ANALYZED
FROM DBA_TABLES DT
WHERE OWNER NOT IN
('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
AND OWNER LIKE '&InputES'
AND EXISTS(
SELECT 1 FROM DBA_INDEXES DI
WHERE OWNER NOT IN
('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
AND DI.TABLE_OWNER = DT.OWNER 
AND DI.TABLE_NAME = DT.TABLE_NAME
GROUP BY DI.TABLE_OWNER, DI.TABLE_NAME
HAVING COUNT(*) >= 7)
ORDER BY OWNER, TABLE_NAME

PROMPT
PROMPT
PROMPT Lista de restricciones deshabilitadas
PROMPT --------------------------------------

CLEAR COLUMNS

COLUMN FCN FORMAT A30         HEADING "FK"
COLUMN FO  FORMAT A10         HEADING "OWNER"
COLUMN FTN FORMAT A30         HEADING "TABLE_NAME"
COLUMN FNR FORMAT 99G999G999  HEADING "NUM_ROWS"
COLUMN FLA FORMAT A20         HEADING "LAST_ANALYZED"

COLUMN RCN FORMAT A30         HEADING "FK"
COLUMN RO  FORMAT A10         HEADING "OWNER"
COLUMN RTN FORMAT A30         HEADING "TABLE_NAME"
COLUMN RNR FORMAT 99G999G999  HEADING "NUM_ROWS"
COLUMN RLA FORMAT A20         HEADING "LAST_ANALYZED"

COLUMN STATUS     FORMAT A20         HEADING "STATUS"
COLUMN DEFERRABLE FORMAT A20         HEADING "DEFERRABLE"
COLUMN DEFERRED   FORMAT A20         HEADING "DEFERRED"
COLUMN VALIDATED  FORMAT A20         HEADING "VALIDATED"

--RESTRCCIONES FK DESHABILITADAS 
SELECT DC1.CONSTRAINT_NAME FCN, DC1.OWNER FO, DC1.TABLE_NAME FTN, DT1.NUM_ROWS FNR, DT1.TO_CHAR(LAST_ANALYZED, 'DD/MM/YYYY HH24:MI:SS') FLA, 
       DC1.R_CONSTRAINT_NAME RCN, DC1.R_OWNER RO, DC2.TABLE_NAME RTN, DT2.NUM_ROWS RNR, DT2.TO_CHAR(LAST_ANALYZED, 'DD/MM/YYYY HH24:MI:SS') RLA, 
       DC1.STATUS, DC1.DEFERRABLE, DC1.DEFERRED, DC1.VALIDATED 
FROM DBA_CONSTRAINTS DC1, 
     (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
        FROM DBA_CONSTRAINTS
       WHERE OWNER NOT IN
             ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
              'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
              'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
              'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
         AND CONSTRAINT_TYPE = 'P') DC2,
     (SELECT OWNER, TABLE_NAME, NUM_ROWS, LAST_ANALYZED
        FROM DBA_TABLES
       WHERE OWNER NOT IN
             ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
              'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
              'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
              'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')) DT1,
     (SELECT OWNER, TABLE_NAME, NUM_ROWS, LAST_ANALYZED
        FROM DBA_TABLES
       WHERE OWNER NOT IN
             ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
              'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
              'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
              'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')) DT2      
WHERE DC1.OWNER NOT IN
('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
AND DC1.OWNER LIKE '&InputES'
AND NOT(DC1.STATUS = 'ENABLED')
AND DC1.CONSTRAINT_TYPE = 'R'
AND DC1.R_OWNER = DC2.OWNER
AND DC1.R_CONSTRAINT_NAME = DC2.CONSTRAINT_NAME
AND DC1.OWNER = DT1.OWNER
AND DC1.TABLE_NAME = DT1.TABLE_NAME
AND DC2.OWNER = DT2.OWNER
AND DC2.TABLE_NAME = DT2.TABLE_NAME
ORDER BY DC1.OWNER, DC1.TABLE_NAME

PROMPT
PROMPT
PROMPT Lista de restricciones PK y Check deshabilitadas
PROMPT -------------------------------------------------

CLEAR COLUMNS

COLUMN RCN FORMAT A30         HEADING "FK"
COLUMN RCT FORMAT A5          HEADING "CTYPE"
COLUMN RO  FORMAT A10         HEADING "OWNER"
COLUMN RTN FORMAT A30         HEADING "TABLE_NAME"
COLUMN RNR FORMAT 99G999G999  HEADING "NUM_ROWS"
COLUMN RLA FORMAT A20         HEADING "LAST_ANALYZED"

COLUMN STATUS     FORMAT A20         HEADING "STATUS"
COLUMN DEFERRABLE FORMAT A20         HEADING "DEFERRABLE"
COLUMN DEFERRED   FORMAT A20         HEADING "DEFERRED"
COLUMN VALIDATED  FORMAT A20         HEADING "VALIDATED"

--RESTRCCIONES PK,CHECK DESHABILITADAS 
SELECT DC1.CONSTRAINT_NAME RCN, DC1.CONSTRAINT_TYPE RCT, DC1.OWNER RO, DC1.TABLE_NAME RTN, DC1.STATUS, DC1.DEFERRABLE, DC1.DEFERRED, DC1.VALIDATED 
FROM DBA_CONSTRAINTS DC1
WHERE DC1.OWNER NOT IN
('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
AND DC1.OWNER LIKE '&InputES'
AND NOT(DC1.STATUS = 'ENABLED')
AND DC1.CONSTRAINT_TYPE <> 'R'
ORDER BY DC1.OWNER, DC1.TABLE_NAME

PROMPT
PROMPT
PROMPT Lista de objetos invalidos
PROMPT ---------------------------

CLEAR COLUMNS

COLUMN OWNER         FORMAT A10         HEADING "OWNER"
COLUMN OBJECT_NAME   FORMAT A30         HEADING "OBJECT_NAME"
COLUMN OBJECT_TYPE   FORMAT A20         HEADING "OBJECT_TYPE"
COLUMN TEMPORARY     FORMAT A20         HEADING "TEMPORARY"
COLUMN GENERATED     FORMAT A20         HEADING "GENERATED"
COLUMN CREATED       FORMAT A20         HEADING "CREATED"
COLUMN LAST_DDL_TIME FORMAT A20         HEADING "LAST_DDL_TIME"

--OBJETOS INVALIDOS  
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, TEMPORARY, GENERATED, CREATED, LAST_DDL_TIME
FROM DBA_OBJECTS
WHERE OWNER NOT IN
('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB','ASTDBA10G')
AND OWNER LIKE '&InputES'
AND STATUS <> 'VALID'
ORDER BY OWNER, OBJECT_NAME, OBJECT_TYPE

PROMPT
PROMPT
PROMPT Lista de restricciones FK sin indice
PROMPT -------------------------------------
 
PROMPT

--FK SIN INDICE
DECLARE
TCONSTRAINT_NAME DBA_CONSTRAINTS.CONSTRAINT_NAME%TYPE; 
TOWNER DBA_CONSTRAINTS.OWNER%TYPE;
TTABLE_NAME DBA_CONSTRAINTS.TABLE_NAME%TYPE;
TNUM_ROWS DBA_TABLES.NUM_ROWS%TYPE;
TLAST_ANALYZED DBA_TABLES.LAST_ANALYZED%TYPE;
TR_CONSTRAINT_NAME DBA_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
TR_OWNER DBA_CONSTRAINTS.OWNER%TYPE;
TR_TABLE_NAME DBA_TABLES.TABLE_NAME%TYPE;
TR_NUM_ROWS DBA_TABLES.NUM_ROWS%TYPE;
TR_LAST_ANALYZED DBA_TABLES.LAST_ANALYZED%TYPE;
TSTATUS DBA_CONSTRAINTS.STATUS%TYPE;
TDEFERRABLE DBA_CONSTRAINTS.DEFERRABLE%TYPE;
TDEFERRED DBA_CONSTRAINTS.DEFERRED%TYPE;
TVALIDATED DBA_CONSTRAINTS.VALIDATED%TYPE;

TCOLUMN_NAME DBA_CONS_COLUMNS.COLUMN_NAME%TYPE;
TPOSITION DBA_CONS_COLUMNS.POSITION%TYPE;

TYPE C IS REF CURSOR;
CC C;
CCOL C;

VCOUNT NUMBER;

TYPE TINDEX_OWNER IS TABLE OF DBA_IND_COLUMNS.INDEX_OWNER%TYPE; 
TYPE TINDEX_NAME IS TABLE OF DBA_IND_COLUMNS.INDEX_NAME%TYPE;

R1INDEX_OWNER TINDEX_OWNER;
R1INDEX_NAME TINDEX_NAME;

R2INDEX_OWNER TINDEX_OWNER;
R2INDEX_NAME TINDEX_NAME;

VNUMERADOR NUMBER;

BEGIN
   DBMS_OUTPUT.PUT_LINE(' ');
   DBMS_OUTPUT.PUT_LINE(RPAD('RESTRICCION',33,' ')||RPAD('PROPIETARIO', 20, ' ')||RPAD('TABLA', 33, ' ')||RPAD('NUM.FILAS', 15, ' ')||RPAD('ANALIZADO', 23, ' ')||RPAD('% DE INDICE CREADO', 19, ' '));
   DBMS_OUTPUT.PUT_LINE(RPAD('-',32,'-')||' '||RPAD('-', 19, '-')||' '||RPAD('-', 32, '-')||' '||RPAD('-', 14, '-')||' '||RPAD('-', 22, '-')||' '||RPAD('-', 18, '-'));

   --RESTRCCIONES FK DESHABILITADAS
   OPEN CC FOR 
   SELECT DC1.CONSTRAINT_NAME, DC1.OWNER, DC1.TABLE_NAME, DT1.NUM_ROWS, DT1.LAST_ANALYZED, DC1.R_CONSTRAINT_NAME, DC1.R_OWNER, DC2.TABLE_NAME, DT2.NUM_ROWS, DT2.LAST_ANALYZED, DC1.STATUS, DC1.DEFERRABLE, DC1.DEFERRED, DC1.VALIDATED 
   FROM DBA_CONSTRAINTS DC1, 
   (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
    FROM DBA_CONSTRAINTS
    WHERE OWNER NOT IN
    ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
     'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
     'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
     'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB', 'ASTDBA10G')
    AND CONSTRAINT_TYPE = 'P') DC2,
   (SELECT OWNER, TABLE_NAME, NUM_ROWS, LAST_ANALYZED
    FROM DBA_TABLES
    WHERE OWNER NOT IN
    ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
     'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
     'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
     'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB', 'ASTDBA10G')) DT1,
   (SELECT OWNER, TABLE_NAME, NUM_ROWS, LAST_ANALYZED
    FROM DBA_TABLES
    WHERE OWNER NOT IN
    ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
     'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
     'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
     'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB', 'ASTDBA10G')) DT2 
   WHERE DC1.OWNER NOT IN
   ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 
    'ODM_MTR', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 
    'QS_ES', 'QS_OS', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 
    'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB', 'ASTDBA10G')
   --AND NOT(DC1.STATUS = 'ENABLED')
   AND DC1.OWNER LIKE '&InputES'
   AND DC1.CONSTRAINT_TYPE = 'R'
   AND DC1.R_OWNER = DC2.OWNER
   AND DC1.R_CONSTRAINT_NAME = DC2.CONSTRAINT_NAME
   AND DC1.OWNER = DT1.OWNER
   AND DC1.TABLE_NAME = DT1.TABLE_NAME
   AND DC2.OWNER = DT2.OWNER
   AND DC2.TABLE_NAME = DT2.TABLE_NAME
   AND DC1.OWNER LIKE '%'
   ORDER BY DC1.OWNER, DC1.TABLE_NAME;
   LOOP
      FETCH CC INTO TCONSTRAINT_NAME, TOWNER, TTABLE_NAME, TNUM_ROWS, TLAST_ANALYZED, TR_CONSTRAINT_NAME, TR_OWNER, TR_TABLE_NAME, 
      TR_NUM_ROWS, TR_LAST_ANALYZED, TSTATUS, TDEFERRABLE, TDEFERRED, TVALIDATED;
      EXIT WHEN CC%NOTFOUND;
      
      SELECT COUNT( * ) INTO VCOUNT 
      FROM DBA_CONS_COLUMNS
      WHERE OWNER = TOWNER
      AND CONSTRAINT_NAME = TCONSTRAINT_NAME;
      
      OPEN CCOL FOR
      SELECT COLUMN_NAME, POSITION
      FROM DBA_CONS_COLUMNS
      WHERE OWNER = TOWNER
      AND CONSTRAINT_NAME = TCONSTRAINT_NAME
      ORDER BY POSITION;
      
      LOOP
         FETCH CCOL INTO TCOLUMN_NAME, TPOSITION;
         EXIT WHEN CCOL%NOTFOUND;
         
         IF TPOSITION = 1 THEN
            SELECT INDEX_OWNER, INDEX_NAME BULK COLLECT INTO R1INDEX_OWNER, R1INDEX_NAME 
            FROM DBA_IND_COLUMNS
            WHERE TABLE_OWNER = TOWNER
            AND TABLE_NAME = TTABLE_NAME
            AND COLUMN_NAME = TCOLUMN_NAME
            AND COLUMN_POSITION = TPOSITION;
         ELSE
            SELECT INDEX_OWNER, INDEX_NAME BULK COLLECT INTO R2INDEX_OWNER, R2INDEX_NAME 
            FROM DBA_IND_COLUMNS
            WHERE TABLE_OWNER = TOWNER
            AND TABLE_NAME = TTABLE_NAME
            AND COLUMN_NAME = TCOLUMN_NAME
            AND COLUMN_POSITION = TPOSITION;
            
            FOR I IN 1..R2INDEX_OWNER.COUNT LOOP
               FOR J IN 1..R1INDEX_OWNER.COUNT LOOP
                  IF (R1INDEX_OWNER(J) <> 'NULO') AND NOT(R1INDEX_OWNER(J) = R2INDEX_OWNER(I))AND(R1INDEX_NAME(J) = R2INDEX_NAME(I)) THEN
                     R1INDEX_OWNER(J) := 'NULO';
                     R1INDEX_NAME(J) := TPOSITION;
                  END IF;
               END LOOP;
            END LOOP;
            
         END IF;

      END LOOP;
      
      IF R1INDEX_OWNER.COUNT > 0 THEN
         FOR J IN 1..R1INDEX_OWNER.COUNT LOOP
            IF (R1INDEX_OWNER(J) = 'NULO') THEN
               IF VNUMERADOR < R1INDEX_NAME(J) THEN
                  VNUMERADOR := R1INDEX_NAME(J);
               END IF;
            ELSE
               VNUMERADOR := VCOUNT;
            END IF;
         END LOOP;
      ELSE
         VNUMERADOR := 0;
      END IF;
      
      IF (VNUMERADOR / VCOUNT) * 100 <> 100 THEN
         DBMS_OUTPUT.PUT_LINE(RPAD(TCONSTRAINT_NAME, 50, ' ')||RPAD(TOWNER, 20, ' ')||RPAD(TTABLE_NAME, 33, ' ')||RPAD(TO_CHAR(TNUM_ROWS, '999,999,999'), 15, ' ')||RPAD(TO_CHAR(TLAST_ANALYZED, 'DD/MM/YYYY HH24:MI:SS'), 23, ' ')||TO_CHAR((VNUMERADOR / VCOUNT) * 100, '990.00'));
      END IF;
   END LOOP;
END;
/

PROMPT

SET FEEDBACK  ON
SET PAGESIZE  9999
SET TERM ON

UNDEFINE ruta_fichero
UNDEFINE InputES
UNDEFINE esquema


SPOOL OFF