Tuning na Instruçao SQL LIKE no Oracle

Fala pessoal, vou postar aqui um dica simples, que é quando se precisa de um tunning rápido nas declarações "LIKE"  que o CBO naturalmente ignora os índices existentes nas colunas utilizadas pelo LIKE para uso de um FTS. Quando ocorre isso temos o recurso de utilizar um hint forçando a utilização do índice.

No Blog do Laurent Schneider tem um demo bem legal.
Abaixo a demonstração.

CREATE TABLE lsc_t AS
SELECT
  ROWNUM ID,
  SUBSTR(DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
 || ' '
 || DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
 ,1,17) NAME,
  TRUNC(SYSDATE-ABS(10000*DBMS_RANDOM.NORMAL)) birthdate,
  LPAD('X',4000,'X') address
FROM DUAL
CONNECT BY LEVEL <= 1e5;

ALTER TABLE lsc_t ADD PRIMARY KEY(ID);

CREATE INDEX lsc_i ON lsc_t(NAME);

EXEC dbms_stats.gather_table_stats(user,'LSC_T',cascade=>true);

SQL> SELECT ID, NAME, birthdate
  FROM lsc_t WHERE NAME LIKE '%ABC%';  2
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |  5044 |   123K| 22123   (1)| 00:04:26 |
|*  1 |  TABLE ACCESS FULL| LSC_T |  5044 |   123K| 22123   (1)| 00:04:26 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------
  379  recursive calls
    0  db block gets
  100090  consistent gets
  100016  physical reads
    0  redo size
    2066  bytes sent via SQL*Net to client
  491  bytes received via SQL*Net from client
    4  SQL*Net roundtrips to/from client
    5  sorts (memory)
    0  sorts (disk)
   35  rows processed

SWLQL> SELECT /*+INDEX(LSC_T,LSC_I)*/  ID, NAME, birthdate
  FROM lsc_t WHERE NAME LIKE '%ABC%';  2

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  5044 |   123K|  3574   (1)| 00:00:43 |
|   1 |  TABLE ACCESS BY INDEX ROWID| LSC_T |  5044 |   123K|  3574   (1)| 00:00:43 |
|*  2 |   INDEX FULL SCAN           | LSC_I |  5000 |       |   220   (2)| 00:00:03 |
-------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
    1  recursive calls
    0  db block gets
  360  consistent gets
  322  physical reads
    0  redo size
  142849  bytes sent via SQL*Net to client
  491  bytes received via SQL*Net from client
    4  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
   35  rows processed

Habilitar DDL no alert.log Oracle

Fala pessoal, o 11g tem um recurso interessante que é habilitar o log de comandos DDLs no alert.log, para habilitar é bem simples basta alterar o parâmetro ENABLE_DDL_LOGGING para true, segue abaixo um pequeno step by step que altera o parâmetro e cria alguns objetos para teste.

COLUMN DESCRIPTION FORMAT A30
COLUMN VALUE FORMAT A30
SET LINESIZE 120

SQL> select value,isdefault,isses_modifiable, issys_modifiable,isbasic,description  
from v$parameter  where name='enable_ddl_logging';

VALUE  ISDEFAULT ISSES ISSYS_MOD ISBAS DESCRIPTION
------ --------- ----- --------- ----- -------------------
FALSE   TRUE    TRUE  IMMEDIATE FALSE enable ddl logging

SQL> alter system set ENABLE_DDL_LOGGING=true ;

System altered.

SQL> conn hudson/c402d92

Connected

SQL> create table t_hudson as select * from user_tables;

Table created.

SQL> grant select on t_hudson to public;

Grant succeeded.

SQL> alter table t_hudson read only;

Table altered.

SQL> drop table t_hudson ;

Table dropped.

Após a alteração do parâmetro vamos verificar se foi gerado o log no alert.log:


[oracle@rjhud trace]$ tail -f alert_centro.log

Mon Nov 21 15:58:43 2011
SMCO started with pid=31, OS id=5981
Mon Nov 21 16:08:37 2011
ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=SPFILE;
ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;
Mon Nov 21 16:09:41 2011
create table t_hudson as select * from user_tables
Mon Nov 21 16:10:09 2011
alter table t_hudson read only
drop table t_hudson


