Tenho a seguinte tabela que quero redefinir:
create table tq84_redefinition (
id number primary key,
ts1 timestamp not null,
ts2 timestamp
);
Observe a not null
restrição na coluna ts1
.
Usando dbms_redefinition
, eu uso especificamente copy_constraints => true
.
create table tq84_redefinition_int (
id number, -- Note: no primary key to prevent «ORA-01408: such column list already indexed»
ts1 date,
ts2 date,
duration_minutes as ((ts2 - ts1) * 24 * 60)
);
begin
dbms_redefinition.start_redef_table(
user, 'tq84_redefinition', 'tq84_redefinition_int',
'id, ' ||
'to_date(to_char(ts1, ''ddmmyyyyhh24miss''), ''ddmmyyyyhh24miss'') ts1, ' ||
'to_date(to_char(ts2, ''ddmmyyyyhh24miss''), ''ddmmyyyyhh24miss'') ts2');
end;
/
-- set serveroutput on
declare
cnt_errors binary_integer;
begin
dbms_redefinition.copy_table_dependents(
user, 'tq84_redefinition', 'tq84_redefinition_int',
-------------------------------------------------------
copy_indexes => dbms_redefinition.cons_orig_params,
copy_triggers => true,
copy_constraints => true,
copy_privileges => true,
ignore_errors => false,
num_errors => cnt_errors,
copy_statistics => true,
copy_mvlog => false);
if cnt_errors > 0 then
dbms_output.put_line('There were ' || cnt_errors || ' errors.');
end if;
end;
/
exec dbms_redefinition.sync_interim_table(user, 'tq84_redefinition', 'tq84_redefinition_int');
exec dbms_redefinition.finish_redef_table(user, 'tq84_redefinition', 'tq84_redefinition_int');
Tudo parece funcionar bem, exceto que desc
no SQL*Plus não exibe corretamente a not null
restrição:
...> desc tq84_redefinition;
Name Null? Type
--------------------------- -------- ---------------
ID NUMBER
TS1 DATE
TS2 DATE
DURATION_MINUTES NUMBER
No entanto, em algum lugar, as restrições não nulas estão em vigor. Eu posso ver isso emitindo um
select constraint_type, constraint_name, search_condition
from user_constraints
where table_name = 'TQ84_REDEFINITION';
Além disso, se eu tentar inserir um registro [ insert into tq84_redefinition (id) values (999999)
], recebo a mensagem de erro (correta) ORA-01400: cannot insert NULL into ("META"."TQ84_REDEFINITION"."TS1")
.
Edit : A versão (v$version) é:
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Edite 2 @Munchi, o resultado de sua declaração select sugerida
select
column_name as "Name",
nullable as "Null?",
concat(concat(concat(data_type,'('),data_length),')') as "Type"
from
user_tab_columns
where
table_name = 'TQ84_REDEFINITION'
é
Name N Type
------------------------------ - --------------
ID Y NUMBER(22)
TS1 Y DATE(7)
TS2 Y DATE(7)
DURATION_MINUTES Y NUMBER(22)
Este é um bug conhecido (a descrição não é pública, infelizmente):
Bug:4396234 ET10.2OREDEF: COL NULLABLE OF *_TAB_COLUMNS TABELA NÃO ATUALIZADA APÓS REDEF ONLINE
As restrições NOT NULL são copiadas como NOVALIDATE e você deve configurá-las para o estado VALIDATE manualmente, por exemplo:
O problema da chave primária é semelhante, mas não há nenhum bug relatado para isso. No entanto, descobri que desativá-lo e reativá-lo resolve o problema.
A
Not Null
cláusula de uma definição de coluna de tabela define um sinalizador$sys$cols
e gera umasys_xxxxxx
restrição na coluna.dbms_redef
no entanto, apenas copia asys_xxxxxxx
restrição e omite a configuração do sinalizador. É esse sinalizador que é decodificado no "Nullable?" exibir (digamos) na guia de colunas SQLDeveloper de uma tabela.Então, qual é o efeito disso -
Bem, a integridade referencial é mantida - tentar inserir um nulo em uma coluna onde a restrição existe ainda falha.
No entanto, li em algum lugar (terei que encontrar esse artigo novamente) que o CBO examina o
$sys$cols
sinalizador (não a restrição) como parte da otimização para determinadas consultas.Considere um índice baseado em uma coluna não nula. Este índice pode ser um candidato para satisfazer uma
select count(*)
consulta de tipo em vez de fazer um FTS. Se o$sys$cols
próprio for nulo (não definido por redef), esse índice não será considerado.Acho que vale a pena o esforço de adicionar etapas ao seu código de redefinição
Depois de redefinir - salve os nomes das colunas não nulas das restrições
- elimine as restrições não nulas - reaplicar por meio da
alter table modify col not null
cláusulaMesmo apenas para acalmar os desenvolvedores Java que percebem a diferença antes e depois.