Oracle - SQL Developer - Números das Linhas

Oracle - Decimal\Horas

DECIMAL/HORAS

select  690/60,

        trunc((690/60),0),

        ((690/60)-(trunc((690/60),0)))*60

from dual

SELECT  HORA,

        TO_NUMBER(SUBSTR(HORA,1,INSTR(HORA,':')-1)) HORA,

        TO_NUMBER(SUBSTR(HORA,(INSTR(HORA,':')+1),2)) MINUTO,

        (TO_NUMBER(SUBSTR(HORA,1,INSTR(HORA,':')-1)) +

         TO_NUMBER(SUBSTR(HORA,(INSTR(HORA,':')+1),2))) AS VLRDECIMAL

FROM ZPFFINANC_IMP

Oracle - INSTR (PATINDEX - SQL Server)

PATINDEX - INSTR

quarta-feira, 4 de janeiro de 2017

13:41

SELECT  HORA,

        TO_NUMBER(SUBSTR(HORA,1,INSTR(HORA,':')-1)) HORA,

        TO_NUMBER(SUBSTR(HORA,(INSTR(HORA,':')+1),2)) MINUTO,

        (TO_NUMBER(SUBSTR(HORA,1,INSTR(HORA,':')-1)) +

         TO_NUMBER(SUBSTR(HORA,(INSTR(HORA,':')+1),2))) AS VLRDECIMAL

FROM ZPFFINANC_IMP

Oracle - UPDATE com FROM

Exemplo:

-- ATUALIZA CAMPO DE HORARIO (LEGADO)

 UPDATE ZIMPFUNC_DEPARA_MATER A SET A.CODHORARIOLEG = ( SELECT  COD_HORARIO_061

                                                        FROM ZIMPFUNCIONARIOS_MATER B

                                                        WHERE B.CHAPA_001 = A.CODFUNC);

                                                                                                              

-- ATUALIZA CAMPO DE HORARIO (RM)

 UPDATE ZIMPFUNC_DEPARA_MATER A SET CODHORARIORM = ( SELECT  CODLABORE

                                                     FROM ZIMPFUNC_HORARIOS B

                                                     WHERE upper(substr(descr_dominio,2,length(descr_dominio))) = upper(a.codhorarioleg));  

Oracle - SELECT * + campos

SELECT  X.*,

               5 AS CODCOLIGADA

FROM ZIMPFUNCIONARIOS_MATER X

SELECT  5 AS CODCOLIGADA,

              X.*

FROM ZIMPFUNCIONARIOS_MATER X

Sempre apelidar a tabela, para que esta forma de SELECT + campos adicionais funcione

Oracle - Upper, Lower, Initcap, Replace, CHR e ASCII

Upper, Lower, Initcap, Replace, CHR e ASCII 

SELECT  UPPER('teste') AS MAIUSCULO,

        LOWER(UPPER('teste')) MINUSCULO,

        INITCAP('teste') PRIMEIRA_MAISUCULA,

        REPLACE(('TESTE  TAB  TAB'),CHR(32),' ') AS RETIRA_TAB,

       'FUMIO'||CHR(32)||'TESTE' AS ADICIONA_TAB_TEXTO,

        ASCII(' ') VERIFICA_CODIGO_ASC_TAB

from dual

Oracle - Translate\Acentuação

SELECT NOME

FROM PPESSOA

WHERE upper

    (translate(nome,

    'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü',

    'ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu')) =  'FÁBIO FÜMIO'

Oracle|MSSQL - ROWNUM\TOP

Prezados,

No banco dados SQL Server (MSSQL), é comum criarmos sentenças com o comando TOP, para facilitar as nossas pesquisas e análises.

Ex:          SELECT TOP 5 *

                FROM GCOLIGADA

Neste comando, ele listará os 5 primeiros registros da tabela GCOLIGADA.

No Oracle, não temos o comando TOP, com o mesmo resultado/funcionalidade, então podemos usar o comando ROWNUM, que funcionará da mesma forma que o comando TOP (MSSQL):

Ex:          SELECT *

                FROM GCOLIGADA

                WHERE ROWNUM <= 5

Oracle - Procedures Válidas?

SELECT OBJECT_NAME, STATUS

FROM DBA_OBJECTS

WHERE OBJECT_TYPE = ’PROCEDURE’

AND STATUS = ‘INVALID’

Oracle - Function (Retira Acentuação)

