Solutions 4 Us

Oracle - Limpando tablespace SYSAUX

2022-06-30T07:59:00.006-07:00

Olá Pessoal!
Em algumas situações nós DBA podemos identificar o crescimento repentino da tablespace SYSAUX, isso acontece as vezes por gerar arquivos maiores do AWR e mesmo com a retenção configurada, pode ser necessário aplicar a limpeza manual deixando com uma retenção menor.

Nesse exemplo, estamos com a retenção de 60 dias para os relatórios AWR, efetuaremos uma limpeza manual deixando apenas 30 dias para obter a redução e liberação de espaço na tablespace:

Então, vamos ao procedimento:

Consumo atual:

Verificando os objetos que estão ocupando espaço na tablespace, podemos ver que trata-se do AWR:

Query:

SELECT occupant_name,
round( space_usage_kbytes/1024) " SPACE_MB",
schema_name,
move_procedure
FROM v$sysaux_occupants
ORDER BY 1;

Verificando os SNAPs do AWR para aplicarmos a remoção:

Query:

SELECT snap_id, instance_number, begin_interval_time
     FROM SYS.WRM$_SNAPSHOT
   WHERE instance_number = 1
   order by 1;

Aplicando a limpeza de 30 dias de snaps do AWR:

Query:

execute dbms_workload_repository.drop_snapshot_range( low_snap_id => 19614, high_snap_id=>19667);

Feito isso, o espaço na tablespace já estará liberado:

Verificando objetos que estão ocupando a tablespace SYSAUX:

SELECT occupant_name,
round( space_usage_kbytes/1024) " SPACE_MB",
schema_name,
move_procedure
FROM v$sysaux_occupants
ORDER BY 1;

Erro ORA-00600 [1115] - Oracle 12c

2021-06-11T06:12:00.001-07:00

Pessoal.

Gostaria de compartilhar sobre um problema que ocorreu recentemente em um processo no banco de dados Oracle 12c. Ao iniciar a execução, o mesmo estourava os seguintes erros:

ORA-00600: internal error code, arguments: [1115]

ou

ORA-00600: código de erro interno, argumentos: [18088]

ou

ORA-00600: código de erro interno, argumentos: [kglpnmi_bad_migration]

Os erros abortavam a sessão do usuário e também em alguns momentos derrubavam a instance do banco de dados.

Para versão 12c, estava com dificuldade de identificar alguma nota sobre o caso e query compartilhar com vocês o procedimento realizado para resolver a situação.

Uma solução, seria subir de versão para 18 ou 19c, porém, o cliente estava em um momento crítico e não poderíamos ter uma parada do ambiente todo naquele momento.

Como usávamos na aplicação o Drive ODP.NET, a solução foi configurar esse parametro na string de conexão somente do terminal que apresentava o problema:

enlist=false

também identifiquei algumas recomendações de aplicar o Patch 24355111, para correção do problema, mas, não cheguei a validar se realmente iria resolver o caso.

Espero ter ajudar.

Query - Vasculhar determinada informação em todos objetos do banco de dados - Oracle

2020-09-23T11:12:00.001-07:00

Olá pessoal,
Vamos a mais uma query para facilitar a busca de informações no banco de dados Oracle.
Dessa vez, segue abaixo a query para vasculhar determinada informação em todos os objetos no banco de dados.

Normalmente utilizo quando o cliente solicita, para verificar determinado objeto que pode estar gerando informações ou arquivos com extensão específicas.

Por exemplo, imagine que existe algum processo no banco de dados que gera arquivos com extensão ".X91".

Para identificar isso, podemos utilizar a seguinte query:

Select * from all_source where upper(text) like '%X91%';

Você pode adicionar o owner para facilitar a busca, caso já saiba onde pode estar o objeto.

Select * from all_source where upper(text) like '%X91%' and owner='OWNER_NAME';

Oracle 11g - Error in invoking target 'agent nmhs' of makefile

2020-03-03T06:45:00.003-08:00

Pessoal.

Durante a instalação do Oracle 11.2.0.4 no Linux 7, recebi o seguinte alerta:

Error in invoking target 'agent nmhs' of makefile

Conforme imagem abaixo:

Para corrigir o problema você pode aplicar a solução de contorno ou conforme suporte da Oracle, existe um patch para aplicar e corrigir o problema, você pode seguir com a instalação e aplicar o patch 19692824 no final. Abaixo segue procedimento para solução de contorno para esse alerta.

Durante a instalação, quando a mensagem de alerta aparecer, não clique nas opções da janela exibida (abortar, repetir ou continuar).

Abra uma nova sessão no sistema operacional e edite o arquivo ins_emagent.mk localizado no $ORACLE_HOME/sysman/lib/.. Procure pela linha $(MK_EMAGENT_NMECTL). No final da linha, deve ser acrescentada a opção

-lnnz11. Veja abaixo o exemplo aplicado:

in $ORACLE_HOME/sysman/lib/ins_emagent.mk

Localize a linha: $(MK_EMAGENT_NMECTL)

Acrescente -lnnz11 ao final: $(MK_EMAGENT_NMECTL) -lnnz11

Após a alteração, podemos clicar na opção "Repetir"da instalação e o processo continuará com sucesso.

Este problema ocorre devido ao bug 19692824 documento.

Oracle 12c - índice SYS.WRH$_SGASTAT_U inválido após upgrade

2020-02-28T05:06:00.000-08:00

Olá pessoal.
Recentemente fiz o upgrade do ambiente Oracle 11g (11.2.0.4) para o Oracle 12c (12.2.0.1) e após alguns dias verifiquei o seguinte índice inválido WRH$_SGASTAT_U:

SYS @ db12c > SELECT table_owner, table_name, index_name
FROM dba_indexes
WHERE index_type = 'NORMAL'
AND table_name IN (
SELECT table_name
FROM dba_tables
WHERE table_name LIKE 'WRH$\_%' ESCAPE '\'
AND partitioned = 'YES')
AND partitioned = 'NO';

TABLE_OWNER TABLE_NAME INDEX_NAME
--------------------------- -------------------------- ---------------------------
SYS WRH$_SGASTAT WRH$_SGASTAT_U

Verificando o suporte da Oracle e identifiquei que o problema trata-se de um BUG da versão 12c do Oracle, o BUG 25954054, descrito no seguinte do ID 25954054.8, informa que o problema pode ocorrer após o upgrade de versão.

Para corrigir o problema, realizei a seguinte solução, conforme recomendado pela Oracle:

Lembrando que será necessário estar conectado no ambiente de banco de dados com o usuário SYS devido as permissões.

SYS @ db12c > alter table wrh$_sgastat drop constraint wrh$_sgastat_u;

Table altered.

SYS @ db12c > alter session set events '14524 trace name context forever, level 1';

Session altered.

SYS @ db12c > alter table wrh$_sgastat add constraint wrh$_sgastat_u

unique (dbid, snap_id, instance_number, name, pool, con_dbid,

stattype) using index local tablespace SYSAUX; 2 3

Table altered.

SYS @ db12c > alter session set events '14524 trace name context off';

Session altered.

SYS @ db12c >

Espero ter ajudado!
Abraço.

ORA-20000: Unable to analyze TABLE insufficient privileges or does not exist

2020-01-23T07:12:00.001-08:00

Olá pessoal.
Recentemente tive um problema para coletar estatísticas no meu banco de dados (versão 11.2.0.4), recebia o seguinte erro com frequência:

ORA-20000: Unable to analyze TABLE "OWNER"."TABLE_NAME", insufficient privileges or does not exist

Porém, quando realizava a coleta manualmente do objeto que apresentava o problema, não gerava nenhum erro.

Para corrigir o problema, como eu executo a coleta de estatísticas com o usuário SYSTEM foi necessário conceder duas permissões ao mesmo, segue:

SQL> grant analyze any to system;

Grant succeeded.

SQL> grant analyze any dictionary to system;

Grant succeeded.

Posteriormente executei a procedure de coleta de estatística manualmente e com sucesso:

SQL> exec system.PROC_STATISTICS_SEMANAL;

PL/SQL procedure successfully completed.

SQL>

Utilitário usado para coletar as estatísticas:
dbms_stats.gather_table_stats

Método da coleta:
auto_sample_size

Espero ter ajudado!
Abraços.

ORA-02374: conversion error loading table

2019-10-21T07:25:00.001-07:00

Olá pessoal.

O erro ORA-02374: conversion error loading table, ocorre quando tentamos importar via DataPump, IMPDP um dump que foi gerado de outro banco de dados com NLS_CHARACTERSET diferente.

Para corrigir esse problema, vamos precisar ajustar o NLS_CHARACTERSET do banco de dados que estamos tentando importar.

Segue procedimento abaixo:

## Verificar o NLS_CHARACTERSET do banco de dados que estamos importando as informações:

SQL> select VALUE
from nls_database_parameters
where parameter='NLS_CHARACTERSET'; 2 3

VALUE
--------------------------------------------------------------------------------
AL32UTF8

## Parar o banco de dados:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

## Iniciar o banco de dados no modo Restrito:
SQL> startup restrict;
ORACLE instance started.

Total System Global Area 2.0200E+10 bytes
Fixed Size 12169992 bytes
Variable Size 4026535160 bytes
Database Buffers 1.6106E+10 bytes
Redo Buffers 54935552 bytes
Database mounted.
Database opened.

## Alterar o NLS_CHARACTERSET:
SQL> alter database character set INTERNAL_USE WE8MSWIN1252;

Database altered.

## Parar o banco de dados novamente:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

## Iniciar o banco de dados novamente, modo normal.
SQL> startup
ORACLE instance started.

