Tira-teima - Oracle DATE datatype - Como o Oracle armazena esse tipo de dados? Internal representation of the DATE datatype

Olá,

Para quem está começando com Oracle e até para quem já trabalha há anos com o produto, o correto é sempre que tiver uma dúvida, buscar informações na documentação oficial e fazer testes para confirmar como funciona . Leia sobre o tema , teste e depois podemos fazer uma explicação.

Basicamente o Oracle armazena o tipo DATE desta forma : DIA, MES , ANO, HORA, MINUTO E SEGUNDO. A máscara deste tipo vai depender do character set configurado e dos parametros de NLS  como o NLS_DATE_FORMAT. Existem outros tipos de dados para armazenamento de datas e intervalos de tempo como TIMESTAMP e INTERVAL YEAR TO MONTH por exemplo.Eu me recordo de ter escrito sobre eles anos atrás aqui mesmo no blog. Link http://jccorrea.blogspot.com.br/2010/08/oracle-sql-date-e-timestamp-datatypes.html

Abaixo a documentação oficial dos Datatypes no Oracle 

http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT413

Um ótimo artigo sobre o tema usando a função DUMP para ver a quantidade de bytes e como funciona a representação interna http://www.ixora.com.au/notes/date_representation.htm

Threads antigas no ASKTOM sobre datas e comparação.Parece brincadeira mas vejo as pessoas cometerem os mesmos erros de comparaçoes até hoje!!

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2858890655722

Uma das coisas boas da Oracle é que na medida do possível existe uma boa documentação. Já tive que trabalhar com outros produtos e exceto os open sources, você tem dificuldade para encontrar uma boa doc.

Oracle - SQL - Execution Plan - Merge Join Cartesian

Muitas e muitas vezes recebi reclamação dos meus amigos desenvolvedores sobre SQL lento.
Aí quando você pede o SQL pra dar uma olhada e faz um "simples " explain plan,que é simples mas com muitas informações realmente importantes para quem escreve SQL,você encontra um MERGE JOIN CARTESIAN ou coisa do tipo.

Minha experiência.Dificilmente precisei fazer um Cartesian Join na minha vida e mais dificil ainda encontrei alguem que criou o SQL me dizer o motivo "daquilo" ou de aquilo está acontecendo.Na maioria das vezes é falta de condição/predicados de join entre as tabelas.E com uma simples conferencia você acha o erro.Pode existir outras causas remotas,mas em 90 e tantos % das vezes é SQL mal escrito.

Como esta semana recebi mais uma vez  um caso destes ,resolvi postar  o ocorrido e alertar para quem não conhece o principal moitvo do fato ocorrer!

A SQL é usada para manipulação de dados em um banco de dados relacional,sempre tenha definido o que você precisa trazer bem como as condições que você deve "setar" no código para trazer somente o que você realmente precisa.E sempre veja o plano de execução para ver se tem como melhorar!
Escreva o SQL fazendo os ajustes necessários,no final o resultado é bem mais satisfatório!

Oracle SQL - Usando MERGE e error log

Já encontrei muitos códigos SQL onde se poderia usar o comando MERGE para resolver o problema.

Sintaxe do comando MERGE:

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];

O MERGE normalmente é usado nas situações em que já se tem uma tabela populada(alvo) e necessita inserir os registros da tabela origem que ainda não se encontram na tabela alvo.

Digamos que sua tabela alvo foi atualizada semana passada.Então hoje você resolve fazer um MERGE entre as linhas para caso tenha ocorrido alteração na tabela origem que esta seja propagada/efetuada na tabela alvo via UPDATE .

O interessante então no MERGE são as opções WHEN MATCHED THEN usando INSERT ou UPDATE.E também a cláusula DELETE .

Vamos fazer um exemplo de merge usando a tabela PRODUCTS do schema OE.

