Menu de navegação

[gsmdfParticipante]

Pessoal,

Estou com o seguinte problema para resolver:

Tenho tabelas de um owner X e tabelas de auditoria do owner Y.
1) Preciso de um select que retorne tabelas que o owner X tem que o owner Y não tem. Como ficaria este select?
2) Um select que retorne coluna em tabela owner X sem correspondente na tabela do owner Y.

O padrão do sistema é para toda tabela criada para X criar uma tabela com mesmo nome para Y com campo v_nomeCampoOriginal contendo valor antigo do campo. Que tem uma trigger que fica alimentando as tabelas de auditoria do owner Y a cada insert/update/delete.

Grato!

[hudsonaParticipante]

Você só quer saber o nome das tabelas e das colunas ?
certo?
Ou você precisa do conteudo também ? (os dados)?
?

Se você não for precisar dos dados, só do nome das tabelas e colunas:

Use as seguintes visões do dicionario de dados:
DBA_TABLES
DBA_TAB_COLUMNS

[vieriParticipante]

select table_name from dba_tables where owner = ‘OWNER’ and table_name not in ( select table_name from dba_tables where owner = ‘OWNER_AUDITORIA’ )

[burgaParticipante]

o 1 o vieri ja deixou aí, segue o 2.

select table_name, column_name
from dba_tab_columns
where owner = 'X'
minus
select column table_name, column_name
from dba_tab_columns
where owner = 'Y'

[gsmdfParticipante]

[quote=”burga”:1ovcwel7]o 1 o vieri ja deixou aí, segue o 2.

select table_name, column_name
from dba_tab_columns
where owner = 'X'
minus
select table_name, column_name
from dba_tab_columns
where owner = 'Y'[/quote]

Eu entendi a lógica, mas esse select no meu banco vai retornar todas as tabelas e colunas das respectivas pois nas tabelas de auditoria o nome do campo é alterado seguindo o padrão seguinte:

Tabela original:
-COD (PK)
-DESC

Tabela auditoria:

• N_COD
  -V_COD
  -V_DESC
  E outras colunas de controle (operação efetuada,data e hora da atualização,etc)

Como seria no oracle essa forma de “concatenar” esse padrão, para compararmos nomeCampo com v_nomeCampo e também n_nomeCampo no caso de pks.

obs: v_ possui o valor antigo e n_ o novo valor.

[gsmdfParticipante]

E um select de colunas com nome “v_%” em tabela do owner AUDIT que estão com constraint NOT NULL?

Muito obrigado pessoal!

[sanclerParticipante]

Usando a função substr(colunaTabelaAuditoria, 3) não daria certo não?

[burgaParticipante]

[quote=”gsmdf”:tpxa9keu]E um select de colunas com nome “v_%” em tabela do owner AUDIT que estão com constraint NOT NULL?

Muito obrigado pessoal![/quote]

Na própria tabela dba_tab_columns existe uma coluna chamada NULLABLE que indica se a coluna é NOT NULL (N) ou não (Y).

Sabendo isso é fácil montar o select que você quer…

select *
from dba_tab_columns
where owner = 'AUDIT'
and nullable = 'N'
and column_name like 'V%';

[gsmdfParticipante]

[quote=”burga”:3jpo99mv][quote=”gsmdf”:3jpo99mv]E um select de colunas com nome “v_%” em tabela do owner AUDIT que estão com constraint NOT NULL?

Muito obrigado pessoal![/quote]

Na própria tabela dba_tab_columns existe uma coluna chamada NULLABLE que indica se a coluna é NOT NULL (N) ou não (Y).

Sabendo isso é fácil montar o select que você quer…

select *
from dba_tab_columns
where owner = 'AUDIT'
and nullable = 'N'
and column_name like 'V%';[/quote]

Funcionou perfeitamente. Poxa, muito bom aprender essas coisas do oracle, realmente tornam fáceis construir esses selects.

[gsmdfParticipante]

