[Oracle] - Identificando as sessões e seus processos no S.O

Segue abaixo, script para idenficar sessões e seus processos no S.O :

SELECT NVL(s.username, '(oracle)') AS username,
       s.inst_id,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   gv$session s,
       gv$process p
WHERE  s.paddr   = p.addr
AND    s.inst_id = p.inst_id
ORDER BY s.username, s.osuser;

[Oracle] - Consulta para identificar quantidade de commits por sessão

Consulta para identificar quantidade de commits por sessão no Oracle:

select c.sid, a.name estatistica, c.username||'@'||c.machine usuario, sum(b.value) qtd_commits 
from v$statname a, v$sesstat b, v$session c 
where a.statistic#=b.statistic# 
and b.sid = c.sid 
and a.name = 'user commits' 
and c.username is not null 
having sum(b.value) > 0 
group by c.sid,a.name, c.username||'@'||c.machine 
order by qtd_commits desc; 

[Oracle] - Utilizando regex para consultas

Bom pessoal, essa postagem é para auxiliar na utilização de expressões regulares no Oracle, facilitando a codificação de procedimentos armazenados e funções:

[Oracle] - Identificando a versão do Oracle Database

Bom pessoal, essa aqui é uma dica rápida para verificar a versão do Oracle Database que está utilizando e a versão de produtos correlacionados ao mesmo:

Queries : 

select *
from v$version;

select *
from product_component_version;

Results : 

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Solaris: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production



PRODUCT
VERSION
STATUS
NLSRTL
11.1.0.6.0
Production
Oracle Database 11g Enterprise Edition
11.1.0.6.0
64bit Production
PL/SQL
11.1.0.6.0
Production
TNS for Solaris:
11.1.0.6.0
Production

Espero que essa postagem ajude a outros profissionais.

Links consultados:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/dba004.htm
http://jasonvogel.blogspot.com.br/2008/08/getting-current-oracle-version-sql.html

[Oracle] - Procedimento para resetar sequences

Bom Pessoal,
 Essa é mais uma Postagem para ajudar em problemas do dia-a-dia, caso faça alguma restauração e os valores das "sequences" do banco estejam desatualizados, esse procedimento corrige a sequencia certa as "sequences" com base nos valores das tabelas informadas na base de dados. Segue abaixo, procedimento para efetuar está manutenção:

[Oracle] - Manutenção de Triggers Duplicadas

Bom Pessoal,

 Vou compartilhar um procedimento para fazer a exclusão de triggers duplicadas que são utilizadas para entregar id´s para cada registro inserido na tabela, segue abaixo:

[Oracle] - Informações de Data e Hora

Neste artigo estarei disponibilizando uma consulta para facilitar a utilização de informações de Data e Hora no Oracle database:

SELECT extract(YEAR FROM SYSDATE) AS ano,
       extract(MONTH FROM SYSDATE) AS mes,
       extract(DAY FROM SYSDATE) AS dia,
       EXTRACT(HOUR FROM NUMTODSINTERVAL(SYSDATE - trunc(SYSDATE), 'DAY')) AS hora,
       extract(minute FROM systimestamp) AS minuto,
       trunc(extract(SECOND FROM systimestamp)) AS segundo,
       SYSDATE AS data_hora
FROM   dual;

Espero que essa dica, ajudem outros profissionais que trabalham com Oracle, facilitando o dia-a-dia.
Graças e Paz sejam com Todos.

[Oracle] - Função para Retornar partes de um texto(string)

Bom pessoal, vou compartilhar uma função que retorna valores por parte de um texto especifico que estou utilizando, facilitando a utilização de particionamento de texto utilizando um carácter como ponto de particionamento:

CREATE OR REPLACE FUNCTION STRIPART(iTEXT VARCHAR2,

                     iCARA CHAR,

                     iINIC INTEGER,

                     iFINA INTEGER,

                     iTUDO INTEGER DEFAULT 1) RETURN VARCHAR2 AS

      vTEXT VARCHAR2(500) := iTEXT;

      vINIC INTEGER := 0;

      vFINA INTEGER := 0;

   BEGIN

      IF iINIC = 0

      THEN

         -- SE FOR ZERO É INICIO DE STRING SEMPRE

         vINIC := 1;

      ELSE

         -- PEGA A POSIÇÃO DO CARACTER iCARA

         vINIC := INSTR(iTEXT, iCARA, 1, iINIC) + 1;

      END IF;

      IF INSTR(iTEXT, iCARA, 1, iFINA) = 0

      THEN

         -- SE NAO ENCONTRAR O CARACTER FINAL PEGA TODA A STRING

         IF iTUDO = 1

         THEN

            vFINA := LENGTH(iTEXT);

         ELSE

            vFINA := 0;

         END IF;

      ELSE

         vFINA := INSTR(iTEXT, iCARA, 1, iFINA) - vINIC;

      END IF;

      vTEXT := SUBSTR(vTEXT, vINIC, vFINA);

      RETURN vTEXT;

   END;

