Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


segunda-feira, 1 de dezembro de 2014

Mover segmentos de LOB deixam os índices da tabela inutilizáveis (UNUSABLE)

Por Eduardo Legatti

Olá,

No artigo de Janeiro/2014 eu abordei sobre a realocação de objetos de bancos de dados entre tablespace distintas. Dentre os objetos que foram abordados no artigo, estão as tabelas, os índices e os segmentos de LOB. Neste artigo irei tratar mais especificamente sobre as consequências de se mover um segmento LOB. Vale a pena salientar que segmentos LOB tem sua origem em colunas de tabelas com tipos de dados (CLOB e BLOB). Abaixo, realizarei uma simulação na qual irei mover um segmento de LOB proveniente de uma coluna BLOB, utilizando o Oracle 12c (12.1.0.2).
 
C:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Seg Dez 1 13:14:21 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Conectado a:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set current_schema=SCOTT;

Sessão alterada.

SQL> create table t1 (id number, nome varchar2(100), imagem blob);

Tabela criada.

SQL> alter table t1 add constraint t1_pk primary key (id);

Tabela alterada.

SQL> create index t1_idx_nome on t1 (nome);

Índice criado.

SQL> insert into t1 values (1,'Miguel',empty_blob());

1 linha criada.

SQL> insert into t1 values (2,'Laura',empty_blob());

1 linha criada.

SQL> commit;

Commit concluído.
 
Após a criação da tabela T1 no schema SCOTT, podemos verificar abaixo que os índices que foram criados na tabela estão com os status VALID.

SQL> select rowid,owner,index_name,status from dba_indexes where owner='SCOTT';

OWNER      INDEX_NAME                     STATUS
---------- ------------------------------ --------
SCOTT      T1_PK                          VALID
SCOTT      T1_IDX_NOME                    VALID
SCOTT      SYS_IL0000020439C00003$$       VALID
  
Abaixo estão listados os registros da tabela com os respectivos ROWIDs de cada linha.
 
SQL> select rowid,id,nome from t1;

ROWID                      ID NOME
------------------ ---------- --------------------
AAAFAFAAEAAAACHAAA          1 Miguel
AAAFAFAAEAAAACHAAB          2 Laura


O objetivo agora será mover o segmento de LOB, proveniente da coluna IMAGEM que está atualmente armazenado na tablespace USERS, para a tablespaces TS_LOB. Como demonstrado abaixo, poderemos consultar a view DBA_LOBS para verificar em qual tablespace está armazenado o segmento de LOB.

SQL> select owner,table_name,column_name,tablespace_name
  2  from dba_lobs
  3  where owner='SCOTT';

OWNER      TABLE_NAME      COLUMN_NAME     TABLESPACE_NAME
---------- --------------- --------------- --------------------
SCOTT      T1              IMAGEM          USERS


Apenas para fins de demonstração, irei utilizar a instrução SQL abaixo para automatizar a criação do comando ALTER TABLE que irá mover o segmento de LOB para a tablespace TS_LOB. Vale a pena salientar que essa instrução é útil quando queremos mover vários segmentos de LOB.

SQL>   SELECT   'alter table '
  2           || owner
  3           || '.'
  4           || table_name
  5           || ' move lob ('
  6           || column_name
  7           || ') store as (tablespace TS_LOB);' as DDL
  8      FROM dba_lobs
  9      where owner='SCOTT'
 10      ORDER BY owner, table_name;

DDL
--------------------------------------------------------------------
alter table SCOTT.T1 move lob (IMAGEM) store as (tablespace TS_LOB);
  
Vamos então a execução do comando ALTER TABLE ... MOVE LOB.
 
SQL> alter table SCOTT.T1 move lob (IMAGEM) store as (tablespace TS_LOB);

Tabela alterada.

SQL> select rowid,id,nome from t1;

ROWID                      ID NOME
------------------ ---------- ---------------------
AAAFALAAEAAAACzAAA          1 Miguel
AAAFALAAEAAAACzAAB          2 Laura  

Após a execução do comando ALTER TABLE, podemos verificar abaixo que os ROWIDs das linhas da tabela T1 mudaram. Como consequência, os índices da tabela ficaram com status UNUSABLE e assim como acontece quando movemos uma tabela, será necessário reconstruir os índices (REBUILD). 
 
SQL> select owner,index_name,status from dba_indexes where owner='SCOTT';

OWNER      INDEX_NAME                     STATUS
---------- ------------------------------ --------
SCOTT      T1_PK                          UNUSABLE
SCOTT      T1_IDX_NOME                    UNUSABLE
SCOTT      SYS_IL0000020439C00003$$       VALID


A não ser que o parâmetro SKIP_UNUSABLE_INDEXES esteja setado como TRUE, qualquer comando DML efetuado na tabela irá ocasionar o erro ORA-01502 como demonstrado abaixo.

SQL> insert into t1 values (3,'Angela',empty_blob());
insert into t1 values (3,'Angela',empty_blob())
*
ERRO na linha 1:
ORA-01502: índice 'SCOTT.T1_PK' ou a sua partição está em estado não utilizável 

Para resolver o problema, será necessário realizar o REBUILD dos índices conforme demonstrado abaixo.

SQL> alter index t1_pk rebuild;

Índice alterado.

SQL> alter index t1_idx_nome rebuild;

Índice alterado.

SQL> select owner,index_name,status from dba_indexes where owner='SCOTT';

OWNER      INDEX_NAME                     STATUS
---------- ------------------------------ --------
SCOTT      T1_PK                          VALID
SCOTT      T1_IDX_NOME                    VALID
SCOTT      SYS_IL0000020439C00003$$       VALID

Dependendo do tamanho de uma tabela e do tamanho dos segmentos de LOB envolvidos, mover um segmento de LOB de modo OFFLINE poderá ocasionar efeitos indesejados no ambiente pois a tabela ficará bloqueada (LOCK) durante a realocação do segmento de LOB. Para evitar esse problema eu aconselho realizar a realocação ONLINE através do uso da package DBMS_REDEFINITION. Os artigos de Abril/2011 e Dezembro/2012 eu abordo o uso desta package.

Por fim, existe uma nota (Doc ID 1228324.1) no My Oracle Support (Metalink) que aborda esse comportamento dos índices ficarem no estado UNUSABLE ao mover um segmento de LOB. A nota não é muito clara se isso seria um bug do Oracle ou um bug na documentação que não chama a atenção para esse comportamento.
 



Nenhum comentário:

Postagens populares