Criei uma função para alterar os caracteres acentuados de um texto/string, facilitando assim algumas consultas/pesquisas que temos de realizar, uma vez que o Oracle diferencia caracteres acentuados dos não acentuados.

O nome da função é: FNC_RETIRA_ACENTUACAO, e a sua utilização é feita da seguinte forma:

FNC_RETIRA_ACENTUACAO( “Texto a ser alterado”)

Ex:          SELECT FNC_RETIRA_ACENTUACAO('ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü')

FROM DUAL;

Resultado:  ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu

CREATE OR REPLACE FUNCTION FNC_RETIRA_ACENTUACAO(V_TEXTO_I IN VARCHAR2)

RETURN VARCHAR2

IS V_TEXTO_O VARCHAR2(100);

BEGIN

  V_TEXTO_O := translate(V_TEXTO_I,'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü',

                                   'ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu'); 

  RETURN (V_TEXTO_O);

END;

SELECT FNC_RETIRA_ACENTUACAO('ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü')

FROM DUAL

SQL => Oracle / Oracle => SQL

http://www.sqlines.com/online

Oracle - Exemplos

GRANT CREATE DATABASE LINK TO RM;
/

CREATE TABLE ZTABELASEXCLUIDAS (NOMETABELA VARCHAR(150),
CONSTRAINT PKZTABELASEXCLUIDAS PRIMARY KEY (NOMETABELA));
/
CREATE TABLE ZALIASORIGEM (NOMEALIAS VARCHAR(60),
                           CODCOLIGADA NUMBER(5),
CONSTRAINT PKZALIASORIGEM PRIMARY KEY (NOMEALIAS));
/
CREATE OR REPLACE PROCEDURE SP_ATIVAFOREIGNKEYS (P_ACAO IN VARCHAR)
AS
  P_NOMETABELA VARCHAR(128);
  P_CHAVEESTRANGEIRA VARCHAR(128);
  P_CURSORNRO INTEGER;
  P_EXECUTE INTEGER;
  CURSOR CURSORTABELAS IS SELECT TABLE_NAME
                          FROM USER_TABLES;

  CURSOR CURSORCONSTRAINTS IS SELECT CONSTRAINT_NAME
                           FROM USER_CONSTRAINTS
                           WHERE TABLE_NAME = P_NOMETABELA
                           AND   CONSTRAINT_TYPE = 'R';      /* SOMENTE FOREIGN KEYS */
BEGIN
  OPEN CURSORTABELAS;
  FETCH CURSORTABELAS INTO P_NOMETABELA;
  LOOP
  EXIT WHEN CURSORTABELAS%NOTFOUND;

    OPEN CURSORCONSTRAINTS;
    FETCH CURSORCONSTRAINTS INTO P_CHAVEESTRANGEIRA;
    LOOP
    EXIT WHEN CURSORCONSTRAINTS%NOTFOUND;
 
      P_CURSORNRO := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(P_CURSORNRO, 'ALTER TABLE ' || P_NOMETABELA || ' ' || P_ACAO || ' CONSTRAINT ' || P_CHAVEESTRANGEIRA, DBMS_SQL.NATIVE);
      P_EXECUTE :=DBMS_SQL.EXECUTE(P_CURSORNRO);
      DBMS_SQL.CLOSE_CURSOR(P_CURSORNRO);
 
    FETCH CURSORCONSTRAINTS INTO P_CHAVEESTRANGEIRA;
    END LOOP;
    CLOSE CURSORCONSTRAINTS;

  FETCH CURSORTABELAS INTO P_NOMETABELA;
  END LOOP;
  CLOSE CURSORTABELAS;
END;
/

Oracle - Dicas para Criação / Manutenção de uma instância Oracle

Dicas para Criação / Manutenção  de uma instância Oracle

(RM Sistemas)

O presente documento contém algumas dicas para DBAs manterem suas instâncias Oracle em perfeito estado de funcionamento.

As primeiras dicas referem-se a parâmetros encontrados no arquivo init.ora, encontrado no diretório de instalação da instância Oracle na máquina em questão.

Para alterar alguns deles (quando possível), basta editar o arquivo em questão, salvá-lo e restartar a instância.

Quaisquer dúvidas podem ser encaminhadas ao suporte da RM Sistemas através do mail (suporte@rm.com.br).

Tamanho  do bloco Oracle

                      