[quote=”sancler”:157eq9d1]Usando a função substr(colunaTabelaAuditoria, 3) não daria certo não?[/quote]

Ótima solução,sancler.
Comecei a mexer com oracle há um mês, então esse mundo de funções e views dele que auxiliam nesses casos ainda é bem desconhecido para min.

[burgaParticipante]

Ou ainda…

Se quiser verificar se as tabelas de auditoria possuem as duas colunas pro valor da PK, você pode tentar o seguinte:

( select t1.table_name, 'N_' || t1.column_name
from DBA_CONS_COLUMNS t1 join DBA_CONSTRAINTS t2
on t1.constraint_name = t2.constraint_name
and t1.owner = t2.owner
and t1.table_name = t2.table_name
and t1.owner = 'X'
and t2.constraint_type = 'P'
union all
select table_name, 'V_' || column_name
from DBA_TAB_COLUMNS
where owner = 'X')
minus
select table_name, column_name
from dba_tab_columns
where owner = 'Y';

[gsmdfParticipante]

[quote=”burga”:2hyxj325]Ou ainda…

Se quiser verificar se as tabelas de auditoria possuem as duas colunas pro valor da PK, você pode tentar o seguinte:

( select t1.table_name, 'N_' || t1.column_name
from DBA_CONS_COLUMNS t1 join DBA_CONSTRAINTS t2
on t1.constraint_name = t2.constraint_name
and t1.owner = t2.owner
and t1.table_name = t2.table_name
and t1.owner = 'X'
and t2.constraint_type = 'P'
union all
select table_name, 'V_' || column_name
from DBA_TAB_COLUMNS
where owner = 'X')
minus
select table_name, column_name
from dba_tab_columns
where owner = 'Y';[/quote]

Onde X = owner_auditoria e Y = owner_normal.
Funcionou, mas para o resultado ficar ideal teria de tratar o seguinte problema:

Há o limite de 30 caracteres para os nomes de colunas.
Logo se o nome da coluna no owner original tem 30 caracteres, por exemplo, ao se criar a coluna da auditoria trunca-se os 2 últimos caracteres. Se a coluna original tiver 29 caracteres trunca-se o ultimo caracter.

ex: COD_ABCDEFGHIJKLMNOPQRSTUVXZAB no owner auditoria vai se tornar V_COD_ABCDEFGHIJKLMNOPQRSTUVXZ

Tem como tratar isso neste select? Pois no caso deste exemplo ele retornaria V_COD_ABCDEFGHIJKLMNOPQRSTUVXZAB e N_COD_ABCDEFGHIJKLMNOPQRSTUVXZAB (no caso de ser PK) e o ideal seria ele não detectar que falta essa coluna.

[gsmdfParticipante]

Duas últimas dúvidas, agora em relação a um select de triggers:

1) Selecionar Triggers de auditoria sem alguma opção marcada: before insert/delete/update.

2)Selecionar Triggers de auditoria desativadas ou inválidas;

[fsitjaParticipante]

A mesma função de antes, se usa o substr para limitar o comprimento da string:

substr('V_COD_ABCDEFGHIJKLMNOPQRSTUVXZAB', 1, 30)

Sendo o 30 o comprimento máximo que você quer cortar e 1 a posição inicial do substring.

[sanclerParticipante]

Só complementando a resposta do fsitja.

Caso você use a função substr(coluna, posiçãoCaracterInicio, posiçãoCaracterFim)
nesse seu select para realmente trazer os dados que resolvem seu prolema o seguinte teria que ser feito:

Na tabela não auditada.
usar a função substr na tabela com os paramêtros 1e 28
substr(colunaTabelaNaoAuditada, 1, 28).

Na tabela auditoria.
usar a função substr na tabela com os paramêtros 3 e 30
substr(colunaTabelaNaoAuditada, 3, 30).

agora cabe a você gsmdf ponderar se esses caracteres podem ou não ser ignorados na solução do seu problema.

Qualquer dúvida continue postando.

[Autor]

Posts