Total System Global Area 2.0200E+10 bytes
Fixed Size 12169992 bytes
Variable Size 4026535160 bytes
Database Buffers 1.6106E+10 bytes
Redo Buffers 54935552 bytes
Database mounted.
Database opened.

## Verifique o valor do NLS_CHARACTERSET, e veja que o mesmo foi atualizado:
SQL> select VALUE
from nls_database_parameters
where parameter='NLS_CHARACTERSET';

VALUE
----------------------------------------------------------------
WE8MSWIN1252

SQL> exit

Após isso, iniciar novamente a importação dos dados.

Qualquer dúvida, deixe seu comentário.
Abraço.

SQLServer - Instalar Banco de Dados de Teste Adventure Works

2019-09-16T08:48:00.001-07:00

Olá!
Para realizar o Download do Banco de Dados De testes da Microsoft, acesse o link abaixo:

https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

Adventure Works Bicicletas, Inc. é um atacadista de bicicleta fictício que vende e fabrica bicicletas e vende Acessórios roupas e ciclo para revendedores por todo o país.
As pessoas na empresa realizar atividades típicos da empresa como:
• Vendas
• Configuração de vendas e outras metas de negócios
• Preenchimento
• Gerenciar inventário
• Gerenciando feiras
• Gerenciando pessoal
• Orçamento

Bom, então vamos para instalação do Banco de Dados Adventure Works!

Via Código:

• Use master

• RESTORE DATABASE AdventureWorks2014

• FROM disk '(Caminho onde estáo arquivo. Ex.: C:\Users\Teste\Download\AdventureWorks2014.bak)'

• WITH MOVE ’AdventureWorks2014_data'

• TO '(Caminho para onde será movido o arquivo .mdf)’,

• MOVE ’AdventureWorks2014_Log'

• TO ’(Caminho para onde serámovido o arquivo .ldf)’, REPLACE
Ou podemos fazer também Via Design:

Exemplo, vamos restaurar o backup usando um banco de dados já criado para testes, veja abaixo que estamos usando o banco de dados nomeado como "BANCO_NOVO".

Como vamos substituir o nosso banco de dados de exemplo, no caso "BANCO_NOVO", marque a opção "WITH REPLACE", conforme exemplo abaixo:

Pronto, posteriormente cliente em "OK". que iniciará a restauração.

Qualquer dúvida, deixe o seu comentário.

Abraço.

Procedimento para enable/disable/run/stop da Scheduler Jobs

2019-04-16T05:59:00.002-07:00

Olá.
Segue abaixo procedimento simples para:
Habilitar, Desabilitar, Executar e Parar execução de JOBs da DBA_SCHEDULER_JOBS.

Será necessário conectar com o usuário, owner do JOB, ou, referenciar o owner no comando para realizar as atividades, no meu exemplo, informo o owner do JOB.

Antes de tudo, verifique se o parametro "job_queue_processes", está diferente de "0", caso o mesmo esteja com o valor "0", os JOBs não vão conseguir executar.

Segue comando para tal:

SQL> alter system set job_queue_processes=10;

Obs.: No exemplo acima o Oracle foi configurado para executar até 10 JOBs simultâneos, ajuste o parâmetro de acordo com a capacidade do seu ambiente.

Continuando:

Executar JOB da DBA_SCHEDULER_JOBS:

SQL> exec dbms_scheduler.run_job('OWNER.JOB_NAME',false);

Obs.: No comando acima, o "false" que informei no final é apenas para liberar o prompt de comando e não ficar aguardando até a conclusão da execução do JOB.

Parar JOB da DBA_SCHEDULER_JOBS:

SQL> exec dbms_scheduler.stop_job('OWNER.JOB_NAME');

Habilitar JOB da DBA_SCHEDULER_JOBS:

SQL> exec dbms_scheduler.enable('OWNER.JOB_NAME');

Desabilitar JOB da DBA_SCHEDULER_JOBS:

SQL> exec dbms_scheduler.enable('OWNER.JOB_NAME');

Qualquer dúvida, deixe o seu comentário!
Espero ter ajudado.

Coleta de estatística com ORA-7445 devido corrupção lógica de dados

2019-04-12T06:32:00.004-07:00

Caros,
Recentemente me deparei com um problema durante a coleta de estatísticas de um ambiente de banco de dados.

ORA-07445: exceção encontrada: dump de memória [kafger_srp()+157] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x145E6B04F] [UNABLE_TO_READ] []
Durante a coleta de estatísticas, a mesma estava falhando e apresentava o erro acima no arquivo de alerta do banco de dados.
Ao executar a coleta de estatísticas manualmente, recebia o erro:

SQL> exec dbms_stats.gather_table_stats('SCHEMA','TABLE_NAME',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,method_opt=>'for all columns size skewonly',degree=>4);

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7736
Session ID: 7 Serial number: 26973

Pesquisando sobre o caso, identifiquei uma nota no Suporte da Oracle que 
disponibiliza um scripts para validação de dados corrompidos a nível de

coluna, através da Doc ID 428526.1. 

A nota solicita para criar o seguinte procedimento que deve ser executado com o "SYS":
Segue abaixo:


drop package body baddata;
drop package baddata;

REM BADDATA.SQL 
REM
REM  Purpose:   The purpose of this package is to identify invisibly
REM             corrupted dates , numbers and character strings 
REM
REM USAGE
REM ~~~~~
REM  The BADDATA package allows you to check a named table, schema or 
REM  entire database for corrupt DATE , NUMBER and CHAR/VARCHAR2 values.
REM  The idea is that you run this package to get a list of columns which 
REM  currently contain data which seems to be corrupt.
REM  The output shows bad row counts 
REM 
REM  Please note this is an example script only.
REM  There is no guarantee associated with the output it presents.
REM
REM  Steps to install:
REM     1. Install this package in the SYS schema
REM        Eg: connect internal
REM            @baddata
REM        This should create a BADDATA$$ control table and a
REM        BADDATA package.
REM        If installing in a 7.1 database then comment out the
REM        call to DBMS_APPLICATION_INFO below.
REM
REM  Steps to use:
REM     1. Populate the BADDATA$$ table with any tables / columns to ignore
REM        If you are not sure you can ignore this step for now.
REM     2. Ensure SPOOL is enabled to catch output and enable SERVEROUT
REM        Eg:
REM             spool mybaddata.log
REM             execute dbms_output.enable(1000000);
REM             set serveroutput on
REM     3. Run one of:
REM             BadData.CheckDB;
REM             BadData.CheckSchema('SCHEMANAME');
REM             BadData.CheckObject('SCHEMANAME','TABLENAME');
REM        to check the entire database / named schema or named table
REM        This will run until all requested items are scanned or
REM        an error occurs.
REM        If any bad columns are found then the tablename / column and 
REM        row counts are reported
REM
REM     4. If an error occurs results so far are dumped.
REM        Depending on the error you may then:
REM             BadData.LastError; to see the error again
REM             BadData.Continue;  to re-start processing at the table
REM                                we just errored on
REM             BadData.Skip;      to mark that we should skip the
REM                                table we errored on by inserting
REM                                a row in BADDATA$$
REM     5. If you want some SQL to look at the failing rows in a table
REM        in more detail use the GetSQL call:
REM             BadData.GetSQL('OWNER','TABLENAME')
REM                to give a select statement for the named table
REM
REM  Notes: 
REM     This package keeps the name of the current owner/table being 
REM     scanned in V$SESSION in the MODULE and ACTION columns.
REM     This can be used to monitor progress.
REM 
REM     This script should not be used against the SYS schema.
REM
REM =====================================================================
REM Set serverout on and enable a large buffer
REM
set serverout on
execute dbms_output.enable(1000000);

REM =====================================================================
REM Table BADDATA$$
REM =====================================================================
REM   This table contains information on Schemas, Tables, Columns
REM   and column data values which can be ignored whilst checking for
REM   suspect values by the BADDATA package
REM 
REM   The table columns are:
REM     ITEM            This determines what this BADDATA$$ row means:
REM                       OWNER   skips the entire schema for OWNER
REM                       TABLE   skips the entire OWNER . TABLE_NAME
REM                       COLUMN  skips COLUMN_NAME in OWNER . TABLE_NAME
REM     OWNER           Case sensitive schema name to ignore
REM     TABLE_NAME      Case sensitive table name to ignore
REM     COLUMN_NAME     Case sensitive column name to ignore
REM     BOOT            Should be NULL. Only rows inserted by this script
REM                     should have a value of 1 allowing this script
REM                     to be re-run without losing data.
REM                     This script will delete rows with a value 1.
REM
create table BADDATA$$ (
        item            varchar2(10),   /* COLUMN, TABLE, OWNER */ 
        owner           varchar2(30),
        table_name      varchar2(30),
        column_name     varchar2(30),
        text            varchar2(500), /* Description */
        boot            number
);
create index i_BADDATA$$ on BADDATA$$(
        item, owner, table_name, column_name
);

REM =====================================================================
REM Delete pre-existing BOOT data from this script
REM =====================================================================
REM
delete from BADDATA$$ where boot=1;

REM =====================================================================
REM Insert some known special tables / columns into BADDATA$$
REM =====================================================================
REM
insert into BADDATA$$ values 
  ('OWNER','SYS',null,null,'Dont check SYS on DB check',1 );
insert into BADDATA$$ values 
  ('TABLE','SYS','BADDATA$$',null,'Dont check myself',1 );
insert into BADDATA$$ values 
  ('TABLE','SYS','SUMDEP$',null,'P_REF_TIME contains 00-000-00 dates',1);
insert into BADDATA$$ values 
  ('TABLE','SYS','SUMDETAIL$',null,'SPARE4 contains 00-000-00 dates',1);
insert into BADDATA$$ values 
  ('COLUMN','SYS','USER$','PTIME','PTIME contains 00-000-00 dates',1);
