No Oracle (e provavelmente em outros lugares), a execução de uma instrução ALTER TABLE fará um commit implÃcito na transação atual.
Temos uma ferramenta (escrita em Java) que deve modificar um esquema por:
- adicionando algumas colunas
- removendo algumas colunas
- atualizando uma tabela de descrição com o novo layout de esquema
A ferramenta conforme escrita exibirá o layout do esquema atual para o usuário e permitirá que ele o modifique. (Basicamente, adicionando ou removendo colunas de "atributos" personalizadas para algumas tabelas) Quando estiver satisfeito, ele poderá aplicar suas alterações.
Observe: O layout básico do esquema e o fato de que você precisa ALTER TABLE para alterar algumas coisas é predefinido por um padrão e não pode ser alterado por nós, pois outras ferramentas não funcionariam mais.
O problema agora é que não podemos executar essas alterações em uma única transação, pois, AFAIK, não é possÃvel fazer várias instruções ALTER TABLE em uma transação.
Que opções temos para "reverter" ao estado inicial se algo der errado ao aplicar as alterações?
Nota: Alguém aqui propôs PONTO DE RESTAURAÇÃO + FLASHBACK - é uma boa ideia chamar isso a partir desta ferramenta (Java)? (Não controlamos totalmente a instância do banco de dados em alguns sites onde a ferramenta deve ser usada.)
Nota: Oracle 10g2 e superior
Como o DDL implicitamente confirma, a única maneira de "reverter" suas alterações é construir a operação reversa e aplicá-la para reverter a alteração, como afirma a_horse_with_no_name.
No entanto, construir tal reversão nem sempre será simples. Se os dados puderem ser gravados na tabela entre as modificações de tipo (varchar2(10) -> varchar2(50), número -> varchar2) e reverter isso, você também terá que verificar se os novos dados serão válidos ao reverter para o tipo original (ou realizar alguma conversão). Esteja ciente de que descartar colunas em tabelas grandes pode levar algum tempo e gerar grandes quantidades de refazer.
Você também deve ter cuidado para não invalidar quaisquer procedimentos armazenados em seu banco de dados e outras dependências de aplicativos como resultado dessas alterações.
A opção de flashback não irá ajudá-lo neste caso. Depois de fazer alterações DDL em uma tabela, você não pode restaurá-la ao estado anterior usando flashback. Tentar fazer isso dará o erro:
ORA-01466: não foi possÃvel ler os dados - a definição da tabela foi alterada
Fazer flashback de seu banco de dados completo seria um exagero e também não seria possÃvel a partir do aplicativo Java - você precisa desligar e montar o banco de dados para concluir esta operação.
O que levanta a questão de para que serve sua ferramenta. Se você só precisa de uma GUI para as pessoas editarem tabelas, algo como o Oracle SQL Data Modeler pode fazer isso e gerar scripts DDL para você. Estes podem então ser validados, testados, uma reversão apropriada construÃda e aplicada ao banco de dados. A modificação da estrutura de um banco de dados (de produção) deve ser feita com cuidado e testada para garantir que todas as alterações sejam válidas!
A única maneira que consigo pensar (além de migrar para um DBMS que suporte DDL transacional) é escrever sua própria manipulação de "transação DDL", onde você cria a instrução correspondente que reverte a alteração real que você fez.
"Reverter" uma ADD COLUMN é bastante fácil, pois você só precisa descartar a coluna. Para reverter um DROP COLUMN, a única opção que vejo é renomear a coluna primeiro e depois, quando tudo foi bem-sucedido, descartar todas as colunas renomeadas. Para reverter a "queda virtual", você só precisa renomear a coluna de volta ao seu nome original.
Outra alternativa poderia ser criar uma cópia das tabelas antes de modificá-las, por exemplo, usando
CREATE TABLE backup_table AS SELECT * FROM original_table(mas aparentemente esta não é uma boa solução se as tabelas forem muito grandes)O uso do Flashback provavelmente não é muito confiável, pois você não pode confiar na disponibilidade dos dados do flashback. O valor padrão para um tempo de flashback garantido é de 15 minutos. Mas o DBA é livre para escolher um valor menor.
A Oracle adicionou um recurso Redefinição baseada em edição para atualizar o esquema do aplicativo principalmente online no 11gR2. Você provavelmente poderia usar isso para atingir seu objetivo.
faça todas as alterações em um conjunto temporário de tabelas.
quando tudo for concluÃdo com sucesso, aplique todas as alterações nas tabelas reais.
A instrução Oracle DDL é confirmada implicitamente. É o único DBMS com o qual trabalho.
A " Tabela FlashBack" do Oracle pode retroceder uma tabela para um ponto anterior no tempo... mas não funciona em uma alteração estrutural (ou seja, DDL) nessa tabela.
"Flashback Database " usa um mecanismo diferente, mas levará todo o banco de dados de volta para antes da mudança, o que você pode não ter permissão para fazer.
Outra opção que ninguém mencionou é o monólito que é DBMS_REDEFINITION .
É um pacote enorme, fornecido pela Oracle, que deve gerenciar tudo isso para você, tornando suas alterações de tabela transparentes, contÃnuas e evitando [quase] qualquer interrupção do serviço. É grande e complicado, mas pode valer a pena o investimento para se familiarizar com isso.
Acho melhor você adicionar uma estrutura onde seus clientes pensam que estão adicionando colunas, mas na verdade eles estão apenas adicionando registros em uma tabela, por exemplo: "Table_Column_DEF". Então você só precisa de uma tabela de ligação entre sua tabela padrão e "Table_Column_DEF", onde você também armazena os valores.
Com esse método, seus clientes podem adicionar qualquer coluna que desejarem e podem fazer isso em 1 transação. Você tem benefÃcios de reversão, flashback, etc... Você tem, claro, algumas compensações a considerar:
1) Coluna de Valor:
2) Desempenho
3) Consulta: Você terá que construir isso dinamicamente. Percorrendo todos os registros "Table_Column_DEF" e adicionando-os com um "select (selecione Value_Int de LinkTable onde ...) Value_Int, ... de ..."