Este parâmetro deve ser configurado no momento da criação da base de dados. Sugerimos a utilização de blocos de 8k (DB_BLOCK_SIZE =  8192). 

OBS: Note que o tamanho do bloco deve ser informado em nº de bytes.

Caso a instância já esteja criada, pode-se verificar a configuração deste parâmetro, editando-se o arquivo  de parâmetros (init.ora)  do Oracle e verificando o valor da variável  DB_BLOCK_SIZE.

Uma outra alternativa para visualizar o valor do parâmetro seria a utilização da ferramenta Instance Manager. Aqui, o parâmetro pode ser visualizado através de:

            Initialization Parameters / Instance Specific

A alteração deste parâmetro só pode ser feita caso a instância seja recriada.

 Distribuição da memória  disponível para o Oracle

Depois de determinada a quantidade de memória que estará disponível para o Sistema Operacional e a quantidade que ficará disponível para o Oracle, deve-se fazer a distribuição da memória disponível para o Oracle.

           

o   SORT_AREA_SIZE 

Deve-se configurar este parâmetro com grande cautela, uma vez que ele reflete a quantidade de memória que cada conexão de usuário irá ter disponível para operações de SORT.

Devemos levar em consideração também a quantidade de memória fixa que cada conexão de usuário gasta (1,7 MB).

Sendo assim, o gasto de memória com conexões de usuários em uma aplicação onde estejam abertas 10 sessões e o SORT_AREA_SIZE esteja configurado com o valor de 1048576  (1MB)  é:

         

          10 * (1,7+1) =  27 MB.

OBS: Note que o valor do SORT_AREA_SIZE  deve ser informado em nº de bytes.

O valor a ser configurado para este parâmetro deve ser definido levando-se em consideração  o número de conexões  simultâneas no Oracle e a quantidade de memória disponínel para  mesmo.

o   DB_BLOCK_BUFFER

Este parâmetro irá refletir a quantidade de memória destinada ao Oracle que será utilizada para Cache de dados.

Este parâmetro deve ser configurado com o número de blocos do Oracle que será utilizado para este Cache.

Sugerido:

60% da memória restante destinada ao Oracle destinada ao Cache de dados.

Como calcular o valor do parâmetro:

          

DB_BLOCK_BUFFER =  (M * 1048576) / B

Onde:  M = É a quantidade de memória ( em Mega bytes ) que deve  ser destinada ao Buffer de Dados.

B= Tamanho do bloco de dados (em bytes) utilizado pelo Oracle.

o   SHARED POOL

Este parâmetro irá refletir a quantidade de memória destinada ao Oracle que será utilizada para o SHARED POOL.

Sugerido:

Restante da memória destinada ao Oracle (40%), destinada ao SHARED POOL.

O valor deste parâmetro deve ser determinado de Bytes.

SHARED_POOL_SIZE = M

Onde:  M= Memória destinada ao SHARED POOL em bytes.

Tablespaces

Deve-se criar pelo menos uma TableSpace distinta para cada finalidade: System, Dados,  Indices, Temp, Rollback, Redo.

É desejável também que os DataFiles destas TableSpaces sejam distribuídos em discos diferentes, de forma a minimizar a concorrência de acesso. Isto deve ser feito conforme a disponibilidade de discos. Exemplo:

           Cenário 1:

           Um disco:

System, Dados,  Iindices, Temp, Rollback , Redo no mesmo disco, mas em TableSpaces diferentes.

           Cenário 2:

           Dois discos:

                       1º disco:  Dados + Temp + Rollback

2º disco: System + Indices + Redo

CPU

Deve-se verificar costantemente a taxa de utilização da CPU, a mesma não deve estar com taxa de utilização muito alta durante a maior parte do período (90% de utilização).

Caso esteja utilizando mais de um processador deve-se verificar se a carga da CPU está balanceada.

Para estas medições, pode se utilizar a ferramenta Performance Monitor (no caso de servidores Windows NT/2000).

COALESCE

Deve-se verificar periodicamente (mensalmente) a fragmentação do espaço livre  alocado para cada TableSpace.

o   Pode-se verificar tal fragmentação utilizando:

SELECT TABLESPACE_NAME, TOTAL_EXTENTS , PERCENT_EXTENTS_COALESCED

FROM  DBA_FREE_SPACE_COALESCED

WHERE PERCENT_EXTENTS_COALESCED <> 100

o   Para efetuar a desfragmentação:

ALTER TABLESPACE tablespace COALESCE