insert into BADDATA$$ values 
  ('COLUMN','SYS','USER$','EXPTIME','EXPTIME contains 00-000-00 dates',1);
insert into BADDATA$$ values 
  ('COLUMN','SYS','USER$','LTIME','LTIME contains 00-000-00 dates',1);
commit;

REM 
REM =====================================================================
REM BadData Package
REM =====================================================================
REM
REM
CREATE OR REPLACE PACKAGE BadData
AS
 --
  PROCEDURE CheckDb( DoChars BOOLEAN default FALSE ) ;
  PROCEDURE CheckSchema( vowner varchar2, DoChars BOOLEAN default False ) ;
  PROCEDURE CheckObject( vowner varchar2, vtable_name varchar2,
         DoChars BOOLEAN default False ) ;
  PROCEDURE LastError;
  PROCEDURE Continue;
  PROCEDURE GetSQL( vOwner varchar2 , vTable varchar2 ) ;
  PROCEDURE Skip;
 --
END BadData;
/

REM =====================================================================
REM BadData Package Body
REM =====================================================================
REM
CREATE OR REPLACE PACKAGE BODY BadData
AS
 --
  DEBUG   boolean:=FALSE;               
  DoCharCols boolean:=FALSE; 
 --
 --
 --
  TYPE t_Col  IS TABLE OF VARCHAR2(40) INDEX BY BINARY_INTEGER;
  Col  t_Col;
  Typ  t_Col;
 --
  TYPE t_Num  IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  badCnt  t_Num;
  Mxl  t_Num;
 --
  NCols INTEGER;
 --
 -- Use these to save current location for error reporting
 --
  Command   varchar2(10);
  OnOwner   varchar2(30);
  OnTable   varchar2(30);
  OnColumn  varchar2(30);
  Pos       varchar2(255);
  Stmt      varchar2(32767);  -- Keep the core statement global for errors
  Frm       varchar2(32767);  -- Keep the FROM ... WHERE ... here
  Err       varchar2(32767);
 --
 -- =====================================================================
 -- PRIVATE Putlines(txt) 
 --   Writes a char string to DBMS_OUTPUT spliting lines as it goes
 --
  PROCEDURE putlines( txt varchar2 ) 
  IS
    p integer:=1;
    len integer;
    LineLen number:=80;
    pos integer;
  BEGIN
    len:=length(txt);
    while (p<len) loop
      pos:=instr(substr(txt,p,LineLen),chr(10));
      if pos<LineLen and pos>0 then
        dbms_output.put_line(substr(txt,p,pos-1));
        p:=p+pos;
      else
        dbms_output.put_line(substr(txt,p,LineLen));
        p:=p+LineLen;
      end if;
    end loop;
  END;
 --
 -- =====================================================================
  PROCEDURE Intro 
  IS 
  BEGIN
   putlines('The output below shows SCHEMA,TABLE and COLUMN name for columns');
   putlines('which appear to contain corrupt data.');
   putlines('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
   dbms_output.new_line;
  END;
 -- =====================================================================
 -- PRIVATE BuildStmt( owner, table, star )
 --  Builds a SQL statement to scan OWNER.TABLE.
 --  If RID is TRUE all rows are selected, otherwise just the rows
 --  we are interested in. 
 --
  PROCEDURE BuildStmt( vowner varchar2, vtable_name varchar2, rid boolean ) 
  IS
    sep  varchar2(10);
    SM   varchar2(10):='SUM';
   --
    CURSOR C_ChkCols( vowner varchar2, vtable_name varchar2) IS
      SELECT rownum, column_name, data_length, data_type
       FROM dba_tab_columns T
      WHERE owner=vowner and table_name=vtable_name
        and data_type in ('DATE','NUMBER','CHAR','VARCHAR2')
        and NOT EXISTS 
                ( select 1 from BADDATA$$ C
                   where c.item='COLUMN'
                     and c.owner=t.owner
                     and c.table_name=t.table_name
                     and c.column_name=t.column_name
                )
    ;
   --
  BEGIN
    Pos:='BuildStmt: Begin';
    stmt:='SELECT ';
   --
    if rid THEN
      stmt:=stmt||'TAB.rowid '||chr(10);
      SM:=' ';
    else
      stmt:=stmt||'count(*) '||chr(10);
    end if;
   --
    Pos:='BuildStmt: Build Select';
    NCols:=0;
    FOR R in C_ChkCols(vowner,vtable_name)
    LOOP
     IF (R.data_type='DATE' OR R.data_type='NUMBER' or DoCharCols) THEN
      NCols:=Ncols+1;
      OnColumn:=R.column_name;
      Col(NCols):=R.column_name;
      Mxl(Ncols):=R.data_length;
      Typ(Ncols):=R.data_type;
      IF rid THEN
        stmt:=stmt||',dump("'||R.column_name||'") '|| chr(10);
      ELSE
        badCnt(NCols):=null;
 if (R.data_type = 'DATE') then
          stmt:=stmt||','||SM||'(decode("'||R.column_name||
  '","'||R.column_name||'"+0,0,1))'||chr(10);
 elsif (R.data_type = 'NUMBER') then
          stmt:=stmt||','||SM||'(decode("'||R.column_name||
  '","'||R.column_name||'"/1,0,1))'||chr(10);
 else
          stmt:=stmt||','||SM||'(decode(sign('||R.data_length||
  '- nvl(length("'||R.column_name||'"),0)),-1,1,0))'||chr(10);
 end if;
      END IF;
     END IF;
    END LOOP;
    IF (NCols<=0) THEN
        return;
    END IF;
    frm:=' FROM "'||vowner||'"."'||vtable_name||'" TAB WHERE (';
    sep:=' ';
    Pos:='BuildStmt: Build WHERE';
    FOR n IN 1 .. NCols
    LOOP
      OnColumn:=Col(n);
      if (Typ(n) = 'DATE') then
        frm:=frm||chr(10)||sep||'("'||Col(n)||'"!="'||Col(n)||'"+0)';
      elsif (Typ(n) = 'NUMBER') then
        frm:=frm||chr(10)||sep||'("'||Col(n)||'"!="'||Col(n)||'"/1)';
      else
        frm:=frm||chr(10)||sep||'(nvl(length("'||Col(n)||'"),0)>'||Mxl(n)||')';
      end if;
      sep:=' OR ';
    END LOOP;
    frm:=frm||' )';
   --
    stmt:=stmt||frm;
  END;
 --
 -- =====================================================================
 -- PRIVATE TableOK( owner, table )
 --  Internal function to check a named TABLE for suspicious data
 --
  FUNCTION TableOK( vowner varchar2, vtable_name varchar2) RETURN Boolean
  IS
    n    integer;
    rows integer;
    cur  integer;
    ok   boolean;
    cnt  number;
   --
  BEGIN
    IF debug then
       dbms_output.put_line('Checking '||vowner||'.'||vtable_name);
    end if;
   -- 
   -- WARNING:  The line below must be commented out in Oracle7.1
   --           as DBMS_APPLICATION_INFO does not exist until 7.2
   --
    DBMS_APPLICATION_INFO.Set_module('BadData: '||vowner,vtable_name);
   --
    OnOwner:=vowner;
    OnTable:=vtable_name;
    OnColumn:=null;
   --
    BuildStmt( vowner,vtable_name,false );
   --
    IF (NCols=0) THEN
        dbms_output.put_line(' -- Object '||vowner||'.'||vtable_name|| ' not found !!!');
        return(FALSE);
    END IF;
   --
    IF debug then putlines(stmt); end if;
   --
    Pos:='TableOK: Open Cursor';
    OnColumn:=null;
    cur:= DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur,stmt, dbms_sql.native);
    DBMS_SQL.DEFINE_COLUMN(cur,1,cnt);
    FOR n in 1 .. NCols
    LOOP
     DBMS_SQL.DEFINE_COLUMN(cur,n+1,badCnt(n));
    END LOOP;
   --
    Pos:='TableOK: Execute';
    rows:= DBMS_SQL.EXECUTE_AND_FETCH(cur);
   --
    ok:=TRUE;
    IF rows > 0 THEN
     Pos:='TableOK: Check Result';
     DBMS_SQL.COLUMN_VALUE(cur,1,cnt);
     IF (cnt>0) THEN
      DBMS_OUTPUT.PUT_LINE('REM '||vowner||'.'||vtable_name||' has '||cnt||
        ' suspect rows');
      FOR n in 1 .. NCols
      LOOP
        OnColumn:=Col(n);
        DBMS_SQL.COLUMN_VALUE(cur,n+1,badCnt(n));
        if (badCnt(n)>0) then
          DBMS_OUTPUT.PUT('rem Column '||Col(n));
          DBMS_OUTPUT.PUT_LINE(' has '||badCnt(n)||' Bad '||Typ(n));
          ok:=false;
          dbms_output.new_line;
        end if;
       END LOOP;
       if (not OK) then
            DBMS_OUTPUT.PUT_LINE('REM ');
            DBMS_OUTPUT.PUT_LINE('REM Use this SQL to see the bad row/s:');
            dbms_output.new_line;
         GetSQL(vowner,vtable_name);
       end if;
       dbms_output.new_line;
     END IF;
     DBMS_SQL.CLOSE_CURSOR(cur);
     return(ok);
    END IF;
   --
    DBMS_OUTPUT.PUT_LINE(vowner||'.'||vtable_name||' ERROR '||sqlerrm);
    DBMS_SQL.CLOSE_CURSOR(cur);
    return(false);
  END;
 --
 -- =====================================================================
 -- PRIVATE SchemaOK( owner, table )
 --  Internal function to check a named SCHEMA for suspicious data
 --  If TABLE is specified this is the start point. If NULL all tables
 --  for this schema are scanned.
 --
  FUNCTION SchemaOK( vowner varchar2, vStartTable varchar2 default NULL ) 
  RETURN Boolean
  IS
    CURSOR C_Tables( vowner varchar2, vStartTable varchar2 ) IS
      SELECT table_name
       FROM dba_tables T
      WHERE owner=vowner
        and (vStartTable is null OR table_name>=vStartTable)
        and NOT EXISTS 
                ( select 1 from BADDATA$$ C
                   where c.item='TABLE'
                     and c.owner=t.owner
                     and c.table_name=t.table_name
                )
      order by table_name
    ;
   --
    ok  boolean:=TRUE;
    nCount number := 0;
  BEGIN
    Pos:='SchemaOK';
    OnOwner:=vowner;
    OnTable:=null;
    OnColumn:=null;
    Stmt:=null;
   --
    FOR T in C_Tables( vowner,vStartTable ) 
    LOOP
        IF NOT TableOK(vowner,T.table_name) THEN
          ok:=false;
        END IF;
        nCount := nCount + 1;
    END LOOP;
    IF nCount = 0 THEN
       dbms_output.put_line(' -- Owner '||vowner||' not found !!!');
       ok:=false;
    END IF;
    return(ok);
  END;
 --
 -- =====================================================================
 -- PRIVATE DbOK( owner, table )
 --  Internal function to check an entire database.
 --  Owner / Table specify a starting owner / table to use.
 --
  FUNCTION DbOK( vStartOwner varchar2 default null,
                 vStartTable varchar2 default null  ) RETURN Boolean
  IS
    CURSOR C_Users IS
      SELECT username FROM dba_users 
        WHERE (vStartOwner is null OR username>=vStartOwner)
        AND NOT EXISTS
                (select 1 from BADDATA$$
                  where item='OWNER' 
                    and owner=username)
        order by username
    ;
   --
    ok  boolean:=true;
  BEGIN
    Pos:='DbOK';
    OnOwner:=null;
    OnTable:=null;