Para maiores informações sobre o parâmetro é só consultar o link:

 http://download.oracle.com/docs/cd/E14072_01/server.112/e10820/initparams078.htm

Exportar linhas sem dump ou backup no Oracle

Muitas vezes é necessário obter um número pequenos de linhas de algumas ou todas tabelas de um ambiente de produção, mas nem sempre um dump ou um backup esta disponível ou ainda o tempo de demora pode atrasar muito uma atuação, quem trabalha em consultorias, fabricas e qualquer lugar onde ambiente de produção é um lugar onde você não pode entrar em 1000 seguranças do seu lado sabe do que eu estou falando.
Então um meio muito utilizado é gerar "Inserts" concatenando via selects os famosos :

SELECT 'INSER INTO ..... '  || COLUMNS  .....

Dessa forma é possível obter os dados da tabela de uma forma simples e rápida, o problema é o tempo gasto na hora de gerar esses selects, principalmente se estamos falando de uma tabela com muitas colunas, ou se você precisa de muitas tabelas.
Pensando nisso eu fiz uma package que gera esses selects dinamicamente , recebendo como argumentos o nome de uma tabela com o número de linhas que deseja, ou passando diretamente um onwer e as linhas que precisa.
Óbvio que esse método só é útil quando precisa de um número pequeno de linhas e não tem em mãos outras ferramentas.

Abaixo o código de criação das packages :

CREATE OR REPLACE PACKAGE PKG_DUMP AS

  PROCEDURE P_DUMP(V_OWNER VARCHAR2,V_ROWS INT);
  FUNCTION V_DUMP(v_table_name VARCHAR2,V_ROWS INT) RETURN VARCHAR2;

END;
/
CREATE OR REPLACE PACKAGE BODY PKG_DUMP AS

PROCEDURE P_DUMP(V_OWNER VARCHAR2,V_ROWS INT)
IS
  V_SMT VARCHAR2(3000);
  V_TABLE_NAME VARCHAR2(100);
  CURSOR trip_cursor IS SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER=V_OWNER AND TABLE_NAME IN 
  ('ACTION','ROLE','ROLE_ACTION','URL','MENU');
BEGIN
  FOR trip_record IN trip_cursor LOOP
  SELECT V_DUMP(trip_record.TABLE_NAME,V_ROWS) INTO V_SMT FROM DUAL;
  V_TABLE_NAME := trip_record.TABLE_NAME;
  dbms_output.new_line();
  dbms_output.new_line();
  DBMS_OUTPUT.PUT_LINE('/************* ' || V_TABLE_NAME || '***************/');
  dbms_output.new_line();
  DBMS_OUTPUT.PUT_LINE(V_SMT);
  END LOOP;
--EXECUTE IMMEDIATE V_SMT ;
EXCEPTION
    WHEN OTHERS THEN
    PKG_ERR.PRC_HANDLE(-20001, 'Problems in P_DUMP  ' || V_TABLE_NAME );
END P_DUMP;

FUNCTION V_DUMP(v_table_name VARCHAR2,V_ROWS INT) 
RETURN VARCHAR2
IS

  v_column_name VARCHAR2(5000);
  v_column_name2 VARCHAR2(5000);
  v_column_name3 VARCHAR2(5000);
  v_column_name4 VARCHAR2(5000);
  v_column_name5 VARCHAR2(5000);
  V_SMT VARCHAR2(5000);
   
  CURSOR trip_cursor IS SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME=v_table_name ORDER BY COLUMN_ID;