Atualização de estatísticas

A rotina de atualização de estatísticas deve ser agendada para execução diária..

Sugere-se o utilizar o Package DBMS_UTILITY para fazer a atualização de estatísticas:

execute DBMS_UTILITY.analyze_schema('RM','COMPUTE')

Para agendar a execução diária de estatísticas em um servidor que esteja utilizando o  Windows NT ou Windows 2000:

1º) crie um arquivo TXT com o seguinte conteúdo:

execute DBMS_UTILITY.analyze_schema('RM','COMPUTE')

             2º)  Utilize o command prompt para agendar a tarefa :

at  22:00 every:M,T,W,Th,F,S,Su cmd /c "sqlplus rm/rm @d:\temp\script.txt"

O comando acima agenda a atualização de estatíscas para ocorrer todos os dias às 22:00 horas. Considera-se que o arquivo TXT criado no primeiro item é o d:\temp\script.txt

Indices

Os índices devem ser reconstruídos periodicamente (mensalmente) , ou após periodo de grande “deleção” dos mesmos.

Para a verificação do percentual de linhas deletadas em um determinado índice utilize:

1º)  Analyze :

               

     ANALYZE INDEX indice VALIDATE STRUCTURE;

    

2º)  Verificando o percetual de deleção:

SELECT LF_ROWS , DEL_LF_ROWS

FROM INDEX _STATS

OBS: Caso 30% das linhas em folhas (LF_ROWS) tenham sido deletados (DEL_LF_ROWS), o índice deve ser reconstruído.

    3º)             Reconstrução do índice:

          ALTER INDEX indice REBUILD tablespace.

Para reconstruir o índice, durante o REBUILD  deve haver espaço disponível para a coexistência dos dois índices (antigo e novo).

O comando abaixo gera um script para a reconstrução de todos os índices da base de dados:

SELECT 'ALTER INDEX  ' ||  INDEX_NAME || 

' REBUILD TABLESPACE RM_INDICES; '

FROM USER_INDEXES

Este script deve ser executado em um momento em que não haja acesso à base de dados.

Export / Import

O Export e Import da base de dados deve ser feito periodicamente com finalidade de reorganizar os dados dentro da base de dados, desfragmentar e resolver casos de linhas Migradas.

Sugere-se fazer a Exportação / Importação da base de dados de 2 em 2 meses.

Passos para a execução do Export / Import

                      

§  Export da base.

§  Backup físico da base

§  Drop do Schema

§  Import da base

OBS: Semanalmente sugere-se  verificar o número de linhas MIGRADAS na base de dados. Caso o número esteja muito alto, deve-se fazer o Export / Import da base.

Para verificar o número de linhas MIGRADAS utilize:

            SELECT TABLE_NAME, CHAIN_CNT

                        FROM USER_TABLES

ORDER BY CHAIN_CNT DESC

OBS:  A coluna CHAIN_CNT, reflete não apenas o número de linhas migradas, mas o processo de Export / Import irá atacar apenas este problema.

ALERT

O ALERT dever ser verificado diariamente . Deve-se verificar se existem mensagens de erro do tipo ORA-00600 e /ou ORA-01578 , que indicam erros internos do Oracle ou blocos corrompidos.

Caso a mensagem esteja acontecendo, o problema deve ser analisado e solucionado antes que se torne ma

Oracle - Como uso SQL Dinâmico com PL/SQL ?

Como uso SQL Dinâmico com PL/SQL ?

A partir do PL/SQL V2.1 utilize a package DBMS_SQL para executar SQL´s Dinâmicos. Exemplo: CREATE OR REPLACE PROCEDURE DYNSQL AS    cur integer;    rc integer; BEGIN    cur := DBMS_SQL.OPEN_CURSOR;    DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);    rc := DBMS_SQL.EXECUTE(cur);    DBMS_SQL.CLOSE_CURSOR(cur); END; Outro Exemplo: CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS    v_cursor integer;    v_dname char(20);    v_rows integer; BEGIN    v_cursor := DBMS_SQL.OPEN_CURSOR;    DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);    DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);    DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);    v_rows := DBMS_SQL.EXECUTE(v_cursor);    loop       if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then          exit;       end if;       DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);       DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);    end loop;    DBMS_SQL.CLOSE_CURSOR(v_cursor);    EXCEPTION       when others then          DBMS_SQL.CLOSE_CURSOR(v_cursor);          raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm); END;