OnColumn:=null;
    Stmt:=null;
   --
    FOR U in C_Users
    LOOP
        IF NOT SchemaOK(U.username,vStartTable) THEN
         ok:=false;
        END IF;
    END LOOP;
    return(ok);
  END;
 --
 -- =====================================================================
 -- PUBLIC FUNCTIONS
 -- =====================================================================
 -- PUBLIC Object( owner, table )
 --  External interface to check an OBJECT (OWNER.TABLE)
 --
  PROCEDURE OBJECT( vowner varchar2, vtable_name varchar2) 
  IS
  BEGIN
    Command:='OBJECT';
    if (TableOK(vowner,vtable_name)) then
        DBMS_OUTPUT.PUT_LINE('Table check for '||vowner||'.'||vtable_name||
                ' found no corrupt data');
    end if;
    Command:=null;
  EXCEPTION
    When others THEN
        Err:=sqlerrm;
        LastError;
        raise;
  END;
 --
 --  External interface to check a schema database
 --
  PROCEDURE CheckObject( vowner varchar2, vtable_name varchar2, 
                         DoChars BOOLEAN default FALSE ) 
  IS
  BEGIN
    DoCharCols:=DoChars;
    Object(vowner,vtable_name);
  END;
 --
 -- =====================================================================
 -- PUBLIC Schema( owner, table )
 --  External interface to check a SCHEMA (OWNER)
 --
  PROCEDURE Schema( vowner varchar2, vStartTable varchar2 default NULL ) 
  IS
  BEGIN
    Command:='SCHEMA';
    if (SchemaOK(vowner,vStartTable)) then
        DBMS_OUTPUT.PUT_LINE('Schema check for '||vowner||
                ' found no corrupt data');
    end if;
    Command:=null;
  EXCEPTION
    When others THEN
        Err:=sqlerrm;
        LastError;
        raise;
  END;
 --
 --  External interface to check a schema database
 --
  PROCEDURE CheckSchema( vowner varchar2, DoChars BOOLEAN default FALSE ) 
  IS
  BEGIN
    DoCharCols:=DoChars;
    Intro;
    Schema(vowner);
  END;
 --
 -- =====================================================================
  PROCEDURE Db ( vStartOwner varchar2 default null,
                 vStartTable varchar2 default null  ) 
  IS
  BEGIN
    Command:='DB';
    if (DbOK(vStartOwner,vStartTable)) then
        DBMS_OUTPUT.PUT_LINE('CheckDB found no corrupt data');
    end if;
    Command:=null;
  EXCEPTION
    When others THEN
        Err:=sqlerrm;
        LastError;
        raise;
  END;
 --
 --  External interface to check a complete database
 --
  PROCEDURE CheckDb( DoChars BOOLEAN default FALSE ) 
  IS
  BEGIN
    DoCharCols:=DoChars;
    Intro;
    DB;
  END;
 --
 -- =====================================================================
 -- PUBLIC Continue( )
 --  External interface to continue processing where we left off in 
 --  case of Object, Schema or DB checks
 --
  PROCEDURE Continue
  IS
    Tab varchar2(30);
    Own varchar2(30);
  BEGIN
    Tab:=OnTable;
    Own:=OnOwner;
    IF Command is not null THEN
      dbms_output.put_line('CONTINUING Check'||Command||
                ' from '||Own||'.'||Tab);
      dbms_output.new_line;
    ELSE
      dbms_output.put_line('Nothing to CONTINUE from...');
      return;
    END IF;
    IF Command='DB' THEN
        DB(Own,Tab);
    ELSE
      IF Command='SCHEMA' THEN
        Schema(Own,Tab);
      ELSE
        IF Command='OBJECT' THEN
          Object(Own,Tab);
        ELSE 
          dbms_output.put_line('Cannot CONTINUE '||Command);
        END IF;
      END IF;
    END IF;
  END;
 --
 -- =====================================================================
 -- PUBLIC GetSQL( owner, table )
 --  External interface to show an SQL statement which selects
 --  suspect rows from OWNER.TABLE
 --  
  PROCEDURE GetSQL( vOwner varchar2 , vTable varchar2 ) 
  IS
  BEGIN
    BuildStmt(vowner,vtable,true);
    putlines(stmt);
    dbms_output.put_line(';');
  END;
 --
 -- =====================================================================
 -- PUBLIC LastError
 --  Displays the last stored error from this package 
 --
  PROCEDURE LastError
  IS
  BEGIN
        dbms_output.new_line;
        dbms_output.put_line('Last BadData Error:');
        dbms_output.put_line(' Pos: '||Pos);
        dbms_output.put_line('  At: '||OnOwner||'.'||OnTable||'.'||OnColumn);
        dbms_output.put_line('Stmt:');
        putlines(Stmt);
        dbms_output.put_line('Error:');
        putlines(Err);
  END;
 --
 -- =====================================================================
 -- PUBLIC Skip
 --  If a BADDATA.CheckDB / SCHEMA / OBJECT errored then the object name
 --  we were on is marked for skipping over in the BADDATA$$ table.
 --
  PROCEDURE Skip
  IS
  BEGIN
    IF Command is not null THEN
      dbms_output.put_line('Inserting '||OnOwner||'.'||OnTable||
        ' into BADDATA$$ to skip it' );
      insert into BADDATA$$ values 
       ('TABLE',OnOwner,OnTable,null,'User request to SKIP this',0 );
      COMMIT;
    ELSE
      dbms_output.put_line('No error occured to SKIP');
    END IF;
   END;
 --
 -- =====================================================================
 --
BEGIN
  -- Initialize DBMS_OUTPUT
  dbms_output.enable(1000000);
END;
/

REM =====================================================================
REM END of BADDATA.SQL
REM =====================================================================


exec baddata.checkobject('SCOTT','EMP');

Exemplo:








Após a criação da estrutura, vamos executar o script para validação, lembrando que existem algumas formas para executar o script.
Exemplos:
- Para validação do banco completo, devemos usar:  BadData.CheckDB( DoChars=>TRUE );
Obs.: Essa é a opção mais demorada, pois a mesma validará todas as tabelas do banco de dados.




