Oracle - Executar procedure

Para executar uma procedure via prompt SQL ou SQLDEVELOPER deve-se antes criar as variáveis que serão retornadas. Algo como:

variable cur refcursor;  -- Variável com o cursor de resposta
execute minha_procedure(:cur);
print cur;

Exemplo:

variable cur refcursor;

execute PR_SE_TB_FLUXO_CHAMADA_2(:cur);

print cur;

Oracle - Atualizar Estatísticas do All_Tables

Para atualizar as informações do all_tables, pode-se utilizar os pacotes:

call dbms_stats.gather_table_stats(<schema>,<tabela>);
call dbms_stats.gather_schema_stats(<schema>);

Oracle/PL-SQL - Delete com BULK

Para operações de delete em grandes tabelas, uma opção é utilizar o conceito de BULK o que garante não só uma melhor performance no DELETE com também diminui a quantidade de controles por causa dos COMMITs. Um exemplo seria:

CREATE OR REPLACE PROCEDURE PR_PURGE IS

CURSOR cdr_cur IS
SELECT ROWID FROM <TABELA> c where <CONDICAO>;

TYPE cdr_data IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
cdr cdr_data;

BEGIN
OPEN cdr_cur;
LOOP
     cdr.DELETE;

     FETCH cdr_cur BULK COLLECT INTO cdr LIMIT <LIMITE>;  --LIMITE

     IF cdr.COUNT>0 THEN
          FORALL j IN 1..cdr.COUNT
          DELETE FROM <TABELA> WHERE ROWID=cdr(j);
          COMMIT;
     ELSE
          EXIT;
     END IF;
END LOOP;

CLOSE cdr_cur;
COMMIT;

EXCEPTION
     WHEN OTHERS THEN
          IF cdr_cur%ISOPEN THEN CLOSE cdr_cur; END IF;
     
END;

onde:
<TABELA> - Tabela que será manipulada
<CONDICAO> - Condição de pesquisa/seleção
<LIMITE> - Quantidade de registros transacionados por vez

exemplo:

CREATE OR REPLACE PROCEDURE PR_PURGE IS

CURSOR cdr_cur IS
SELECT ROWID FROM TABELA_A c where c.DATA > '20141201';

TYPE cdr_data IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
cdr cdr_data;

BEGIN
OPEN cdr_cur;
LOOP
     cdr.DELETE;

     FETCH cdr_cur BULK COLLECT INTO cdr LIMIT 100000;

     IF cdr.COUNT>0 THEN
          FORALL j IN 1..cdr.COUNT
          DELETE FROM TABELA_A WHERE ROWID=cdr(j);
          COMMIT;
     ELSE
          EXIT;
     END IF;
END LOOP;

CLOSE cdr_cur;
COMMIT;

EXCEPTION
     WHEN OTHERS THEN
          IF cdr_cur%ISOPEN THEN CLOSE cdr_cur; END IF;
     
END;

Oracle - Alteração de TableSpace

Para alterar o TableSpace de uma tabela:

ALTER TABLE NomeDaTabela MOVE TABLESPACE NomeNovoTablespace;  

Após a movimentação o(s) índice(s) deve(m) ser re-criado(s):

ALTER INDEX IndexName REBUILD COMPUTE STATISTICS; 

Para alterar o TableSpace de um Índice:

ALTER INDEX NomeDaTabela REBUILD TABLESPACE NomeNovoTablespace;  

Para gerar um script de alteração, pode-se usar:

SELECT 'ALTER TABLE NomeDoEsquema.' || table_name ||   
       ' MOVE TABLESPACE NomeNovoTablespace;'  
FROM dba_tables  
WHERE owner = 'NomeDoEsquema';  
   
SELECT 'ALTER INDEX NomeDoEsquema.' || index_name ||   
       ' REBUILD TABLESPACE NomeNovoTablespace;'  
FROM dba_indexes  
WHERE owner = 'NomeDoEsquema'   
AND index_type != 'LOB';

Dica obtida em: http://www.pierin.com/Publicacoes-13-banco_de_dados-.html

PL/SQL - Diferença entre Datetime, em Segundos

Para obter a diferença, em segundos, entre duas datas (datetime), deve-se, após fazer a subtração (diferença) entre elas,  efetuar a devida conversão, conforme a fórmula:

round(abs(diferença) * 24 * 60 * 60)

onde diferença: data1 - data2

Exemplo:

round(abs(data_final - data_inicial) * 24 * 60 * 60) as dif_seg

Oracle - SQL Plus - Tabs

A ferramenta SQL*Plus da Oracle tem por default, substituir diversos espaços/brancos (spaces) de um resultado de uma query por tabs (\t) vide http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm#i2683445

"SET TAB {ON | OFF}
Determines how SQL*Plus formats white space in terminal output. OFF uses spaces to format white space in the output. ON uses the TAB character. TAB settings are every eight characters. The default value for TAB is system dependent."

Para desativar isto, deve-se colocar antes da execução do SELECT a opção SET TAB OFF

Para outros parâmetros do SQL*Plus, consulte  SET System Variable Summary (http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm)

Oracle - Sessions

Para visualizar as sessões do Oracle, podemos utilizar a view V$SESSION.

Exemplo:

select sid, status from V$SESSION;

Para mais informações:

http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2088.htm

Programas C : Acessar banco de dados Oracle - Linux

