Verificar os campos chave nas tabelas do Banco.

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Avatar do usuário
wbarrence
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 55
Registrado em: Qui, 06 Out 2011 10:30 am
Localização: SP
--
WABS

Olá Pessoal,

Hoje estou com uma tarefa bem chatinha de se resolver, se alguém tiver alguma boa idéia para me ajudar eu agradeço de coração.

Nesses últimos dias ocorreu um problema na Produção, em que uma FK que faz referência a uma PK não tinham o mesmo tamanho, e conforme o valor da PK foi aumentando, chegou a um ponto em que a FK não conseguia mais comportar o valor da PK, e só descobrimos essa falha, quando ela apareceu na Produção.

Gostaria de saber se existe alguma maneira de verificar no banco inteiro (em todas as tabelas), se as PK's são do mesmo tamanho que as FK's que fazem referência a ela?

Att,
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Olá wbarrance,

Eu fiz uma querie que talvez atenda ao que você precisa. Entretanto, algumas considerações:

- Todas as suas PKs e FKs precisam estar no MESMO schema;
- Você deve executar a querie com o usuário do SCHEMA (dono das PK/FK);
- Todas as suas PKs/Fks são compostas de uma única coluna. Não existe PK ou FK composta (duas colunas ou mais);

Uma vez exposta estas considerações, segue a querie:

Selecionar tudo

 SELECT FK.TABELA_FILHA AS TABELA_FILHA, 
        FK.FOREIGN_KEY AS FK_CONSTRAINT,
        FK.COLUNA_FILHA,
        FK.DATA_TYPE,
        FK.DATA_LENGTH,
        FK.DATA_PRECISION,
        PK.TABELA_PAI,
        PK.PRIMARY_KEY AS PK_CONSTRAINT,
        PK.COLUNA_PAI,
        PK.DATA_TYPE,
        PK.DATA_LENGTH,
        PK.DATA_PRECISION
 FROM 
     ( 
     SELECT DC.TABLE_NAME AS TABELA_PAI , DCC.COLUMN_NAME AS COLUNA_PAI, DC.CONSTRAINT_NAME AS PRIMARY_KEY,
            DTC.DATA_TYPE, DTC.DATA_LENGTH, DTC.DATA_PRECISION
       FROM USER_CONSTRAINTS DC, USER_CONS_COLUMNS DCC, USER_TAB_COLUMNS DTC
      WHERE DC.CONSTRAINT_NAME = DCC.CONSTRAINT_NAME AND DC.CONSTRAINT_TYPE = 'P' AND
            DCC.COLUMN_NAME = DTC.COLUMN_NAME AND DCC.TABLE_NAME = DTC.TABLE_NAME) PK, 
     ( 
     SELECT DC.TABLE_NAME AS TABELA_FILHA, DCC.COLUMN_NAME AS COLUNA_FILHA, DC.R_CONSTRAINT_NAME AS PRIMARY_KEY, DCC.CONSTRAINT_NAME AS FOREIGN_KEY,
            DTC.DATA_TYPE, DTC.DATA_LENGTH, DTC.DATA_PRECISION
       FROM USER_CONSTRAINTS DC, USER_CONS_COLUMNS DCC, USER_TAB_COLUMNS DTC
      WHERE DC.CONSTRAINT_NAME = DCC.CONSTRAINT_NAME AND DC.CONSTRAINT_TYPE = 'R' AND
            DCC.COLUMN_NAME = DTC.COLUMN_NAME AND DCC.TABLE_NAME = DTC.TABLE_NAME  ) FK
 WHERE  PK.PRIMARY_KEY = FK.PRIMARY_KEY
--   AND (FK.DATA_TYPE <> PK.DATA_TYPE OR FK.DATA_LENGTH <> FK.DATA_LENGTH OR FK.DATA_PRECISION <> PK.DATA_PRECISION)
 ORDER BY PK.TABELA_PAI,
          FK.TABELA_FILHA
Não posso lhe garantir que a querie está 100% certa. No meu schema ela funcionou corretamente. Sugiro que faça testes em seu ambiente, antes de considerar ela correta. Agradeço eventuais comentários de outros foristas.

Note que existe uma linha comentada na querie ('-- AND'). Se reativar o filtro comentando (removendo o '--'), ela só vai mostrar as situações onde a FK não bate com a PK (devido a "tipo", "tamanho" ou "precisão" diferentes).

Talvez o pessoal do forum possa indicar outras queries (mais simples) ou maneiras mais rápidas de se detectar estes problemas.

Abraços,

Sergio Coutinho
Avatar do usuário
wbarrence
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 55
Registrado em: Qui, 06 Out 2011 10:30 am
Localização: SP
--
WABS

Olá Sérgio,

Ainda estou fazendo alguns testes aqui, mas a query você montou está perfeita!!!

Está me trazendo todas as PK's e FK's do Banco, e a lógica que você usou para montar a query é bem simples, como sou iniciante nessa área, estou aproveitando este momento para estudar ela.

Desde já agradeço pela sua ajuda.

Att,
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Olá wbarrence,

Legal que ela está sendo útil para você. Mas lembre-se das restrições que te passei no comentário anterior, ok?

No meu ambiente, os analistas estabeleceram que não haveria chaves compostas de FK ou PK (chaves com duas ou mais colunas), então isso tornou a querie BEM mais simples.

Talvez seja possível adaptar esta querie em ambientes com chaves compostas, mas você precisaria "quebrar um pouco" a cabeça para ter certeza que as associações estão corretas.

Talvez esteja falando algo que você já saiba, mas conhecer o dicionario de dados (especialmente as DBA_<tables>) vai te facilitar muito a vida como DBA, pois muitas vezes não vamos ter a oportunidade de usar ferramentas gráficas nos ambientes.

Abraços e tudo de bom !

Sergio Coutinho
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 15 visitantes