- Para validação de um schema específico, devemos usar: BadData.CheckSchema('SCHEMA, DoChars=>TRUE);.





- Para validação de uma tabela específica: 

BadData.CheckObject('SCHEMA','TABLE_NAME',DoChars=>TRUE);.




No meu caso vou usar o exemplo para a tabela específica:











Este erro ocorre devido a existência de colunas com dados que possuam mais caracteres do que o limite da coluna. Por exemplo uma coluna com 15 caracteres, sendo que o campo esta definido com CHAR(6) .

Realizando um teste simples na tabela em questão e o problema mencionado acima se confirma:


SQL> create table schema.teste as select * from schema.table_name;

create table schema.teste as select * from schema.table_name

*

ERROR at line 1:

ORA-12899: valor muito grande para a coluna ??? (real: 15, mßximo: 6)




SQL>

Identificado o problema e as linhas com corrupção, será necessário corrigi-las. Posteriormente a coleta de estatísticas executará com sucesso.

Erro RMAN-03002 RMAN-06171 - Duplicate rman sem target database

2019-02-28T09:41:00.000-08:00

Olá Pessoal

Ao iniciar o duplicate de um banco de dados utilizando um backup físico, podemos receber a seguinte mensagem de erro se não ajustar corretamente o comando de restore:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of allocate command at 28/02/2019 09:10:21
RMAN-06171: not connected to target database
Solução: Para corrigir o problema, você deve ajustar o seu comando de restore onde for:

allocate channel c1 device type disk;

Para:

allocate auxiliary channel c1 device type disk;

E execute o duplicate novamente.

Boa sorte!

Problema na Instalação do Oracle 11g 64 bits - Windows 64 (WFMLRSVCApp.ear)

2018-09-19T10:09:00.002-07:00

Olá pessoal.

Durante a instanação do Oracle 11g 64 bits no Windows Server 64 bits, recebi a seguinte mensagem de alerta:

Porém, consegui resolver o problema com facilidade executando apenas o seguinte procedimento:

Como a instalação do Oracle em Windows tem duas pastas para descompactar, as vezes esse erro ocorre pois o seu usuário do Sistema Operacional não tem permissão de administrador.
Para resolver o problema, pasta copiar o conteúdo da pastas 2 do instalador do Oracle, no meu caso era "win64_11gR2_database_2of2" e colar o conteúdo dessa pasta na pasta 1 do instalador do Oracle, no meu caso a pasta 1 está nomeada como "win64_11gR2_database_1of2".
Feito isso, iniciei a instalação do Software Oracle novamente que deve sanar o problema.

Qualquer dúvida, deixe seu comentário.
Abraço.

Bug (Doc ID 339657.1) Instalação do Software Oracle do 10g - Error in writing to directory /tmp

2018-03-07T06:54:00.000-08:00

Olá.
Recentemente precisei realizar a instalação de um Oracle 10G em um RHEL5.11 64Bits, porém, o servidor possuía um disco com capacidade maior de 2TB e isso gera um BUG na versão 10G do Oracle.
Iniciei a instalação do software Oracle e recebi o seguinte alerta:

[oracle@producao]/u01/orainstall/database> ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-03-06_03-41-21PM. Please wait ...
Error in writing to directory /tmp/OraInstall2018-03-06_03-41-21PM. Please ensure that this directory is writable and has atleast 60 MB of disk space. Installation cannot continue.
: No such file or directory

Pesquisando sobre o problema no metalink identifiquei a seguinte nota: (Doc ID 339657.1) que explica que o Oracle 10G não consegui interpretar um disco com capacidade de armazenamento superior a 2TB.
Nessa nota também recomenda a configuração de uma area de Swap maior que 2TB para contornar o problema, porém, não resolveu.

Uma solução de contorno que ajudou nesse caso foi consumir o espaço disponível deixando apenas 2TB disponível, segue abaixo algumas informações de como fazer isso:

1 - Primeiro vamos gerar informações em um arquivo nesse disco, para consumir o espaço disponível e posteriormente realizarmos a instalação do Software Oracle, segue exemplo abaixo de como criar esse arquivo:

[oracle@producao]~> dd if=/dev/zero of=fArquivo1 bs=1048576 count=100000

O exemplo acima, irá criar um arquivo de 100GB chamado "Arquivo1" no filesystem com capacidade acima de 2TB.
ATENÇÃO: No meu caso precisei criar 20 arquivos, pois, o disco possuia 4TB. Veja qual a sua necessidade e crie apenas a quantidade de arquivos que precisa!

2 - Após criar os arquivos e ocupar o espaço deixando apenas 2TB disponível, certifique que o espaço fisico disponível no filesystem está conforme o esperado:

[oracle@producao]~> df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
4.0T 2.1T 1.8T 51% /
/dev/sda1 97M 24M 68M 27% /boot
tmpfs 16G 0 16G 0% /dev/shm

3 - Posteriormente inicie a instalação do Oracle 10G novamente!

[oracle@producao]/u01/orainstall/database> ./runInstaller

Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2

Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-03-07_12-47-12PM. Please wait ...[oracle@hsmedproducao]/u01/orainstall/database>

Pronto, feito isso, poderá prosseguir com a instalação normalmente.
Espero ter ajudado, qualquer dúvida, deixe seu comentário.

ORA-01792 maximum number of columns in a table or view is 1000

2018-02-07T08:32:00.001-08:00

Olá pessoal.
Recentemente me deparei com o erro ORA-01792 no banco de dados Oracle 12c.
O erro diz que a tabela ou view possui mais de 1000 colunas. A Oracle não recomenda tabela com mais de 1000 colunas e por esse motivo também informa mesmo atualizando a versão do Oracle o problema pode não ser resolvido, conforme nota do Metalink ( 19509982.8).
Esse erro está relacionado ao seguinte BUG (Bug 19509982 Disable raising of ORA-1792 by default).
Como solução de contorno a Oracle disponibilizou nessa nota para alterar um parâmetro oculto do Oracle. conforme mensagem abaixo:

Workaround
 The preference is to alter the query and/or view definitions to avoid 
 the error. However in cases where the SQL cannot be adjusted then the
 checking can be disabled by setting "_fix_control" to "17376322:OFF".

O parametro oculto deve ser alterado apenas em ambiente Enterprise, em todo caso, como a Oracle disponibilizou a nota e deu essa opção como solução contorno, temos a evidência e podemos altera-lo.
Segue comando abaixo:

alter system set "_fix_control"='17376322:OFF' scope=both;

Obs.: Em todo caso, se a Oracle te procurar para falar da utilização do parametro, deve-se referenciar a nota do Metalink (DOC ID 19509982.8) justificando a utilização do mesmo.
Qualquer dúvida, deixe seu comentário.

Habilitando modo archivelog - Oracle 11g

2018-01-23T12:27:00.001-08:00

Olá,
hoje vamos habilitar o moto archivelog do banco de dados Oracle, para posteriormente executarmos o backup físico do ambiente através do utilitário RMAN, dessa forma, não necessita de parada do ambiente Oracle para execução do Backup.

Vamos a etapa de habilitação do modo archivelog do Oracle:

1 - Verificar se o Oracle esta no modo archivelog:

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0.4/dbteste/dbs/arch
Oldest online log sequence 18043
Current log sequence 18045

2 - Defina o Local para geração dos archivelog e ajuste o parametro log_archive_dest_1, ajuste também o nome do archivelog alterando o parametro log_archive_format:

SQL> alter system set log_archive_dest_1='LOCATION=/u01/oraarch/dbteste REOPEN=60' scope=both;

System altered.

SQL> alter system set log_archive_format='arc_%t_%s_%r.arch' scope=spfile;

System altered.

3 - Parar o ambiente de banco de dados Oracle (caso seja ambiente de produção, alinhe coma operação qual o melhor horário para executar essa atividade) :
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

4 - Montarr o banco de dados Oracle para habilitar o modo archivelog:
SQL> startup mount;
ORACLE instance started.

Total System Global Area 3089920000 bytes
Fixed Size 2232352 bytes
Variable Size 2097156064 bytes
Database Buffers 973078528 bytes
Redo Buffers 17453056 bytes
Database mounted.

5 - Habilitar o modo archivelog:

SQL> alter database archivelog;

Database altered.

6 - Abra o banco de dados Oracle novamente:

SQL> alter database open;

Database altered.

7 - Verifique se o modo archivelog foi habilitado com sucesso:

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0.4/dbteste/dbs/arch
Oldest online log sequence 18043
Next log sequence to archive 18045
Current log sequence 18045
SQL>

8 - Force a geração de um archivelog para posteriormente verificar se o mesmo foi gerado conforme esperado no diretório esperado:

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL>

Pronto, seu ambiente já esta no Modo archivelog.

Query KILL SESSION Oracle

2018-01-23T11:54:00.002-08:00

Olá Pessoal,
Algumas vezes deparamos com situações no banco de dados Oracle onde identificamos centenas de sessões de um determinado usuário executando o mesmo processo e travando o ambiente de banco de dados Oracle.
O Cliente desesperado com a situação solicita para encerrar a sessão e normalizar o ambiente o mais rápido possível.
Pensando nesse tipo de situação, criei uma query simples concatenando os valores referente a sessão do determinado usuário para posteriormente finalizar.

Segue abaixo query para realizar o KILL SESSION:

select ' alter system disconnect session '''||SID||','||SERIAL#||''' immediate; ' from v$session where username ='USUARIO';

ou

select ' alter system kill session '''||SID||','||SERIAL#||''' immediate; ' from v$session where username ='USUARIO'

Qualquer dúvida, deixe o seu comentário!

Oracle 10g - Mover banco de dados de ASM para filesystem

2017-09-21T13:30:00.002-07:00

Olá,
Hoje vamos fazer um teste em um ambiente Oracle XE para mover o banco de dados do ASM para FileSystem.
Primeiramente vamos garantir as seguintes informações:
Baixar o banco de dados e o listener do servidor.
Iniciar apenas o banco de dados Oracle e manter o Listener baixado, para garantir que nenhum usuário irá conectar no ambiente no momento das atividades.
Verifique as tablespaces temporárias no seu ambiente de banco de dados e elimine-as do ambiente, ou seja, podemos dropar as temporárias.

Feito isso, vamos ao passo a passo das atividades:
1 - Criar tablespace temporária no Filesystem, conforme exemplo abaixo:
SQL> alter tablespace TEMP add tempfile '/mnt/backup/oracle/xe/temp_01.dbf' size 128m autoextend on next 64m maxsize 4096m;

Tablespace altered.

2 - Mover os controlfiles para o Filesystem:
2.1 Baixe o banco de dados novamente com o comadno:
SQL> shutdown immediate

2.2 Apenas monte o banco de dados conforme comando abaixo:
SQL> startup mount

2.3 Configure o parametro do controlfile no filesystem, conforme comando abaixo:
SQL> alter system set control_files='/mnt/backup/oracle/xe/control01.ctl' scope=spfile;

2.4 Baixe o banco de dados novamente:
SQL> shutdown immediate

2.5 Apenas monte o banco de dados com o comando:
SQL> startup mount

2.6 Restaurar o Controlfile via rman, segue exemplo abaixo:
rman target /
RESTORE CONTROLFILE FROM '+DATA/xe/controlfile/current.256.745338209';
alter database mount;

3 - Verifique os Datafiles do seu ambiente de banco de dados:

SQL> select file#, name from v$datafile;

FILE# NAME
---------- ------------------------------------------------
1 +DATA/xe/datafile/system.260.745338219
2 +DATA/xe/datafile/undotbs1.261.745338231
3 +DATA/xe/datafile/sysaux.262.745338237
4 +DATA/xe/datafile/users.264.745338243
5 +DATA/xe/datafile/rm_dados.266.745340513
6 +DATA/xe/datafile/rm_dados2.166.74534051
7 +DATA/xe/datafile/rm_indices.268.745340545
8 +DATA/xe/datafile/rm_dados_ff.269.745340559

8 rows selected.

4 - Realize a cópia dos datafiles do seu ambiente de banco de dados para o Filesystem do servidor:

RMAN> BACKUP AS COPY DATAFILE 1 FORMAT '/mnt/backup/oracle/xe/system01.dbf';
RMAN> BACKUP AS COPY DATAFILE 2 FORMAT '/mnt/backup/oracle/xe/undotbs1.dbf';
RMAN> BACKUP AS COPY DATAFILE 3 FORMAT '/mnt/backup/oracle/xe/sysaux.dbf';
RMAN> BACKUP AS COPY DATAFILE 4 FORMAT '/mnt/backup/oracle/xe/users.dbf';
RMAN> BACKUP AS COPY DATAFILE 5 FORMAT '/mnt/backup/oracle/xe/rm_dados.dbf';
RMAN> BACKUP AS COPY DATAFILE 6 FORMAT '/mnt/backup/oracle/xe/rm_dados2.dbf';
RMAN> BACKUP AS COPY DATAFILE 7 FORMAT '/mnt/backup/oracle/xe/rm_indices.dbf';
RMAN> BACKUP AS COPY DATAFILE 8 FORMAT '/mnt/backup/oracle/xe/rm_dados_ff.dbf';

5 - Execute o Switch para alterar os datafiles e utilizar a cópia que acabamos de gerar:

RMAN> switch datafile 1 to copy;
RMAN> switch datafile 2 to copy;
RMAN> switch datafile 3 to copy;
RMAN> switch datafile 4 to copy;
RMAN> switch datafile 5 to copy;
RMAN> switch datafile 6 to copy;
RMAN> switch datafile 7 to copy;
RMAN> switch datafile 8 to copy;

6 - Verifiquei se os datafiles foram alterados e estão no diretório correto:

SQL> select name from v$datafile;

NAME
----------------------------------------------------
/mnt/backup/oracle/xe/system01.dbf
/mnt/backup/oracle/xe/undotbs1.dbf
/mnt/backup/oracle/xe/sysaux.dbf
/mnt/backup/oracle/xe/users.dbf
/mnt/backup/oracle/xe/rm_dados.dbf
/mnt/backup/oracle/xe/rm_dados2.dbf
/mnt/backup/oracle/xe/rm_indices.dbf
/mnt/backup/oracle/xe/rm_dados_ff.dbf

8 rows selected.

7 - Abra o banco de dados com o seguinte comando:
SQL> alter database open;

Database altered.

Pronto, feito isso o seu banco de dados está completamente no filesystem, apenas lembre de recriar os redologs e também mudar o diretório dos archivelog.
Espero que tenha ajudado, qualquer dúvida, deixe o seu comentário.

MySQL - Ativar geração de LOG

2017-09-21T08:01:00.001-07:00

Olá.
Hoje vou mostrar como ativar o LOG do MySQL para apoiar na verificação de erros e alertas.
Segue abaixo o passo a passo:

-- Primeiramente vamos conectar no ambiente de banco de dados MySQL:
root@serverexemplo:/var/run/mysqld#> mysql -u root --password=********

-- Verificar bancos de dados:
mysql> show databases;
+-------------------------+
| Database |
+-------------------------+
| information_schema |
| adm |
| prod |
| mysql |
| performance_schema |
| test |
+--------------------------+
9 rows in set (0.00 sec)

-- Conectar no banco de dados PROD:
mysql> use prod

-- Verificar geração de LOG do ambiente de banco de dados PROD :
mysql> SHOW VARIABLES LIKE "general_log%";
+--------------------+---------------------------------+
| Variable_name | Value |
+--------------------+---------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/fsvrlx06.log |
+------------------+-----------------------------------+

-- Habilitar geração de LOG para apoiar análise do problema:
mysql> SET GLOBAL general_log = 'ON';
mysql> SHOW VARIABLES LIKE "general_log%";
+--------------------+---------------------------------+
| Variable_name | Value |
+--------------------+---------------------------------+
| general_log | ON |
| general_log_file | /var/lib/mysql/fsvrlx06.log |
+------------------+-----------------------------------+

Pronto, feito isso, através do sistema operacional você poderá verificar o arquivo "/var/lib/mysql/fsvrlx06.log". O mesmo possui informações referente ao banco de dados MySQL sobre erros e alertas e é bastante utilizado para apoiar em uma análise do banco de dados.

Qualquer dúvida, deixe seu comentário.
Abraço.

MySQL - Data truncation: Incorrect datetime value

2017-09-21T07:29:00.002-07:00

Verificação do erro com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'COLUMN_NAME' at row 1.

O erro é gerado ao inserir informações em uma determinada tabela do banco de dados MySQL, cujo o campo da Coluna não aceita o valor que está sendo inserido.
Vejamos os exemplos de como resolver o problema:

-- Primeiramente vamos conectar no ambiente de banco de dados MySQL:

root@serverexemplo:/var/run/mysqld#> mysql -u root --password=********

-- Verificar bancos de dados:
mysql> show databases;
+-------------------------+
| Database |
+-------------------------+
| information_schema |
| adm |
| prod |
| mysql |
| performance_schema |
| test |
+--------------------------+
9 rows in set (0.00 sec)

-- Conectar no banco de dados PROD:
mysql> use prod

-- Verificar versão do banco de dados PROD:
mysql> SHOW VARIABLES LIKE "%version%";
+--------------------------------+---------------------------------------------+
| Variable_name | Value |
+--------------------------------+---------------------------------------------+
| innodb_version | 5.6.36 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.36 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+--------------------------------+---------------------------------------------+
7 rows in set (0.01 sec)

-- Verificar geração de LOG do ambiente de banco de dados PROD :
mysql> SHOW VARIABLES LIKE "general_log%";
+--------------------+---------------------------------+
| Variable_name | Value |
+--------------------+---------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/fsvrlx06.log |
+------------------+-----------------------------------+

-- Habilitar geração de LOG para apoiar análise do problema:
mysql> SET GLOBAL general_log = 'ON';
mysql> SHOW VARIABLES LIKE "general_log%";
+--------------------+---------------------------------+
| Variable_name | Value |
+--------------------+---------------------------------+
| general_log | ON |
| general_log_file | /var/lib/mysql/fsvrlx06.log |
+------------------+-----------------------------------+

-- Criação de tabela de Backup para testes:
mysql> create table TABLE_EXAMPLO as SELECT * FROM TABLE_EXAMPLE_BACKUP;
Query OK, 2163 rows affected (1.12 sec)
Records: 2163 Duplicates: 0 Warnings: 0

-- Validar Descrição da tabela de Backup Criada:
mysql> desc TABLE_EXAMPLE_BACKUP;
+---------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+-------+
| PROJECT_ID | int(11) | NO | | NULL | |
| PROJECT_NAME | varchar(255)| NO | | NULL | |
| PROJECT_DESCRIPTION | varchar(255) | NO| | NULL | |
| PROJECT_TYPE | int(11) | NO | | NULL | |
| USER_ID | varchar(36) | NO | | NULL | |
| GROUP_ID | varchar(36) | NO | | NULL | |
| MANAGERGROUP_ID | varchar(36) | NO| | NULL | |
| TASK_ID | int(11) | NO | | NULL | |
| DATE_CREATED | bigint(20) | NO | | NULL | |
| PUBLISH_TAG | int(11) | NO | | NULL | |
| PROJECT_PUBLISHDATE| datetime | YES | | NULL | |
| PROJECT_PUBLISHED_BY | varchar(36) | NO | | NULL | |
| PROJECT_PUBLISHED_BY_NAME | varchar(255) | YES||NULL||
| USER_NAME | varchar(128) | YES | | NULL | |
| GROUP_NAME | varchar(128) | YES | | NULL | |
| MANAGERGROUP_NAME | varchar(128) | YES|| NULL | |
+---------------------------+--------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

-- Execução de testes na Tabela nova para Simulação do ERRO:
mysql> INSERT INTO TABLE_EXEMPLO_BACKUP (PUBLISH_TAG,PROJECT_ID,PROJECT_NAME,PROJECT_PUBLISHDATE,PROJECT_PUBLISHED_BY,PROJECT_PUBLISHED_BY_NAME,USER_ID,USER_NAME,GROUP_ID,GROUP_NAME,MANAGERGROUP_ID,MANAGERGROUP_NAME,PROJECT_DESCRIPTION,DATE_CREATED,PROJECT_TYPE,TASK_ID) VALUES (2163,4,'Offline','2017-09-21 15:49:TT','123-abc-1234-abcd','Admin ','123-abc-1234-abcd','Admin ','123-abc-1234-abcd','Users','123-abc-1234-abcd','Projectmanagers','The Offline Project',11111111111111,0,4);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> show warnings;
+-----------+-------+-------------------------------------------------------------------------------+
| Level | Code | Message |
+-----------+-------+-------------------------------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'PROJECT_PUBLISHDATE' at row 1 |
+-----------+-------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#### SOLUÇÃO
Como podemos observar nos testes acima, o erro ocorre na coluna PROJECT_PUBLISHDATE, portanto, vamos alterar o tipo do campo da COLUNA para o datatype aceitável, conforme abaixo:

Alteração do Datatype da coluna PROJECT_PUBLISHDATE de DATETIME para BIGINT

ALTER TABLE NEW_CMS_BACKUP_PROJECTS MODIFY PROJECT_PUBLISHDATE BIGINT;

-- Validar Descrição da tabela de Backup após alteração:
mysql> desc TABLE_EXAMPLE_BACKUP;
+---------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+-------+
| PROJECT_ID | int(11) | NO | | NULL | |
| PROJECT_NAME | varchar(255)| NO | | NULL | |
| PROJECT_DESCRIPTION | varchar(255) | NO| | NULL | |
| PROJECT_TYPE | int(11) | NO | | NULL | |
| USER_ID | varchar(36) | NO | | NULL | |
| GROUP_ID | varchar(36) | NO | | NULL | |
| MANAGERGROUP_ID | varchar(36) | NO| | NULL | |
| TASK_ID | int(11) | NO | | NULL | |
| DATE_CREATED | bigint(20) | NO | | NULL | |
| PUBLISH_TAG | int(11) | NO | | NULL | |
| PROJECT_PUBLISHDATE|bigint(20)| YES | | NULL | |
| PROJECT_PUBLISHED_BY | varchar(36) | NO | | NULL | |
| PROJECT_PUBLISHED_BY_NAME | varchar(255) | YES||NULL||
| USER_NAME | varchar(128) | YES | | NULL | |
| GROUP_NAME | varchar(128) | YES | | NULL | |
| MANAGERGROUP_NAME | varchar(128) | YES|| NULL | |
+---------------------------+--------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

-- Executar o insert novamente na tabela após o ajuste:
mysql> INSERT INTO TABLE_EXEMPLO_BACKUP (PUBLISH_TAG,PROJECT_ID,PROJECT_NAME,PROJECT_PUBLISHDATE,PROJECT_PUBLISHED_BY,PROJECT_PUBLISHED_BY_NAME,USER_ID,USER_NAME,GROUP_ID,GROUP_NAME,MANAGERGROUP_ID,MANAGERGROUP_NAME,PROJECT_DESCRIPTION,DATE_CREATED,PROJECT_TYPE,TASK_ID) VALUES (2163,4,'Offline','2017-09-21 15:49:TT','123-abc-1234-abcd','Admin ','123-abc-1234-abcd','Admin ','123-abc-1234-abcd','Users','123-abc-1234-abcd','Projectmanagers','The Offline Project',11111111111111,0,4);
Query OK, 1 row affected (0.00 sec)

Pronto, insert realizado com sucesso.
Agora só realizar o mesmo ajuste na tabela definitiva.

Espero que tenha ajudado.
Qualquer dúvida, deixe seu comentário.

Abraço.

Mover tabela AUD$ da tablespace SYSTEM + ORA-46267

2017-06-20T08:06:00.001-07:00

Olá pessoal.
Se você procurou por esse procedimento, provavelmente já sabe que não podemos redimensionar a tablespace SYSTEM.
Nessa tablespace podemos adicionar novos datafiles, porém, a mesma irá crescer e não será possível reduzir esse espaço que alocado.
Mas o foco desse post é falarmos sobre a tabela AUD$ que fica na tablespace SYSTEM. Quando configuramos a auditoria de alguma tabela do banco de dados, as informações são "populadas" na tabela AUD$ e para que a tabela não cresça muito e ocupe o espaço da tablespace SYSTEM, faremos o move da tabela AUD$ para outra tablespace, para isso, siga o procedimento abaixo:

1 - Verificar tablespace da tabela AUD$.

SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name ='AUD$';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ SYSTEM

2 - Criar uma nova tablespace de auditoria para mover a tabela AUD$.

SQL> create tablespace auditoria datafile '+DGDATAFILES' SIZE 128M autoextend on next 128M maxsize 25600m
extent management LOCAL autoallocate segment space management AUTO
LOGGING;

Tablespace criado.

3 - Verificar tamanho da tabela AUD$.
SQL> select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name ='AUD$';

SEGMENT_NAME SIZE_IN_MEGABYTES
--------------------------------------------------------------------------------- -----------------
AUD$ 20080

Obs.: Nesse caso temos uma "pegadinha" do Oracle. Como podemos observar a tabela possui 20GB e como criei a tablespace com SIZE 128M e autoextend de 128M até crescimento total de 25G, será exibido o seguinte erro:

ORA-46267: Espaco insuficiente no tablespace 'AUDITORIA', n?o e possivel concluir a operac?o

Pois, o Oracle não consegue copiar toda a tabela de uma só vez com o size 128M, para corrigir o problema, você precisa fazer um resize do datafile da seguinte forma:

alter database datafile '+DGDATAFILES/oracleteste/datafile/auditoria.335.946922105' resize 25g;

4 - Mover a tabela AUD$ para tablespace AUDITORIA:

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

audit_trail_location_value => 'AUDITORIA');