dbajcc@DBPOST on 24-FEB-10 >desc  oe.products;  Name                                                  Null?    Type  ----------------------------------------------------- -------- ------------------------------------  PRODUCT_ID                                            NOT NULL NUMBER(6)  LANGUAGE_ID                                                    VARCHAR2(3)  PRODUCT_NAME                                                   NVARCHAR2(125)  CATEGORY_ID                                                    NUMBER(2)  PRODUCT_DESCRIPTION                                            NVARCHAR2(2000)  WEIGHT_CLASS                                                   NUMBER(1)  WARRANTY_PERIOD                                                INTERVAL YEAR(2) TO MONTH  SUPPLIER_ID                                                    NUMBER(6)  PRODUCT_STATUS                                                 VARCHAR2(20)  LIST_PRICE                                                     NUMBER(8,2)  MIN_PRICE                                                      NUMBER(8,2)  CATALOG_URL                                                    VARCHAR2(50) dbajcc@DBPOST on 24-FEB-10 >select count(*) from oe.products;   COUNT(*) ----------        288 Elapsed: 00:00:00.01 dbajcc@DBPOST on 24-FEB-10 >

Vamos criar uma tabela cópia da tabela oe.products mas no schema dbajcc

dbajcc@DBPOST on 24-FEB-10 >create table jcc_products   2   as select * from oe.products where rownum=0 ; Table created. Elapsed: 00:00:01.57 dbajcc@DBPOST on 24-FEB-10 >

Vamos inserir exatamente 100 linhas nesta nova tabela,lembrando que na tabela oe.products há 288 linhas :

dbajcc@DBPOST on 24-FEB-10 > dbajcc@DBPOST on 24-FEB-10 >insert into jcc_products   2  select * from oe.products   3  where rownum < 101 ; 100 rows created. Elapsed: 00:00:00.20 dbajcc@DBPOST on 24-FEB-10 >commit ; Commit complete. Elapsed: 00:00:00.01 dbajcc@DBPOST on 24-FEB-10 >

Agora vamos usar o MERGE para inserir os outros 188 registros .

Vamos usar a package DBMS_ERRLOG para criar uma tabela de captura de erros:

dbajcc@DBPOST on 24-FEB-10 >EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('jcc_products', 'jcc_products_log'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.30 dbajcc@DBPOST on 24-FEB-10 >

Usando o Merge:

dbajcc@DBPOST on 24-FEB-10 > dbajcc@DBPOST on 24-FEB-10 >merge into jcc_products jp   2  using oe.products p on (jp.product_id = p.product_id) when not matched then insert   3    4    5  ( jp.language_id, jp.product_name, jp.category_id, jp.product_description, jp.weight_class, jp.warranty_period, jp.supplier_id, jp.product_status, jp.list_price, jp.min_price , jp.catalog_url ) values ( p.language_id,  p.product_name,  p.category_id,  p.product_description,  p.weight_class,  p.warranty_period,  p.supplier_id,  p.product_status,  p.list_price,  p.min_price ,   6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   p.catalog_url                               ); 29 188 rows merged. Elapsed: 00:00:00.13 dbajcc@DBPOST on 24-FEB-10 >commit ; Commit complete. Elapsed: 00:00:00.01 dbajcc@DBPOST on 24-FEB-10 > dbajcc@DBPOST on 24-FEB-10 >SELECT ORA_ERR_MESG$, ORA_ERR_TAG$ from  jcc_products_log ; no rows selected Elapsed: 00:00:00.00 dbajcc@DBPOST on 24-FEB-10 >

Principalmente para quem trabalha com ETL o MERGE é essencial !

Oracle SQL - Usando LEFT and RIGHT JOIN

Olá,

Hoje o assunto é OUTER JOIN.

Essa dica de post eu peguei com o meu amigo Eduardo do blog bieduardo.blogspot.com/ .

Normalmente muitos profissionais caem nesta dúvida cruel.

Como fazer corretamente um OUTER JOIN?

Pirmeiro vamos relembrar o que é um OUTER JOIN.

"Um OUTER JOIN é um SIMPLE JOIN ou INNER JOIN  com uma "opção extendida " de trazer também os dados que não satisfazem a condição de JOIN.

No Oracle para usar LEFT ou RIGHT  OUTER JOIN em seu SQL você pode usar o caracter "+" entre parênteses para informar ao Oracle qual operador você está utilizando.Você também pode usar o padrão SQL92 para JOIN usando as palavras RIGHT OUTER JOIN e LEFT OUTER JOIN.

Mostrarei isso mais a frente no segundo post.

A explicação sobre de qual lado utilizar é simples:

Se a coluna que contém os dados nulos é a da direita você então coloca o sinal (+) na coluna da esquerda e vice-versa.

Ex. Nas tabelas A e B existe a  coluna ID do tipo NUMBER.

Na tabela A existe uma linha em que a coluna ID é nula,mas mesmo assim você precisa trazer esta linha na sua query ,neste caso você precisar fazer um JOIN para obtê - las.

ex.

select 

from tabela1 A,tabela2 B

where A.id = B.id(+) ;

No exemplo acima foi feito um LEFT JOIN pois quero retornar todos os dados da tabela A que não satisfazem a condição da JOIN .

Criando as tabelas de exemplo :

dbajcc@DBPOST on 23-FEB-10 >create table jcc_join_a (seq number(2) ,  text varchar2(10)) ;  2    3 Table created. Elapsed: 00:00:00.03 dbajcc@DBPOST on 23-FEB-10 > insert into jcc_join_a  values(1,'disk1') ;  2 1 row created. Elapsed: 00:00:00.02 dbajcc@DBPOST on 23-FEB-10 > insert into jcc_join_a  values(2,'disk2') ;  2 1 row created. Elapsed: 00:00:00.00 dbajcc@DBPOST on 23-FEB-10 > insert into jcc_join_a(seq)  values(3) ;  2 1 row created. Elapsed: 00:00:00.00 dbajcc@DBPOST on 23-FEB-10 >commit ; Commit complete. Elapsed: 00:00:00.00 dbajcc@DBPOST on 23-FEB-10 > create table jcc_join_b (seq number(2) ,  text varchar2(10)) ;  2    3 Table created. Elapsed: 00:00:00.02 dbajcc@DBPOST on 23-FEB-10 > insert into jcc_join_b  values(1,'disk1') ;    2 1 row created. Elapsed: 00:00:00.01 dbajcc@DBPOST on 23-FEB-10 >  insert into jcc_join_b  values(2,'disk2') ;  2 1 row created. Elapsed: 00:00:00.00 dbajcc@DBPOST on 23-FEB-10 > insert into jcc_join_b(text)  values('disk3') ;  2 1 row created. Elapsed: 00:00:00.00 dbajcc@DBPOST on 23-FEB-10 >

Fazendo um RIGHT JOIN :

dbajcc@DBPOST on 23-FEB-10 >select a.* ,b.*  from jcc_join_a a,       jcc_join_b b where a.seq(+) = b.seq ;  2    3    4        SEQ TEXT              SEQ TEXT ---------- ---------- ---------- ----------          1 disk1               1 disk1          2 disk2               2 disk2                                  disk3 Elapsed: 00:00:00.02 dbajcc@DBPOST on 23-FEB-10 >

Ou usando a sintaxe do padrão SQL92 :

dbajcc@DBPOST on 24-FEB-10 >select  a.* ,b.*   2  from jcc_join_a a right outer join jcc_join_b b   3   on  a.seq = b.seq ;        SEQ TEXT              SEQ TEXT ---------- ---------- ---------- ----------          1 disk1               1 disk1          2 disk2               2 disk2                                  disk3 Elapsed: 00:00:00.00 dbajcc@DBPOST on 24-FEB-10 >

Acima destaquei em azul a coluna na qual é o alvo e que por sinal contém um campo SEQ nulo para o campo TEXT "disk3" .

Agora vamos fazer um LEFT JOIN :

[oracle@marte ~]$ sqlplus dbajcc SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 24 07:49:02 2010 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options dbajcc@DBPOST on 24-FEB-10 > select a.* ,b.*  from jcc_join_a a,       jcc_join_b b where a.seq = b.seq(+) ;  2    3    4        SEQ TEXT              SEQ TEXT ---------- ---------- ---------- ----------          1 disk1               1 disk1          2 disk2               2 disk2          3 Elapsed: 00:00:00.05 dbajcc@DBPOST on 24-FEB-10 >

Ou usando o outro tipo de sintaxe :

dbajcc@DBPOST on 24-FEB-10 >select  a.* ,b.*   2  from jcc_join_a a left outer join jcc_join_b b   3  on  a.seq = b.seq ;        SEQ TEXT              SEQ TEXT ---------- ---------- ---------- ----------          1 disk1               1 disk1          2 disk2               2 disk2          3 Elapsed: 00:00:00.19 dbajcc@DBPOST on 24-FEB-10 >

Temos uma outra opção que é usar a um FULL OUTER JOIN para trazer os dados que não satisfazem a condição de JOIN das duas tabelas envolvidas.

dbajcc@DBPOST on 24-FEB-10 >select  a.* ,b.*   2  from jcc_join_a a full outer join jcc_join_b b   3   on  a.seq = b.seq ;        SEQ TEXT              SEQ TEXT ---------- ---------- ---------- ----------          1 disk1               1 disk1          2 disk2               2 disk2                                  disk3          3 Elapsed: 00:00:00.01 dbajcc@DBPOST on 24-FEB-10 >

Nem vou trazer exemplos sobre Cartesian Join pois acredito ser de pouca utilidade.

Até,

Júlio César Corrêa 

Oracle SQL - Find whitespace- Regular Expressions - Oracle

Procurando espaços em branco em uma coluna :

dbajcc@DBPOST on 23-FEB-10 >create table jcc_regex(x number(1),y varchar2(15)) ; Table created. Elapsed: 00:00:00.02 dbajcc@DBPOST on 23-FEB-10 >insert into jcc_regex   2  values(1,'Os piratas'); 1 row created. Elapsed: 00:00:00.13 dbajcc@DBPOST on 23-FEB-10 >insert into jcc_regex   2  values(2,'Ospiratas'); 1 row created. Elapsed: 00:00:00.00 dbajcc@DBPOST on 23-FEB-10 >commit ; Commit complete. Elapsed: 00:00:00.01 dbajcc@DBPOST on 23-FEB-10 >

dbajcc@DBPOST on 23-FEB-10 >select x,regexp_count(y,'(\s)') whitespace from jcc_regex ;          X WHITESPACE ---------- ----------          1          1          2          0 Elapsed: 00:00:00.01 dbajcc@DBPOST on 23-FEB-10 >

Se por acaso vc está usando um campo CHAR e por um motivo qualquer você precise fazer um replace ,use o REGEXP_REPLACE.

Você tem uma lista que fica na doc Database Advanced Application Developer's Guide.Tem vários exemplos que podem ajudar você .

Para obter das docs :

tahiti.oracle.com

Resposta a uma questão sobre Materialized view e External Table

Olá,

Estes dias surgiu um pergunta no grupo de discussão Oracle_Br,uma pergunta de uma pessoa que estava estudando sobre Performance Tunning.
A questão era sobre criar uma Materialized View sobre uma External Table.
Não sei porque e nem como surgiu esta questão ,mas resolvi fazer um teste para ver no que dava.Encontrei um post no forum internacional em que Donald Burleson cita que nunca fez isso,mas afirmava que o que poderia dar problema no ponto de vista de arquitertura do Oracle é a atualização(refresh) desta MVIEW.
O unico ponto interessante que achei nesta questão seria o fato de que se pudesse gerar um arquivo externo novo,digamos que seja uma arquivo diario e que a MVIEW automaticamente fizesse o refresh dos dados.

Abaixo os codigos da criação da external table e da materialized view que enviei para o forum para exemplo:

Segue os testes que acabei de fazer a respeito da questão.

Está mal formatado porque eu copiei e colei,mas dá para seguir a logica e os comandos

- Criei o arquivo .txt

[oracle@TAHITI dir_work]$ vi test_ext_t
[oracle@TAHITI dir_work]$ ls

- Criei o diretorio

SQL> create directory dir_work as '/u02/oradata/lab/dir_work'
2 ;

Directory created.

-Concedi as permissões
SQL> grant read,write on directory dir_work to public;

Grant succeeded.

Criei a tabela externa com base no arquivo e no diretorio
SQL> create table test_ext
2 (name varchar2(15),
3 sobrenome varchar2(15),
4 idade number(2))
5 organization external
6 (default directory dir_work
7 access parameters
8 ( records delimited by newline
fields terminated by '|'
9 10 )
11 location('test_ext_t.txt')
12 );

Table created.

-Alterei a tabela porque troquei as palavras chaves rs
SQL> alter table test_ext
2 access parameters
3 ( records delimited by newline
4 fields terminated by '|'
5 );

Table altered.

-Alterei o nome do arquivo,senão.. "don't work"
[oracle@TAHITI dir_work]$ mv text_ext_t.txt test_ext_t.txt

[oracle@TAHITI dir_work]$ exit
exit

- Testei uma consulta na rabela externa
SQL> select * from test_ext;

NAME SOBRENOME IDADE
--------------- --------------- ----------
julio correa 22

SQL>

Criei uma mview de teste
SQL> create materialized view test_mv_on_ext
2 build immediate
3 as select * from test_ext;

Materialized view created.

-Testei a query na mview criada anteriormente
SQL>
SQL> select * from test_mv_on_ext;

NAME SOBRENOME IDADE
--------------- --------------- ----------
julio correa 22

-Outro teste de criação de mview

-Tentativa de refresh na mview
SQL> begin
2 dbms_refresh('test_mv_on_ext2');
3 end;
4

Esta ultima ficou executando,mas não me retornou nada,nem mesmo errro.

O problema deve estarna hora do refresh.Por se tratar de um arquivo,caso você gere outro arquivo com o mesmo nome no diretorio, o Oracle não saberá disto.
Não sei se isto funciona,nunca fiz.Mais vou tentar um refresh ok?

Abs,

Julio Cesar Correa

Profiles : PASSWORD_LOCK_TIME and FAILED_LOGIN_ATTEMPTS

Olá,

Olá como vai tudo bem?Olá como vai tudo bem?...

Pergunto: O que é um PROFILE(perfil) ?

Reposta simplificada:

" Um mecanismo que o Oracle fornece para controlarmos recursos e também limites por usuários.Um PROFILE é um conjunto de limites de recursos."

View : DBA_PROFILES .

Vamos testar dois parâmetros muito interessantes quando usamos PROFILES:

PASSWORD_LOCK_TIME e FAILED_LOGIN_ATTEMPTS .

Vou mostrar na prática,é mais legal!

Os testes serão no Ubuntu 8.10 com Oracle 10g XE (ainda não saiu o 11g rs) em VmWare Workst.

Vamos ao sqlplus:

Criando um profile:

SQL> create profile plcktime_failogatt limit
2 password_lock_time 5/1440
3 failed_login_attempts 2 ;

Profile created.

Criando um usuário para teste:

SQL> create user naum_loga identified by naum_loga
2 profile plcktime_failogatt ;

User created.

Concedendo privilégios:

SQL> grant connect,create session to naum_loga;

Grant succeeded.

SQL>

Verificando na view DBA_USERS:


SQL> select username,account_status,profile
2 from dba_users
3 where username ='NAUM_LOGA';

USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
NAUM_LOGA OPEN PLCKTIME_FAILOGATT

Com as cobaias criadas agora , então vamos testar?

SQL> disco
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> conn naum_loga/senha_errada
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> conn naum_loga/senha_errada
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> conn naum_loga/senha_errada
ERROR:
ORA-28000: the account is locked

Humm. O FAILED_LOGIN_ATTEMPTS funcionou. Mas ,e o parametro PASSWORD_LOCK_TIME ?

SQL> conn sys / as sysdba
Enter password:
Connected.
SQL> select username,account_status from dba_users
2 where username='NAUM_LOGA';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
NAUM_LOGA LOCKED(TIMED)

SQL>

Viram a palavra "TIMED" na coluna account_status?

Ok.

Vamos calcular o tempo?

SQL> alter session set nls_date_format='dd/mm/rrrr,hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
28/02/2009,22:56:49
Agora só nos resta esperar!

Você leitor vai se safar desta hein rs.

(Locutor)

- Cinco minutos depois...

(DBA)

- Fui tomar uma água na cozinha.

Let me see:

SQL> select sysdate from dual;

SYSDATE
-------------------
28/02/2009,23:04:02

SQL>

Dá e sobra rs.

SQL> disco
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> conn naum_loga/naum_loga
Connected.

SQL> select 'eu estou logado' naum_loga from dual;

NAUM_LOGA
---------------------------------------------
eu estou logado

SQL>

Vamos exterminar nossas cobaias?

SQL> disco
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> conn sys / as sysdba
Enter password:
Connected.
SQL> drop profile PLCKTIME_FAILOGATT;
drop profile PLCKTIME_FAILOGATT
*
ERROR at line 1:
ORA-02382: profile PLCKTIME_FAILOGATT has users assigned, cannot drop without CASCADE

Why?
Ok.I got it.

SQL> drop user naum_loga;

User dropped.

SQL> drop profile PLCKTIME_FAILOGATT;

Profile dropped.

SQL> disco
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL>

Boa noite a todos,

Júlio César Corrêa