BEGIN

  FOR trip_record IN trip_cursor LOOP
  v_column_name2 := trip_record.column_name ;
  v_column_name := v_column_name || ',' || v_column_name2;

  END LOOP;
  SELECT REGEXP_REPLACE(v_column_name, ',' , '' ,1,1)  into v_column_name3 FROM DUAL;
  --SELECT REGEXP_REPLACE(v_column_name, ',' , '|| '',''||' ,2) into v_column_name4 FROM DUAL;
  SELECT REGEXP_REPLACE(v_column_name, ',' , '|| '''''''' || '',''||'''''''' ||' ,2) into v_column_name4 FROM DUAL;
  SELECT REGEXP_REPLACE(v_column_name4, ',' , ',''''||''''''''||' ,1,1)  into v_column_name5 FROM DUAL;
  --DBMS_OUTPUT.PUT_LINE(v_column_name);
  V_SMT:= ' SELECT ''INSERT INTO ' || v_table_name || ' ( ' || v_column_name3 || ' ) VALUES (''' || v_column_name5
  || '||'''''''' ||'');''  FROM ' || v_table_name || ' WHERE ROWNUM < ' || V_ROWS || ';';
  --EXECUTE IMMEDIATE V_SMT ;
  --DBMS_OUTPUT.PUT_LINE(V_SMT);
  RETURN V_SMT;
EXCEPTION
    WHEN OTHERS THEN
    PKG_ERR.PRC_HANDLE(-20001, 'Problems in V_DUMP  ' || v_table_name );
END V_DUMP;
END PKG_DUMP;
/

Agora as formas de Utilizar a package, a primeira forma é gerar o insert de apenas uma tabela. Primeiro executando a function PKG_DUMP.V_DUMP passando o nome da tabela e depois o número de linhas.

new@ORACLE> SELECT PKG_DUMP.V_DUMP('TESTE',29) FROM DUAL;
 SELECT 'INSERT INTO TESTE ( X,C,H,G,HG ) VALUES (',''||''''||X|| '''' || ','||'''' ||C|| '''' || ','||'''' ||H|| '''' || ','||'''' ||G|| '''' || ','||'''' ||HG||'''' ||');'  FROM TESTE WHERE ROWNUM <
 29;

Após o resultado executa o select gerado e já se tem o insert pronto.

new@ORACLE>  SELECT 'INSERT INTO TESTE ( X,C,H,G,HG ) VALUES (',''||''''||X|| '''' || ','||'''' ||C|| '''' || ','||'''' ||H|| '''' || ','||'''' ||G|| '''' || ','||'''' ||HG||'''' ||');'  FROM TESTE WHERE ROWNUM <
 29;  2
INSERT INTO TESTE ( X,C,H,G,HG ) VALUES (
'1','2','1','1','A');

INSERT INTO TESTE ( X,C,H,G,HG ) VALUES (
'4','5','1','1','A');

INSERT INTO TESTE ( X,C,H,G,HG ) VALUES (
'4','5','1','1','A');

A outra forma é tendo todas as tabelas, executando a procedure PKG_DUMP.P_DUMP passando o owner e o número de linhas:

new@ORACLE> set pagesize 0
set long 90000
set linesize 200
set trimspool ON
set feedback off
set echo off 

new@ORACLE>EXECUTE PKG_DUMP.P_DUMP('NEW',100);

Ele vai gerar o output de todos os selects para geraçao de inserts de todas as tabelas que esse owner possui, é aconselhável utilizar um spool para receber esse conteúdo.

That's all folks 

Descobrindo o DBID quando o banco esta em nomount

Fala pessoal, uma situação chata mas que pode acontecer é recuperar os controlfiles depois de alguma perda. Se você não usa a FRA para uma recuperação automática dos controlfiles com o rman, você vai precisar setar o DBID e o control-file autobackup location manualmente.
Mas ai que mora a questão, se seu banco não esta aberto e você não tem o DBID anotado, como descobrir ele ??
Então vamos a boa dica :

Primeira coisa vamos logar no rman:

[oracle@rjhud ~]$ rlwrap rman target=sys/c402d92@centro

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 24 11:46:23 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN>


Agora que começa o legal da história , precisamos descobrir o DBID e como o banco não esta montado não iremos conseguir acessar a V$DATABASE então o que vamos fazer, vamos no diretório onde os backups estão armazenados ou no diretório dos datafiles e vamos fazer a seguinte busca atraves do comando strings do sistema operacional.
Os comandos vão variar para o tipo de backup que você vai pesquisar, se é um backup do datafile system ou undo ou um backupset.

strings file_name |grep MAXVALUE, (No caso do SYSTEM datafile or FULL Backup)
strings file_name |grep MAXVALUE (No caso do UNDO datafile )