END;

/

Obs.: No meu caso, demorou 32 minutos devido o tamanho da tabela AUD$.

5 - Verificar se a tabela foi movida.

SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name ='AUD$';

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

AUD$ AUDITORIA

6 - Verificar tamanho da tabela movida.

SQL> select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name ='AUD$';

SEGMENT_NAME SIZE_IN_MEGABYTES

------------------------------ -----------------

AUD$ 20280

Espero ter ajudado, qualquer dúvida, deixe o seu comentário.
Um abraço.

ORA-09925: Unable to create audit trail file - Resolvido

2017-02-27T06:36:00.002-08:00

Olá pessoal,
Segue abaixo procedimento para resolver o erro ORA-09925.

O erro normalmente ocorre ao conectar no banco de dados com o usuário ADMIN, "SYSDBA".

Mensagem de erro completa do erro:

ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 28: No space left on device
Additional information: 9925
ORA-01075: you are currently logged on

Causa:
Esse problema ocorre, pois o sistema operacional atingiu o limite máximo de arquivos no diretório.
Esse problema pode ser identificado através do seguinte comando:

[oracle@srvteste1 ]$ df -hi
Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/mapper/vg_srvteste1-lv_root
3.2M 95K 3.1M 3% /
tmpfs 7.9M 274 7.9M 1% /dev/shm
/dev/mapper/mpathap2 126K 22 125K 1% /boot
/dev/mapper/mpathap1 0 0 0 - /boot/efi
/dev/mapper/vg_srvteste1-lv_home
13M 58K 13M 1% /home
/dev/mapper/oraclevg-lv_u01
3.2M 3.2M 7.0K 100% /u01
/dev/mapper/bkpvg-lv_bkp
94M 14K 94M 1% /manobras