[Oracle] - Função para Remover caracteres especiais em Textos

Bom pessoal, a função abaixo remover caracteres especiais em textos no Oracle, facilitando o tratamento de dados do tipo texto, auxiliando em consultas e criação de índices.

CREATE OR REPLACE FUNCTION NORMALIZAR(str_in VARCHAR2) RETURN VARCHAR2 IS
   pos           NUMBER(10);
   chars_special VARCHAR2(255);
   chars_normal  VARCHAR2(255);
   str           VARCHAR2(255) := UPPER(str_in);
BEGIN
   chars_special := 'ÁÀÃÂÉÊÍÓÔÕÚÜÇ.-';
   chars_normal  := 'AAAAEEIOOOUUC  ';
   str           := TRIM(upper(str));
   pos           := length(chars_normal);
   WHILE pos > 0
   LOOP
      str := REPLACE(str,
                     substr(chars_special, pos, 1),
                     substr(chars_normal, pos, 1));
      pos := pos - 1;
   END LOOP;
   str := TRIM(str);
   WHILE regexp_like(str, ' {2,}')
   LOOP
      str := REPLACE(str, '  ', ' ');
   END LOOP;
   pos := length(str);
   WHILE pos > 0
   LOOP
      IF regexp_like(substr(str, pos, 1), '[^A-Z0-9Ç@._ +-]+')
      THEN
         str := concat(substr(str, 1, pos - 1), substr(str, pos + 1));
      END IF;
      pos := pos - 1;
   END LOOP;
   RETURN str;
END;
/

[Oracle] - Trabalhando com Listas Dinâmicas

Bom pessoal, vou informar abaixo a implementação de criação e utilização de listas dinâmicas no Oracle, validas para versões 10g, 11g e 12c.

CREATE OR REPLACE TYPE t_id IS TABLE OF VARCHAR2(32000);

/

CREATE OR REPLACE

FUNCTION fnc_gera_lista(lista       VARCHAR2,

                           delimitador VARCHAR2) RETURN t_id IS

      v_id t_id;

   BEGIN

      SELECT regexp_substr(REPLACE(lista, delimitador, ','),

                           '[^,]+',

                           1,

                           LEVEL) AS lista

      BULK   COLLECT

      INTO   v_id

      FROM   dual

      CONNECT BY regexp_substr(REPLACE(lista, delimitador, ','),

                               '[^,]+',

                               1,

                               LEVEL) IS NOT NULL;

      RETURN v_id;

END;

/

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

--- EXEMPLO DE UTILIZAÇÃO: ---

-- LISTA DE DADOS NUMÉRICOS --

SELECT TO_NUMBER(COLUMN_VALUE) AS LISTA

        FROM   TABLE(FNC_GERA_LISTA('22;19;30;35;40;60;71;92;', ';')); 

-- LISTA DE DADOS ALPHANUMÉRICOS --

SELECT TO_CHAR(COLUMN_VALUE) AS LISTA

        FROM   TABLE(FNC_GERA_LISTA('a;B;C;d;E;F;g;H;', ';'));

-- LISTA DE DADOS ALPHANUMÉRICOS(DATAS) --

SELECT TO_CHAR(TO_DATE(COLUMN_VALUE ,'DD/MM/YYYY'), 'DD/MM/YYYY') AS LISTA

FROM   TABLE(FNC_GERA_LISTA('15/01/2011;11/12/2010;10/10/1999;16/08/1998;01/10/2003;12/12/2012;10/10/2010;11/11/2011;',

                            ';'));

[Oracle] - Formatação de Data para Sistemas e Geração de Senhas

 Bom pessoal, segue abaixo consulta para formatação de dados para visualização em front-ends e um gerador de senhas para oracle:

-- Oracle – Exibição data do sistema no formato extenso
-- exemplo 1
SELECT TO_CHAR(SYSDATE, 'FMDay, DD" de "Month" de "YYYY') AS data_formatada
FROM   dual;
-- exemplo 2
SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') AS data_formatada
FROM   dual;
-- Geraçao de senha aleatória no Oracle
-- exemplo 1
SELECT dbms_random.string('U', 2) || trunc(dbms_random.value(1000, 9999)) gera_senha
FROM   dual;

[ORACLE] - Verificação os parâmetros de processos, sessões e transações

Verificamos aqui os parâmetros que estão em vigência no nosso ambiente Oracle:

processes=x

session=(1.5 * PROCESSES) + 22

transactions=sessions*1.1

select name, value

from v$spparameter

where name in ('sessions','processes','transactions');

Name	Value
Processes	3000
Sessions	3022
Transactions