Exemplos :

[root@rjhud backup2]# ls -lh

-rw-r----- 1 oracle oinstall 972K Aug 23 16:42 01mknoqp_1_1_TAGBACKUP_FULL
strings 01mknoqp_1_1_TAGBACKUP_FULL |grep MAXVALUE,

1287652985, MAXVALUE,

Esse é o meu DBID :1287652985

Agora é só seguir a sequencia para restaurar o controlfile e da próxima vez mantenha o seu DBID guardado em uma planilha ou repositório qualquer.

That's all folks

ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

Estava agora configurando uma base nova, antes de passar ela para ARCHIVELOG no momento que estava configurando o diretório dos archives recebi o famoso:

SQL> Alter system set log_archive_dest_1='/u01/app/oracle/archives/iraja';

ORA - 16179 : incremental changes to "log_archive_dest_1" not allowed with 

Meu cenário era um Oracle Database 11G R2 com um Red Hat 5.5.

A Solução é bem simples galera e eu achei no metalink que tem o Doc ID: 194494.1.
Só adicionar a palavra LOCATION antes do caminho do diretório.

SQL> ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archives/iraja' ;

System altered.

That's all folks

Problema com DISPLAY no VirtualBox com Oracle

Post simples e com muito conteúdo na Internet, mas que de vez em quando me dava trabalho e eu não vi/não achei nada tão simples na internet.
Problema :

>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<

Cenário:
VirtualBox 4.4 Instalando o Oracle Database 11G R2

Soluçao :
Como root executar :
xhost +localhost

Depois loga com o oracle na mesma sessão e zaz(Como diria o chaves).

That's all folks !!

Recuperando Variável Bind com a V$SQL_BIND_CAPTURE

Segue abaixo uma forma bem simples de pegar o valor de uma variável bind utilizando a view V$SQL_BIND_CAPTURE . Precisei recentemente pegar os valores de uma consulta que uma sessão estava realizando e o trace não estava ajudando por "n" fatores e utilizando o velho dicionario de dados foi bem mais simples nesse caso.

1_ Buscando o valor o sql_id na V$SQL :

SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%all_tab_columns%';

SQL_TEXT                                  SQL_ID

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

select col.*, com.Comments from sys.all_  032n4avhdnaz3

tab_columns col,      sys.all_col_commen

WHERE o.owner LIKE :1 ESCAPE '/'

AND o.object_name LIKE 2: ESCAPE '/'

Atenção a identificação das binds , essa mesma posição vai ser recuperada logo a frente.

2_ Olhando a V$SQL_BIND_CAPTURE

SQL>

 desc V$SQL_BIND_CAPTURE

 Name                                                              Null?    Type

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

 ADDRESS                                                                    RAW(8)

 HASH_VALUE                                                                 NUMBER

 SQL_ID                                                                     VARCHAR2(13)

 CHILD_ADDRESS                                                              RAW(8)

 CHILD_NUMBER                                                               NUMBER

 NAME                                                                       VARCHAR2(30)

 POSITION                                                                   NUMBER

 DUP_POSITION                                                               NUMBER

 DATATYPE                                                                   NUMBER

 DATATYPE_STRING                                                            VARCHAR2(15)

 CHARACTER_SID                                                              NUMBER

 PRECISION                                                                  NUMBER

 SCALE                                                                      NUMBER

 MAX_LENGTH                                                                 NUMBER

 WAS_CAPTURED                                                               VARCHAR2(3)

 LAST_CAPTURED                                                              DATE

 VALUE_STRING                                                               VARCHAR2(4000)

 VALUE_ANYDATA                                                              ANYDATA

3_Buscando o valor da bind desejada :

SQL_ SELECT NAME,TO_CHAR(LAST_CAPTURED,'DD/MM/YYYY HH24:MI:SS'),VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID='0prhvnya3f97z' ;

NAME                           TO_CHAR(LAST_CAPTUR VALUE_STRING

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

:1                             01/08/2011 16:24:39 REPORT_SERVER_AALTAMIRANO

:2                             01/08/2011 16:24:39 ROLE

Pronto, de forma simples foi recuperado os valores passados no filtro da consulta SQL.