Solução:
Para resolver o problema, basta efetuar a limpeza dos arquivos de auditoria e trace do banco de dados.
Segue abaixo comandos para realizar a limpeza dos arquivos:

find /u01/app/oracle/diag/ -mtime +7 -name "*.trc" -exec rm -f {} \;
find /u01/app/oracle/diag/ -mtime +7 -name "*.trm" -exec rm -f {} \;
find /u01/app/oracle/diag/ -mtime +7 -name "*.aud" -exec rm -f {} \;
find /u01/app/oracle/admin/ -mtime +7 -name "*.aud" -exec rm -f {} \;
find /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/audit/ -mtime +7 -name "*.aud" -exec rm -f {} \;

Após realizar a limpeza dos arquivos de auditoria e trace, realize um novo teste de conexão com o banco de dados.

Obs.: Procedimento utilizando em ambiente Oracle 11gR2

Qualquer dúvida, deixe o seu comentário.
Abraço.

Limpar memória de Swap rápido - Linux

2016-09-01T12:53:00.000-07:00

Olá pessoal,
recentemente tive um problema referente ao consumo de Swap em um servidor de produção. Porém, como o ambiente estava bastante lento, não conseguia trabalhar, o comando abaixo nos ajudará a liberar a memória a locada e nos dar um folego até identificar a causa raiz do problema.

Vamos a prática...

1 - Verificar consumo de memória RAM do servidor

[root@servidor ~]# free -m
total used free shared buffers cached
Mem: 7993 7992 80 0 37 6979
-/+ buffers/cache: 895 7098
Swap: 8503 1259 7244

Como podemos notar no resultado acima, o servidor está fazendo 1.2GB de Swap.
Para ganharmos um folego, limpe o cache do sistema operacional.

2 - Execute o comando para limpar o cache do sistema operacional:

[root@servidor ~]# free && sync && echo 3 > /proc/sys/vm/drop_caches && free
total used free shared buffers cached
Mem: 8184988 8104404 80584 0 38852 7148904
-/+ buffers/cache: 916648 7268340
Swap: 8707220 1260744 7446476
total used free shared buffers cached
Mem: 8184988 2299344 5885644 0 704 1508540
-/+ buffers/cache: 790100 7394888
Swap: 8707220 1260744 7446476

3 - Posteriormente verifique o consumo de memória RAM do servidor novamente:

[root@servidor ~]# free -m
total used free shared buffers cached
Mem: 7993 2252 5740 0 1 1480
-/+ buffers/cache: 771 7221
Swap: 8503 59 8443

Como podemos notar, após as atividades realizadas acima, o servidor liberou a Swap e baixou o consumo total da memória RAM, para apenas 2.2GB

Espero que tenha ajudado.
Qualquer dúvida, deixe seu comentário.

Alterar reserva de espaço do usuário root - Linux

2016-09-01T06:32:00.002-07:00

Olá pessoal,
Hoje vamos falar um pouco sobre um espaço que fica alocado para o usuário "root" do sistema operacional Linux. Esse espaço alocado para o usuário "root" normalmente é acima de 5% do espaço do disponível no filesystem. Porém, todo esse espaço não é mais necessário, visto que o objetivo desse espaço alocado é evitar que o sistema operacional trave, caso ocorra algum esgotamento de espaço físico do servidor.

