Oracle Move Tablespace – Movimentando Objetos Entre Tablespaces

Neste ambiente, o nosso sistema de monitoramento alertou falta de espaço em disco.

Ao analisarmos o problema, encontramos vários datafiles de tamanhos diversos para a tablespace do sistema Winthor.
Os objetos estavam distribuidos em diversos datafiles, impossibilitando o RESIZE dos datafiles.
Para reduzir a quantidade de espaço sendo utilizado pela tablespace, optei por utilizar o MOVE TABLESPACE, movendo os objetos de uma tablespace para outra.
A Tablespace possui Tabelas, Índices, Materialized Views e Lobs.

Como o Banco de Dados é Standard Edition, não é possivel realizar o MOVE de forma online. Fique atento, pois o processo causa indisponibilidade nos objetos!

Preparando o Ambiente de Testes

SQL> CREATE TABLESPACE OLD_TABLESPACE DATAFILE '/u02/oradata/WINT/OLD_TABLESPACE01.DBF' SIZE 100M;
SQL> CREATE TABLESPACE NEW_TABLESPACE DATAFILE '/u02/oradata/WINT/NEW_TABLESPACE01.DBF' SIZE 100M;

SQL> ALTER USER KELWIN QUOTA UNLIMITED ON OLD_TABLESPACE;
SQL> ALTER USER KELWIN QUOTA UNLIMITED ON NEW_TABLESPACE;

SQL> CREATE TABLE TESTE TABLESPACE OLD_TABLESPACE AS SELECT * FROM ALL_OBJECTS;

SQL> CREATE INDEX KELWIN.TESTE_IDX ON KELWIN.TESTE (OBJECT_NAME) TABLESPACE OLD_TABLESPACE;

SQL> CREATE MATERIALIZED VIEW KELWIN.MVIEW_TESTE TABLESPACE OLD_TABLESPACE BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS SELECT * FROM KELWIN.TESTE;

SQL> CREATE TABLE TESTE_LOB (CODIGO NUMBER(10),NOME CLOB) TABLESPACE OLD_TABLESPACE LOB ("NOME") STORE AS BASICFILE (TABLESPACE "OLD_TABLESPACE");

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, TABLESPACE_NAME FROM DBA_OBJECTS WHERE OWNER = 'KELWIN';

OWNER         OBJECT_NAME                     OBJECT_TYPE
------------ ------------------------------ -----------------------
KELWIN       TESTE_IDX                      INDEX
KELWIN       TESTE                          TABLE
KELWIN       MVIEW_TESTE                    TABLE
KELWIN       MVIEW_TESTE                    MATERIALIZED VIEW
KELWIN       TESTE_LOB                      TABLE
KELWIN       SYS_LOB0000064475C00002$$      LOB

Movendo Tabelas (MOVE TABLESPACE)

SQL> SELECT 'ALTER TABLE '||OWNER||'.'||SEGMENT_NAME||' MOVE TABLESPACE NEW_TABLESPACE;' AS COMANDO FROM DBA_SEGMENTS WHERE TABLESPACE_NAME LIKE 'OLD_TABLESPACE' AND SEGMENT_TYPE = 'TABLE';

COMANDO
-----------------------------------------------------------------------------------------
ALTER TABLE KELWIN.TESTE MOVE TABLESPACE NEW_TABLESPACE;
ALTER TABLE KELWIN.TESTE_LOB MOVE TABLESPACE NEW_TABLESPACE;

SQL> ALTER TABLE KELWIN.TESTE MOVE TABLESPACE NEW_TABLESPACE;

Tabela alterada.

SQL> ALTER TABLE KELWIN.TESTE_LOB MOVE TABLESPACE NEW_TABLESPACE;

Tabela alterada.

Quando realizamos o MOVE da tabela, os índices ficam com status UNUSABLE. Vamos fazer o REBUILD dos índices já movendo para nova tablespace.

SQL> SELECT INDEX_NAME, STATUS FROM DBA_INDEXES WHERE OWNER = 'KELWIN' AND STATUS <> 'VALID';

INDEX_NAME           STATUS
-------------------- --------
TESTE_IDX            UNUSABLE

SQL> SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD TABLESPACE NEW_TABLESPACE;' AS COMANDO FROM DBA_INDEXES WHERE STATUS IN ('UNUSABLE','INVALID') AND TABLESPACE_NAME LIKE 'OLD_TABLESPACE';

SQL> ALTER INDEX KELWIN.TESTE_IDX REBUILD TABLESPACE NEW_TABLESPACE;

Índice alterado.

Movendo Materialized Views

SQL> SELECT 'ALTER MATERIALIZED VIEW '||OBJECT_NAME||' MOVE TABLESPACE NEW_TABLESPACE;' AS COMANDO FROM DBA_OBJECTS WHERE OWNER = 'KELWIN' AND OBJECT_TYPE = 'MATERIALIZED VIEW';

COMANDO
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER MATERIALIZED VIEW MVIEW_TESTE MOVE TABLESPACE NEW_TABLESPACE;

SQL> ALTER MATERIALIZED VIEW MVIEW_TESTE MOVE TABLESPACE NEW_TABLESPACE;

View materializada alterada.

Movendo Lobs

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER = 'KELWIN';

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
KELWIN                         SYS_LOB0000064475C00002$$      LOBSEGMENT         OLD_TABLESPACE

SQL> SELECT TABLE_NAME, COLUMN_NAME FROM DBA_LOBS WHERE SEGMENT_NAME = 'SYS_LOB0000064475C00002$$';

TABLE_NAME           COLUMN_NAME
-------------------- --------------------
TESTE_LOB            NOME

SQL> SELECT DBA_LOBS.TABLE_NAME, DBA_SEGMENTS.SEGMENT_NAME, DBA_SEGMENTS.SEGMENT_TYPE, DBA_SEGMENTS.TABLESPACE_NAME, DBA_LOBS.COLUMN_NAME 
,'ALTER TABLE '||DBA_SEGMENTS.OWNER||'.'||DBA_LOBS.TABLE_NAME||' MOVE LOB ('||DBA_LOBS.COLUMN_NAME||') STORE AS (TABLESPACE NEW_TABLESPACE);' AS COMANDO
FROM DBA_SEGMENTS, DBA_LOBS 
WHERE DBA_SEGMENTS.SEGMENT_NAME = DBA_LOBS.SEGMENT_NAME 
AND DBA_SEGMENTS.TABLESPACE_NAME = 'OLD_TABLESPACE';

COMANDO
---------------------------------------------------------------------------------------
ALTER TABLE KELWIN.TESTE_LOB MOVE LOB (NOME) STORE AS (TABLESPACE NEW_TABLESPACE);

SQL> ALTER TABLE TESTE_LOB MOVE LOB (NOME) STORE AS (TABLESPACE NEW_TABLESPACE);

Tabela alterada.

Pronto! Agora iremos verificar se ainda existem objetos na tablespace antiga.

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME  = 'OLD_TABLESPACE';

não há linhas selecionadas

O Ultimo passo é remover a tablespace antiga e renomear a nova tablespace.

--Tenha Cuidado!

SQL> DROP TABLESPACE OLD_TABLESPACE INCLUDING CONTENTS AND DATAFILES;

Tablespace eliminado.

SQL> ALTER TABLESPACE NEW_TABLESPACE RENAME TO OLD_TABLESPACE;

Tablespace alterado.

Depois de todo o processo realizado, nosso sistema de monitoramento reconheceu que o espaço em disco do sistema operacional foi normalizado e o incidente foi finalizado de forma automática.