Pré-requisitos:
* Obrigatórios (x86 - http://www.oracle.com/technetwork/topics/linuxsoft-082809.html e x86_64 - http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html)

• Oracle Instant Client Package - Basic (All files required to run OCI, OCCI, and JDBC-OCI applications) - Bibliotecas que permitem o acesso ao banco oracle
• Oracle Instant Client Package - SDK (Additional header files and an example makefile for developing Oracle applications with Instant Client) - Arquivos source/headers para usar nos programas "C"

* Opcionais

• Oracle Instant Client Package - SQL*Plus(Additional libraries and executable for running SQL*Plus with Instant Client) - Possibilita a conexão ao Oracle através do SQL *PLUS

TNSNAME

Deve existir o arquivo TNSNAME com as conexões que serão utilizadas. Este arquivo normalmente deverá ficar em /etc/tnsnames.ora

Conector C - Oracle

Ocilib - http://orclib.sourceforge.net/
Após efetuar o download e descompactar o arquivo, use esta dica importante para a instalação:
sudo ./configure --with-oracle-headers-path=<caminho para onde estão os includes do sdk oracle> --with-oracle-home=<caminho do oracle home/instant client onde estão as bibliotecas do oracle>

Exemplo:
sudo ./configure --with-oracle-headers-path=/usr/lib/instantclient_10_2/sdk/include --with-oracle-home=/usr/lib/instantclient_10_2/

Depois:
sudo make
sudo make install

Importante: as variáveis de ambiente ORACLE_HOME e LD_LIBRARY_PATH devem estar setadas !
ORACLE_HOME=<caminho do oracle home/instant client onde estão as bibliotecas do oracle>
LD_LIBRARY_PATH=<caminho do oracle home/instant client onde estão as bibliotecas do oracle>:<caminho onde foram instaladas as bibliotecas do ocilib)
USER_LIBS=<caminho onde está instalado o ocilib>


Exemplos:
(32 bits)

ORACLE_HOME=/usr/lib/instantclient_10_2
LD_LIBRARY_PATH=/usr/lib/instantclient_10_2:/usr/local/lib

(64 bits)

ORACLE_HOME=/usr/lib/oracle/10.2.0.4/client64
LD_LIBRARY_PATH=/usr/lib/oracle/10.2.0.4/client64/lib:/usr/local/lib:/usr/local/lib64

USER_LIBS=/usr/local

Para compilar/linkeditar programas

Os seguintes parâmetros deverão ser usados
Compilação:
(32 bits)
-I$USER_LIBS/include 
Linkedição
-L/$ORACLE_HOME -lclntsh -L$USER_LIBS/lib -locilib

Exemplo via linha de comando:
gcc conecta_oracle.c -o conecta_oracle -I$USER_LIBS/include -L/$ORACLE_HOME -lclntsh -L$USER_LIBS/lib -locilib


(64 bits)
Os seguintes parâmetros deverão ser usados
Compilação:
-I$USER_LIBS/include 
Linkedição
-L$ORACLE_HOME/lib -lclntsh -L$USER_LIBS/lib -locilib

Exemplo via linha de comando:
(32 bits)
gcc conecta_oracle.c -o conecta_oracle -I$USER_LIBS/include -L$ORACLE_HOME -lclntsh -L$USER_LIBS/lib -locilib

(64 bits)

gcc conecta_oracle.c -o conecta_oracle -I$USER_LIBS/include -L$ORACLE_HOME/lib -lclntsh -L$USER_LIBS/lib -locilib

Exemplo de programa "C" para acessar banco de dados oracle:

#include "ocilib.h"

int main(void)
{
    OCI_Connection *cn;
    OCI_Statement* st;
    OCI_Resultset* rs;

    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
        return EXIT_FAILURE;

    cn = OCI_ConnectionCreate("VEDBD02_205", "usr_r2s2", "r2s2", OCI_SESSION_DEFAULT);

    printf("Server major    version : %i\n",   OCI_GetServerMajorVersion(cn));
    printf("Server minor    version : %i\n",   OCI_GetServerMinorVersion(cn));
    printf("Server revision version : %i\n\n", OCI_GetServerRevisionVersion(cn));
    printf("Connection      version : %i\n\n", OCI_GetVersionConnection(cn));

    st = OCI_StatementCreate(cn);

    OCI_ExecuteStmt(st, "select id_sdr, dat_ini_vig, dat_fim_vig, vlr_sdr, dat_sdr from tb_groa_valor_sdr order by 1");

    rs = OCI_GetResultset(st);

    while (OCI_FetchNext(rs))
    {
        printf("%i - %s - %s - %s - %s\n", OCI_GetInt(rs, 1), 
            OCI_GetString(rs,2), OCI_GetString(rs,3),
            OCI_GetString(rs,4), OCI_GetString(rs,5));
    }

    OCI_Cleanup();

    return EXIT_SUCCESS;
}

Oracle - Problemas com o Start do Listener

Se o serviço de Listener do Oracle não estiver 'startando' uma boa forma de tentar descobrir o que acontece é usando o programa lsnrctl via prompt, e digitando o comando start. Ele exibirá então na tela, caso ocorra algum erro, os arquivos de parâmetros que estão sendo lidos para o start do Listener.

Em tempo, normalmente são os parâmetros configurados nos arquivos listener.ora, sqlnet.ora e tnsnames.ora que ficam dentro do subdiretório Network/Admin

Resumindo:
lsnrctl
start