No exemplo abaixo, deixei o espaço alocado em 1% para o usuário "root", equivalente a 2.9GB, ou seja, o suficiente para não travar o ambiente caso ocorra o esgotamento de espaço físico.

Então! Vamos a prática...

1 - Verificar espaço livre disponível:

[root@servidor ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 293G 272G 5.9G 98% /
tmpfs 4.0G 0 4.0G 0% /dev/shm
/dev/mapper/server
229G 133G 85G 62% /mnt

2 - Alterar a reserva do usuário "root" no diretório "/" para 1%.

[root@servidor ~]# tune2fs -m 1 /dev/sda1
tune2fs 1.39 (29-May-2006)
Setting reserved blocks percentage to 1% (790859 blocks)

3 - Verifique como ficou o espaço disponível:

[root@ser-002 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 293G 272G 18G 94% /
tmpfs 4.0G 0 4.0G 0% /dev/shm
/dev/mapper/server
229G 133G 85G 62% /mnt

Espero que tenha ajudado!

Obs.: nunca execute o comando com o valor "0" no lugar do "1", isso poderá causar problemas futuros no seu ambiente, deixando 0% de espaço reservado para o usuário "root" no diretório "/".

Qualquer dúvida, deixe seu comentário.
Abraço

Renomeando Owner / Schema no banco de dados Oracle

2016-06-29T08:39:00.002-07:00

Olá pessoal,
Existe algumas dúvidas sobre essa questão, mas eu digo que sim! é possível renomear um owner/schema no banco de dados Oracle.
Segue procedimento para renomear owner/schema do ambiente de banco de dados Oracle, esse procedimento foi realizado no banco de dados Oracle 12c.

Clique abaixo para ver o procedimento completo! Boa diversão!

1 - Verificar owner/schema a ser renomeado:
SQL> select user#,NAME from SYS.user$ WHERE NAME='TEST_RENAME';

USER# NAME
---------- ------------------------------
13980 TEST_RENAME

2、- Modificar o nome do owner/schema:
SQL> UPDATE USER$ SET NAME='TEST_NEW_NAME' WHERE USER#=13980;

3、- Confirmar modificação:
SQL> COMMIT;

4 - Modificar o SCN:
SQL> ALTER SYSTEM CHECKPOINT;

5、- Modificar a senha do novo owner/schema:
SQL> ALTER USER ... IDENTIFIED BY ...;

6、- Se aparecer o erro ORA-01918, execute o refresh da shared_pool
SQL> ALTER USER ... IDENTIFIED BY ...;
ORA-01918: user name does not exist

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

7、- Execute novamente a moficicação da senha:
SQL> ALTER USER new_schema IDENTIFIED BY ...;

8、- Realizar Testes de conexão com o novo owner:
SQL> CONN TEST_NEW_NAME/senha@ORCL;
Connected.
SQL>

9、- Verifique se os objetos estão no novo Owner/Schema:

SQL> select count(*) from dba_objects where owner ='TEST_NEW_NAME';

COUNT(*)
----------
1

10 - Note que o owner/schema antigo, não possui mais informações:
SQL> select count(*) from dba_objects where owner ='TEST_RENAME';

COUNT(*)
----------
0

Qualquer dúvida, deixe o seu comentário!

Desfragmentando Tabelas no Oracle 10g ou 11g

2016-02-03T05:14:00.004-08:00

Neste Post irei mostrar um dos caminhos para realizar a desfragmentação de tabelas no Oracle 10g/11g.

Quando preciso desfragmentar uma tabela?

Se a sua tabela recebe apenas inserções (insert) de dados, você nao precisa se preocupar porque ela nunca terá problemas de fragmentação.

O problema da fragmentação aparece quando atualizamos ou apagamos (update/delete) dados na tabela.

Os espaços que são liberados durante as operações (update/delete) não são imediatamente re-utilizado. Isto resulta em “espaços livre/blocos de dados espalhados nas tablespaces ” que são chamados de fragmentação de dados.

Vamos entender como o Oracle gerencia os espaços das tabelas.

Acredito que você ja ouviu falar em HWM (High water mark) nós conhecemos como marca d’agua.

Para entender melhor vamos fazer uma analogia, se você olhar dentro de uma caixa d’agua você vai ver uma linha (marca) proximo a borda entre o lugar que sempre tem agua e o lugar que nunca tem agua.

Do mesmo jeito acontece no Oracle a HWM define uma linha (marca) entre os blocos que foram utilizados e os blocos que nunca foram utilizados.

A marca dágua – HWM é o limite do número de blocos que uma tabela pode estar utilizando.

Quando realizamos uma leitura completa da tabela (full table scan, FTS), o Oracle sempre ira ler todos os blocos utilizados até a marca d’agua. Se existir muitos blocos livres que já foram utilizados até a marca d’agua, estes espaços livres (blocos) também serão lidos , isto gerar I/O extra e seu ambiente irá ter perda de perfomance durante operações de FTS.

Vamos identificar a marca d’agua na tabela e nós iremos saber se a tabela é candidata a reorganização.

Encontraremos 2 tipos de blocos que devemos nós preocupar:

Unused Space/empty_blocks – Que nunca foram utilizado.

Free Space/Blocks – Blocos sofreram update e delete

SQL> create table teste_sparano as select * from dba_objects;
Table created.

SQL> analyze table teste_sparano compute statistics;
Table analyzed.

SQL> select blocks "Usados",
2 empty_blocks "Nunca Utilizado",
3 num_rows "Total de Linhas"
4 from user_tables
5 where table_name='TESTE_SPARANO';

Usados Nunca Utilizado Total de Linhas
---------- --------------- ---------------
1016 7 69072

SQL> delete from teste_hwm
2 where owner ='SYS';

30219 rows deleted.

SQL> commit;
Commit complete.
Percebe que não houve diferença nos blocos utilizado.

SQL> select blocks "Usados",
2 empty_blocks "Nunca Utilizado",
3 num_rows "Total de Linhas"
4 from user_tables
5 where table_name='TESTE_SPARANO';

Usados Nunca Utilizado Total de Linhas
---------- --------------- ---------------
1016 7 38853

Se você tem um ambiente que possui tabelas que sofrem muitas modificações (update/delete), periodicamente você ira precisar desfragementar suas tabelas.

Verifique a necessidade de Reorganização

Baixo tempo de resposta (por tabela)
Alto numero de linhas encadeadas (na verdade migrada)
Tabela está crescendo e espaço antigo nao está sendo liberado
Nota: As consultas que utilizam indices não irá ter muito beneficio comparadas com as consultas que utilizam leitura completa (FTS).

Vamos reorganizar
Nas versões anteriores ao Oracle 10g , tinhamos 3 maneiras de reorganizar as tabelas.

Export, Import e Drop
Usar o recurso Online Object Redefinition
Usar o comando Alter table move (para outra tablespace ou na mesma)
Já no Oracle 10g conseguimos uma outra maneira de reorganizar os dados.

O comando Shrink: Este comando é aplicado somente em tabelas armazenado em Tablespace com AUTO SEGMENT SPACE MANAGEMENT.

Antes de usar o SHRINK deve ser habilitado a opção ROW MOVEMENT na tabela.

SQL> alter table teste_sparano enable row movement;
Table altered.

Existe 2 maneiras de executar o SHRINK.
1 – Executar em Duas etapas – Se estiver preocupado com disponibilidade da tabela
2 – Executar em Uma etapa – Se não estiver preocupado com disponibilidade da tabela

Eu sempre utilizo este comando em duas etapas.

Primeira etapa iremos realizar a reorganização das linhas da tabelas.
Durante esta etapa todas DML’s continuam ocorrendo. Esta etapa é a mais demorada.
Dependendo do tamanho da sua tabela este processo pode demorar horas.

SQL> alter table teste_sparano shrink space compact;
Table altered.

Elapsed: 00:00:02.89
Segunda etapa iremos resetar a marca d’agua (HWM).
Nesta etapa a tabela não pode sofrer DML, porém esta etapa é muito rapida.

SQL> alter table teste_sparano shrink space;
Table altered.

Elapsed: 00:00:00.27
Ou Podemos executar este processo em uma etapa. O Oracle irá realizar a reorganização das linhas (rows ) e ira resetar o HWM.

SQL> alter table teste_sparano shrink space;
Table altered.

Agora vamos ver a marca d’agua:

SQL> analyze table teste_sparano compute statistics;
Table analyzed.

SQL> select blocks "Usados",
2 empty_blocks "Nunca Utilizado",
3 num_rows "Total de Linhas"
4 from user_tables
5 where table_name='TESTE_SPARANO';

Usados Nunca Utilizado Total de Linhas
---------- --------------- ---------------
590 26 38853
A diferença entre o anterior é agora é de aproximadamente 50%, isto significa aumento de performance no meu ambiente, pois não precisarei ler blocos desnecessários durante o FTS.

Cuidados a serem tomados :
– Executando o shrink space sem o compact a tabela não poderá ser acessada durante o processo de desfragmentação.
– Caso você execute o shrink space compact e demore muito tempo para executar o shrink space para resetar o HWM, a tabela já poderá estar desfragmentada e este ultimo comando irá demorar mais que o esperado, pois ele irá primeiro desfragmenta os dados depois realizar o reset.
– Dependendo do tamanho da tabela você tem que se preocupar com o Tamanho da Tablespace de UNDO e com o parametrô UNDO_RETENTION.

Vantagens:
– Diferente do “alter table move” os indices não estarão com o status UNUSABLE.
Depois do comando shrink os indices são atualizados também.

– Não precisaremos de downtime para realizar a reorganização das tabelas.

– Não requer espaço extra para executar este processo.