Olá pessoal, tudo bem?
Várias operações DDL de manutenção de esquema não exigem mais "bloqueios", tornando essas operações não intrusivas e transparentes para uso de maneira on-line.
A remoção de bloqueios internos permite um desenvolvimento de aplicativos mais simples e robusto, especialmente para migrações de aplicativos. Evita assim interrupções de aplicativos para muitas das operações típicas de manutenção de esquemas do banco de dados.
Com a possibilidade ONLINE para manutenção de esquemas utilizando DDLs, não precisamos mais parar aplicações em janelas de manutenção para tais operações, devido a alta concorrência em objetos.
A partir do Oracle Database 12c Release 1 (12.1.0.1), podemos efetuar várias operações DDL de maneira ONLINE, se quiser confira na documentação da Oracle abaixo:
https://docs.oracle.com/database/121/NEWFT/chapter12101.htm#NEWFT280
Vamos montar nosso cenário para realizarmos alguns testes com esta nova funcionalidade, ONLINE, e no final do artigo vamos conhecer uma nova funcionalidade com o TRUNCATE.
Vamos lá.
Vamos criar duas tabelas para nossos testes. Vamos criar uma tabela que armazenará informações de funcionários e uma tabela para armazenar diversos pagamentos destes funcionários.
Não se atente para a lógica do negócio, pois, estamos apenas considerando estas tabelas para nossos testes.
Veja abaixo:
Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 Connected as producao SQL> SQL> create table tb_funcionarios 2 ( id_func number not null, 3 nome_func varchar2(40) not null, 4 datacad date not null, 5 constraint funcionarios_pk primary key (id_func) 6 ); Table created SQL> create table tb_pagto_func 2 (id_func number not null, 3 datapagto date not null, 4 vlrpagto number(10,2) not null, 5 constraint pagto_func_pk primary key (id_func,datapagto) 6 ); Table created SQL> alter table tb_pagto_func add constraint fk_funcionarios foreign key (id_func) references tb_funcionarios (id_func) on delete cascade; Table altered
Certo, temos duas tabelas criadas. A tabela tb_pagto_func é filha da tb_funcionarios, conforme mostra a FK criada acima, com a opção DELETE CASCADE.
Criamos abaixo um índice em nossa tabela de funcionários e outro índice em nossa tabela de pagamentos:
SQL> create index idx_datacad on tb_funcionarios(datacad); Index created SQL> SQL> create index idx_datapagto on tb_pagto_func(datapagto); Index created SQL>
Agora vamos inserir alguns dados. Vamos cadastrar dois funcionários:
SQL> SQL> insert into tb_funcionarios values ( 1, 'JOSE DA SILVA', trunc(sysdate)); 1 row inserted SQL> insert into tb_funcionarios values ( 2, 'ANA MARIA', trunc(sysdate)); 1 row inserted
Vamos inserir também dois pagamentos para o funcionário 1 e um pagamento para o funcionário 2:
SQL> insert into tb_pagto_func values (1, trunc(sysdate), 500 ); 1 row inserted SQL> insert into tb_pagto_func values (1, trunc(sysdate)+1, 700 ); 1 row inserted SQL> insert into tb_pagto_func values (2, trunc(sysdate), 325.80 ); 1 row inserted SQL> commit; Commit complete SQL>
1: Operações DDL ONLINE:
Certo, vamos aos nossos testes. Vamos ver algumas operações DDL que conseguimos fazer de maneira ONLINE.
Nosso cenário:
SQL> select * from tb_funcionarios; ID_FUNC NOME_FUNC DATACAD ---------- ---------------------------------------- ----------- 1 JOSE DA SILVA 20/02/2018 2 ANA MARIA 20/02/2018 SQL> select * from tb_pagto_func; ID_FUNC DATAPAGTO VLRPAGTO ---------- ----------- ------------ 1 20/02/2018 500,00 1 21/02/2018 700,00 2 20/02/2018 325,80
Faremos um pequeno resumo de algumas operações que não conseguíamos realizar sem a opção ONLINE em um ambiente altamente transacional.
Agora você pode usar a nova palavra-chave ONLINE para também permitir a execução de instruções DML durante as seguintes operações DDL:
: alter table ... set unused (column) online:
Imagine um ambiente transacional, e uma transação é iniciada pelo usuário da SESSÃO 1, obtendo um lock no objeto em questão.
A sessão 1 inicia a transação e ainda não finalizou-a com commit ou rollback, veja:
USUÁRIO SESSÃO 1:
SQL> update tb_funcionarios set datacad = datacad + 1 where id_func = 1; 1 row updated SQL>
Neste ponto outra sessão, tenta o comando ddl abaixo sem a opção online. O usuário recebe erro, porque sem o online era exigido lock no objeto e o objeto já está envolvido pelo lock transacional emitido pelo usuário da sessão 1.
Veja:
USUÁRIO SESSÃO 2:
SQL> alter table tb_funcionarios set unused (nome_func);
alter table tb_funcionarios set unused (nome_func)
ORA-00054: o recurso está ocupado e é obtido com o NOWAIT especificado ou o timeout expirou
SQL>
Ok, mas em seguida a mesma sessão 2 utiliza agora a opção: online(alter table tb_funcionarios set unused (nome_func) online;), e neste ponto a sessão 2 ficará aguardando até terminar a transação da sessão 1, mas, nada impede que outras sessões iniciem transações na tabela, veja a SESSÃO 3:
USUÁRIO SESSÃO 3:
A sessão 3 inicia uma transação em outros registros da tabela no meio do alter table da sessão 2. Sem problemas! O alter será liberado para o usuário da sessão 2 após o final da transação da sessão 1.
SQL> update tb_funcionarios set datacad = datacad + 1 where id_func = 2; 1 row updated SQL>
USUÁRIO SESSÃO 2:
Após o commit da sessão 1 a sessão com alter é liberada.
SQL> alter table tb_funcionarios set unused (nome_func) online;
Table altered
SQL>
: alter index index_name unusable online;
Outro DDL que tem a opção ONLINE é o: alter index index_name unusable online, vamos ter uma idéia. O usuário da sessão 1 inicia uma transação(update) na tabela:
USUÁRIO SESSÃO 1
SQL> update tb_funcionarios set datacad = datacad + 1 where id_func = 1; 1 row updated SQL> commit; Commit complete SQL>
Neste ponto se outro usuário, com a sessão 2 por exemplo emitir o: alter index index_name unusable sem a opção ONLINE, receberá o erro abaixo:
USUÁRIO SESSÃO 2
SQL> alter index idx_datacad unusable; alter index idx_datacad unusable ORA-00054: o recurso está ocupado e é obtido com o NOWAIT especificado ou o timeout expirou
Mesmo cenário anterior! O comando é repetido, porém, agora com a opção ONLINE(alter index index_name unusable online). A sessão 2 ficará aguardando até a sessão 1 finalizar a transação e então o alter index será liberado para o usuário:
SQL> alter index idx_datacad unusable online; Index altered SQL>
: drop index online:
Temos o mesmo comportamento para o drop do index. O usuário com a sessão 1 inicia uma transação:
USUÁRIO SESSÃO 1
SQL> update tb_pagto_func set vlrpagto = 935.20 where id_func = 2 and datapagto = trunc(sysdate); 1 row updated SQL> COMMIT; Commit complete SQL>
Se outro usuário, denominado sessão 2 tentar dropar um índice que estará envolvido para auxiliar uma transação, ele receberá o erro abaixo:
USUÁRIO SESSÃO 2
SQL> drop index idx_datapagto; drop index idx_datapagto ORA-00054: o recurso está ocupado e é obtido com o NOWAIT especificado ou o timeout expirou
O usuário emite o drop porém agora com online: drop index idx_datapagto online;
Este usuário ficará aguardando até a finalização da transação anterior e após a finalização da transação o drop é liberado para o usuário:
SQL> drop index idx_datapagto online; Index dropped SQL>
:alter table ... drop constraint ... online:
Uma evolução bem interessante que recebeu a opção ONLINE, é no ddl: alter table table_name drop constraint constraint_name online.
Em ambientes altamente transacionais este comando era um grande candidato a ter parada da aplicação para aplicação no banco. O usuário com a sessão 1 inicia um transação na tabela pai:
USUÁRIO SESSÃO 1
SQL> update tb_funcionarios set datacad = datacad + 1 where id_func = 1; 1 row updated SQL>
Outro usuário, agora com a sessão 2 inicia uma transação, mas, agora na tabela filho:
USUÁRIO SESSÃO 2
SQL> update tb_pagto_func set vlrpagto = 935.20 where id_func = 2 and datapagto = trunc(sysdate); 1 row updated SQL>
Neste ponto um outro usuário com a sessão 3 tenta eliminar uma FK sem ONLINE no meio de uma transação não finalizada. Ele receberá o erro:
USUÁRIO SESSÃO 3
SQL> alter table tb_pagto_func drop constraint fk_funcionarios;
alter table tb_pagto_func drop constraint fk_funcionarios
ORA-00054: o recurso está ocupado e é obtido com o NOWAIT especificado ou o timeout expirou
Após este erro o usuário emite o drop constraint com ONLINE: alter table tb_pagto_func drop constraint fk_funcionarios online. Ele também ficará aguardando a transação finalizar.
Porém, é liberado o drop após o commit da SESSÃO 2, e olha que legal, MESMO QUE A SESSÃO 1 (DO PAI) NÃO ESTEJA FINALIZADA:
SQL> alter table tb_pagto_func drop constraint fk_funcionarios online; Table altered SQL>
:TRUNCATE TABLE CASCADE
E para finalizar nosso artigo, a partir do Oracle Database 12c Release 1 (12.1.0.1) também tivemos uma evolução no comando TRUNCATE TABLE. Ele agora tem a opção CASCADE, que permite a a execução do comando truncate de maneira recursiva e truncar tabelas filho.
Veja os dados que temos nas duas tabelas. Temos uma FK(com a opção ON DELETE CASCADE) ligando a tabela pai e filha:
SQL> select * from tb_funcionarios; ID_FUNC NOME_FUNC DATACAD ---------- ---------------------------------------- ----------- 1 JOSE DA SILVA 20/02/2018 2 ANA MARIA 20/02/2018 SQL> select * from tb_pagto_func; ID_FUNC DATAPAGTO VLRPAGTO ---------- ----------- ------------ 1 20/02/2018 500,00 1 21/02/2018 700,00 2 20/02/2018 325,80 SQL>
Antes desta versão do Oracle, se tentassemos emitir um truncate na tabela pai, com filhos existentes e mesmo com a opção ON DELETE CASCADE habilitada, o seguinte erro era emitido:
SQL> truncate table tb_funcionarios;
truncate table tb_funcionarios
ORA-02266: chaves exclusiva/primária na tabela referenciadas por chaves externas ativadas
Agora não, é eliminado em cascata:
Se você incluir a opção CASCADE no truncate na tabela pai, todos os dados serão eliminados na tabela filho e pai, veja:
SQL> truncate table tb_funcionarios cascade;
Table truncated
SQL>
SQL> select * from tb_funcionarios;
ID_FUNC NOME_FUNC DATACAD
---------- ---------------------------------------- -----------
SQL> select * from tb_pagto_func;
ID_FUNC DATAPAGTO VLRPAGTO
---------- ----------- ------------
SQL>
Abraço,
Ronaldo.
Nenhum comentário:
Postar um comentário