select name, value

from v$parameter

where name in ('sessions','processes','transactions');

Name	Value
Processes	3000
Sessions	4536
Transactions	4989

[ORACLE] - Formatação de Datas em texto no Oracle

Bom pessoal, segue abaixo formatação de datas para exemplo com os links para pesquisa quando necessário:

SELECT to_char(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') AS data_e_hora_inteira,
       to_char(SYSDATE, 'HH24:MI:SS') AS hora_inteira,
       to_char(SYSDATE, 'HH') AS hora_h12,
       to_char(SYSDATE, 'WW') AS SEMANA,
       to_char(SYSDATE, 'W') AS SEMANA1,
       to_char(SYSDATE, 'IW') AS SEMANA2,
       to_char(SYSDATE, 'Day', 'nls_language =''BRAZILIAN PORTUGUESE''') AS nome_dia,
       to_char(SYSDATE, 'Month', 'nls_language =''BRAZILIAN PORTUGUESE''') AS nome_mes,
       to_char(SYSDATE, 'YEAR', 'nls_language =''BRAZILIAN PORTUGUESE''') AS nome_ano,
       to_char(SYSDATE, 'DD', 'nls_date_language = PORTUGUESE') AS dia,
       to_char(SYSDATE, 'MM', 'nls_date_language = PORTUGUESE') AS mes,
       to_char(SYSDATE, 'YYYY', 'nls_date_language = PORTUGUESE') AS ano,
       to_char(SYSDATE, 'HH24') AS hora_h24,
       to_char(SYSDATE, 'MI') AS minuto,
       to_char(SYSDATE, 'SS') AS segundo,
       to_char(SYSDATE,
               ('DAY, dd "de" FMMONTH "de" YYYY'),
               'nls_date_language = PORTUGUESE') AS data_literal,
       to_char(SYSDATE,
               ('DAY, dd "," FMMONTH "," YYYY'),
               'nls_date_language = AMERICAN') AS data_literal_americana,
       to_char(SYSDATE,
               'yyyy-MON-dd, FMDAY',
               'nls_date_language = AMERICAN') data_padrao_americano,
       to_char(SYSDATE,
               'FMDAY , dd/MM/yyyy',
               'nls_date_language = PORTUGUESE') data_padrao_brasil,
       sessiontimezone AS timezone_da_sessao,
       current_date AS data_formato_timezone
FROM   dual;

Tabelas de parâmetros

Parâmetros	Descrição
YEAR	Ano (Ex: dois mil e onze, twenty eleven)
YYYY  YYY  YY  Y	Ano (Ex: 2011)  Ano (Ex: 011)  Ano (Ex: 11)  Ano (Ex: 1)
Q	Quadrimestre (1,2,3,4)
MM	Mês (Ex: 10)
MON	Abreviatura do nome do Mês (Ex: OUT)
MONTH	Nome do Mês (Ex: Outubro)
RM	Mês em números romanos (Ex: X)
WW	Semana do Ano de 1 a 53
W	Semana do mês de 1 a 5
D	Dia da semana de 1 a 7 (1 = Domingo até 7=Sábado)
DAY	Nome do dia da semana (Ex: Sabádo)
DD	Dia do Mês de 1 a 31
DDD	Dia do Ano de 1 a 366
DY	Abreviatura do dia da semana (Ex: SÁB)
HH	Hora de 1 a 12
HH12	Hora de 1 a 12
HH24	Hora de 1 a 24
MI	Minutos
SS	Segundos
SSSSS	Milésimos

Fonte:

http://ss64.com/ora/syntax-fmt.html

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html

[SQL] - BD de Cep 2014 para MySQL, PostgreSQL e Oracle

Bom pessoal, venho compartilhar a base de CEP 2014(17/01/2014) do Brasil em vários bancos de dados para facilitar o cadastro de endereçamento em diversa aplicações.

Segue abaixo, link para download:
https://www.dropbox.com/s/78zuhdotwdqr4kb/banco_de_dados_cep_17_01_2014.rar?dl=0

Espero que possa ajudar desenvolvedores que precisem de uma base de dados de endereçamento atualizada.

[SQL] - BD de Municípios IBGE 2013 e 2014 ( Oracle, MySQL, PostgreSQL e MS SQL Server)

Bom pessoal, venho compartilhar base de municípios do IBGE 2013 e 2014 atualizada para diversos bancos de dados, sendo Oracle, MySQL, PostgreSQL e MS SQL Server.

Segue abaixo, link para download:
https://www.dropbox.com/s/we4vis6p96cpkux/municipio_ibge.zip?dl=0

Espero que possa ajudar.

[Oracle] - Script par extrair DDL de tablespaces

Bom pessoal, segue abaixo script para geração de SQL para criação de tablespaces no Oracle:

set pagesize 0
set feedback off
set linesize 1000
spool cre_tbs.sql
select 'create tablespace ' || df.tablespace_name || chr(10)
|| ' datafile ''' || df.file_name || ''' size ' || df.bytes
|| decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize '
|| maxbytes)
|| chr(10)
|| 'default storage ( initial ' || initial_extent
|| decode (next_extent, null, null, ' next ' || next_extent )
|| ' minextents ' || min_extents
|| ' maxextents ' || decode(max_extents,'2147483645','unlimited',max_extents)
|| ') ;'
from dba_data_files df, dba_tablespaces t
where df.tablespace_name=t.tablespace_name;
spool off
set pagesize 20
set feedback on
set linesize 150

A consulta não é de minha autoria e o original pode ser encontrado em:
http://toolkit.rdbms-insight.com/gen_cre_ts.php

[Oracle] - Uso nls_language em funções SQL

Poucas pessoas sabem que boa parte das funções SQL do Oracle suportam sobrepor as configurações da sessão no que tange o NLS (national language support), abaixo exemplos da utilização dessa sintaxe:

TO_DATE ('1-JAN-99', 'DD-MON-YY', 'nls_date_language = American') 
TO_CHAR (hire_date, 'DD/MON/YYYY',  'nls_date_language = French')  
TO_NUMBER ('13.000,00', '99G999D99', 'nls_numeric_characters = '',.''')  
TO_CHAR (salary, '9G999D99L', 'nls_numeric_characters = '',.''
                               nls_currency = '' Dfl''') 
TO_CHAR (salary, '9G999D99C', 'nls_numeric_characters = ''.,''
                               nls_iso_currency = Japan') 
NLS_UPPER (last_name, 'nls_sort = Swiss')  
NLSSORT (last_name, 'nls_sort = German')

Fonte: http://download.oracle.com/docs/cd/B10500_01/server.920/a96529/ch7.htm

http://oraclemais.blogspot.com.br/2010/09/uso-nlslanguage-em-funcoes-sql.html 

[Oracle] - Habilitando autoextend para datafiles

O DBA hoje em dia não precisa mais se preocupar com o overhead causado pela extensão automática dos datafiles. Soluções como gerenciamento local das tablespaces e infraestrutura de hardware mais competentes absorvem quase que totalmente o impacto. Sendo assim, habilitar esse recurso ajuda a deixar a administração do banco mais fácil.
Ex.:
alter database datafile '+DATA/dr/datafile/users.264.708874247' autoextend on next 256M;

Nesse exemplo estou habilitado a extensão automática para o datafile (autoextend on) e estou informando de quantos em quantos megas eu quero que isso aconteça (next 256M).

Para habilitar para todos os datafiles:

spool runts.sql

select
'alter database datafile '||
file_name||
' '||
' autoextend on;'
from
dba_data_files;

@runts

Fonte:
http://oraclemais.blogspot.com.br/2010/06/oracle-autoextend-on.html

[Oracle] - FKs apontando para as PKs e UKs de uma tabela

As vezes acontece o erro abaixo quando tentamos truncar ou deletar todas as informações de uma tabela. Pare resolver é necessário identificar quais tabelas apontam para a tabela que eu quero truncar e precisamos desabilitar essas FKs.

Erro Oracle:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

1 - Identificando as FKs:

SELECT owner,
table_name,
constraint_name
FROM all_constraints ci
WHERE ci.constraint_type = 'R'
AND (ci.r_owner , ci.r_constraint_name) IN
(SELECT owner,
constraint_name
FROM all_constraints c
WHERE owner = '&dono'
AND table_name = '&tabela'
AND constraint_type IN ('P','U')
)

2 - Desabilite as constraints retornadas:

ALTER TABLE owner.tabela DISABLE CONSTRAINT nome_constraint;

3 - Execute o truncate das tabelas filhas e pai:

TRUNCATE TABLE owner.tabela_filha;
TRUNCATE TABLE owner.tabela_pai;


4 - Habilite as constraints retornadas:

ALTER TABLE owner.tabela ENABLE CONSTRAINT nome_constraint;

Fonte:
http://oraclemais.blogspot.com.br/2010/11/fks-apontando-para-as-pks-e-uks-de-uma.html

[Oracle] - Verificando o andamento do Job do datapump(expdp/impdp)

Para saber o andamento de um job do datapump faça o seguinte select:

SELECT job_name "Nome",
owner_name "Dono" ,
workers ,
job_mode "Modo" ,
dp.state "Status" ,
ROUND((sofar*100)/totalwork,2) "% Completado"
FROM gv$session_longops sl, gv$datapump_job dp
WHERE sl.opname = dp.job_name
AND sofar != totalwork

Fonte:
http://oraclemais.blogspot.com.br/2009/07/datapump.html