Upgrade to Oracle 11gr2 - Para quem não sabe por onde começar

Abaixo uma figura que ilustra os simples passos para o processo de upgrade!
Esta é uma das tarefas mais delicadas,pois envolverá o seu ambiente de produção.

Esqueça ir direto para o passo 5 please!
So , take easy e take care with your  database !

Abaixo o link da documentação(em .pdf) do Oracle Database 11gr2 que fala sobre o processo de migração de uma versão anterior para o 11gr2,mais conhecido como upgrade kk.

http://download.oracle.com/docs/cd/E11882_01/server.112/e10819.pdf

Tem um post ótimo sobre isso do meu amigo Eduardo Legatti  aqui .

Att,

Júlio César Corrêa

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 Database 11g Essentials for Implementors (Beta)

Para quem trabalha com parceiros Oracle é uma boa este exame :

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_514

Lembrando que vale para as duas versões!

"This exam is valid for Oracle Database 11gR1 and 11gR2."

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

Comentários interessantes sobre realizar um exame de certificação

O assunto é sobre o exame 1z0-047 - SQL Expert .

http://laurentschneider.com/wordpress/2007/07/oracle-certified-sql-expert.html#comments

Oracle Database 11gr2 - MEMORY_TARGET e /dev/shm

Olá,

Continuando os testes com o Oracle Database 11gr2 conforme o tempo livre que tenho,fiz alguns testes diminuindo a quantidade de memória RAM da VM .Como estava com o Oracle Restart habilitado,após subir o servidor de banco de dados logo teria os serviços disponíveis para uso.

[oracle@marte ~]$ [oracle@marte ~]$ echo $ORACLE_HOME /u01/app/oracle/product/11.2.0/dbhome_1 [oracle@marte ~]$ echo $ORACLE_SID dbpost [oracle@marte ~]$ ps -ef|grep pmon oracle    2997     1  0 08:42 ?        00:00:00 asm_pmon_+ASM oracle    3118     1  0 08:43 ?        00:00:00 ora_pmon_dbpost oracle    4083  3444  0 09:58 pts/2    00:00:00 grep pmon [oracle@marte ~]$ ps -ef|grep tns oracle    2908     1  0 08:41 ?        00:00:00 /u01/app/oracle/product/11.2.0/grid/bin/tnslsnr LISTENER -inherit oracle    4086  3444  0 09:59 pts/2    00:00:00 grep tns [oracle@marte ~]$

Bom parece que o banco de dados está disponível

[oracle@marte ~]$ [oracle@marte ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 23 10:02:19 2010 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to an idle instance. idle on 23-FEB-10 > idle on 23-FEB-10 >select * from v$instance; select * from v$instance * ERROR at line 1: ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0 Elapsed: 00:00:00.17 idle on 23-FEB-10 >

Há algo errado.

Vamos ao alert log

[oracle@marte /]$ cd /u01/app/oracle/diag/rdbms/dbpost/dbpost/alert/ [oracle@marte alert]$ ls -ltrh total 6.6M -rw-r----- 1 oracle dba 6.6M Feb 23 09:36 log.xml [oracle@marte alert]$ [oracle@marte alert]$ tail -20f log.xml </msg> <msg time='2010-02-23T09:36:06.048-02:00' org_id='oracle' comp_id='rdbms'  msg_id='opistr_real:935:3971575317' type='NOTIFICATION' group='startup'  level='16' host_id='marte' host_addr='127.0.0.1'  pid='3479'>  <txt>Starting ORACLE instance (normal)  </txt> </msg> <msg time='2010-02-23T09:36:06.075-02:00' org_id='oracle' comp_id='rdbms'  type='UNKNOWN' level='16' host_id='marte'  host_addr='127.0.0.1' pid='3479'>  <txt>WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 536870912 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 322220032 and used is 474198016 bytes. Ensure that the mount point is /dev/shm for this directory.  </txt> </msg> <msg time='2010-02-23T09:36:06.092-02:00' org_id='oracle' comp_id='rdbms'  type='UNKNOWN' level='16' host_id='marte'  host_addr='127.0.0.1' pid='3479'>  <txt>memory_target needs larger /dev/shm  </txt> </msg>

Analisando o erro acima vamos ver o filesystem /dev/shm

[root@marte /]# df -Ph Filesystem            Size  Used Avail Use% Mounted on /dev/sda1              23G   15G  6.7G  70% / tmpfs                 760M  453M  308M  60% /dev/shm [root@marte /]# cat /proc/meminfo MemTotal:      1555508 kB MemFree:        315880 kB Buffers:         64700 kB Cached:         968316 kB SwapCached:          0 kB Active:         423800 kB Inactive:       769132 kB HighTotal:      655296 kB HighFree:         1168 kB LowTotal:       900212 kB LowFree:        314712 kB SwapTotal:     1574360 kB SwapFree:      1574360 kB Dirty:             468 kB Writeback:           0 kB AnonPages:      159928 kB Mapped:          91568 kB Slab:            29760 kB PageTables:       9068 kB NFS_Unstable:        0 kB Bounce:              0 kB CommitLimit:   2352112 kB Committed_AS:  1866740 kB VmallocTotal:   114680 kB VmallocUsed:      3280 kB VmallocChunk:   111252 kB HugePages_Total:     0 HugePages_Free:      0 HugePages_Rsvd:      0 Hugepagesize:     4096 kB [root@marte /]#

Vamos ver o tamanho alocado no paramtreo MEMORY_TARGET :

[oracle@marte /]$ cd $ORACLE_HOME/dbs [oracle@marte dbs]$ vi initdbpost.ora ... *.memory_target=512m *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1'

Ok.

O erro está ocorrendo porque não temos 512MB disponíveis no /dev/shm .Isto ocorre porque como não configurei o /dev/shm e tenho somente 1536MB de RAM alocado para esta VM.

Vou retirar totalmente o banco de dados dbpost do Oracle Restart :

[oracle@marte dbs]$ cd / [oracle@marte /]$ srvctl disable database -d dbpost [oracle@marte /]$

[root@marte /]# ps -ef|grep pmon oracle    2997     1  0 08:42 ?        00:00:00 asm_pmon_+ASM root      4774  4362  0 11:39 pts/2    00:00:00 grep pmon [root@marte /]#

Para efeito de testes não quero alterar o tamanho da memória RAM.Vamos alterar o /dev/shm  que ainda está com valores default no meu SO.

Pode-se fazer via comando direto no shell/terminal,mas no caso para ficar definitivo após o boot vou colocá-lo no /etc/fstab .

root@marte /]# cat /etc/fstab LABEL=/                 /                       ext3    defaults        1 1 tmpfs                   /dev/shm                tmpfs   defaults        0 0 devpts                  /dev/pts                devpts  gid=5,mode=620  0 0 sysfs                   /sys                    sysfs   defaults        0 0 proc                    /proc                   proc    defaults        0 0 LABEL=SWAP-sda2         swap                    swap    defaults        0 0 [root@marte /]#

Fazendo o remount do /dev/shm

[root@marte /]# mount -o remount,size=1G /dev/shm [root@marte /]#

Alocando o espaço para ficar disponível na inicialização do sistema :

[root@marte /]# vi /etc/fstab [root@marte /]# cat /etc/fstab LABEL=/                 /                       ext3    defaults        1 1 tmpfs                   /dev/shm                tmpfs   defaults,size=1024m,auto        0 0 devpts                  /dev/pts                devpts  gid=5,mode=620  0 0 sysfs                   /sys                    sysfs   defaults        0 0 proc                    /proc                   proc    defaults        0 0 LABEL=SWAP-sda2         swap                    swap    defaults        0 0 [root@marte /]#

 reboot :

[root@marte /]# shutdown -r now Broadcast message from root (pts/2) (Tue Feb 23 11:50:40 2010): The system is going down for reboot NOW! [root@marte /]#

Curiosidade  - Oracle Restart em ação

[root@marte ~]# tail -f /var/log/messages Feb 23 11:52:34 localhost smartd[2568]: Device: /dev/sde, IE (SMART) not enabled, skip device Try 'smartctl -s on /dev/sde' to turn on SMART features Feb 23 11:52:34 localhost smartd[2568]: Unable to register SCSI device /dev/sde at line 36 of file /etc/smartd.conf ... Feb 23 11:52:37 localhost pcscd: winscard.c:219:SCardConnect() Reader E-Gate 0 0 Not Found Feb 23 11:52:37 localhost last message repeated 3 times Feb 23 11:52:38 localhost /u01/app/oracle/product/11.2.0/grid/bin/crswrapexece.pl[2552]: executing "/u01/app/oracle/product/11.2.0/grid/bin/ohasd.bin reboot"

Após o boot total,vamos tentar subir a instance dbpost.

Abaixo podemos ver que o /dev/shm está com a tamanho que foi colocado no /etc/fstab

[root@marte ~]# df -Ph Filesystem            Size  Used Avail Use% Mounted on /dev/sda1              23G   15G  6.7G  70% / tmpfs                 1.0G  154M  871M  16% /dev/shm [root@marte ~]#

Startup no dbpost :

[oracle@marte dbs]$ ps -ef|grep pmon oracle    2994     1  0 11:53 ?        00:00:00 asm_pmon_+ASM oracle    3124  2948  0 12:00 pts/2    00:00:00 grep pmon [oracle@marte dbs]$ strings spfiledbpost.ora |grep memory *.memory_target=512m [oracle@marte dbs]$ srvctl start database -d dbpost -o open PRCR-1079 : Failed to start resource ora.dbpost.db CRS-2501: Resource 'ora.dbpost.db' is disabled [oracle@marte dbs]$ [oracle@marte dbs]$ [oracle@marte dbs]$ echo $ORACLE_HOME /u01/app/oracle/product/11.2.0/dbhome_1 [oracle@marte dbs]$ echo $ORACLE_SID dbpost [oracle@marte dbs]$ sqlplus "/ as sysdba" SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 23 12:03:58 2010 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to an idle instance. idle on 23-FEB-10 >startup nomount ; ORACLE instance started. Total System Global Area  535662592 bytes Fixed Size                  1337720 bytes Variable Size             364906120 bytes Database Buffers          163577856 bytes Redo Buffers                5840896 bytes idle on 23-FEB-10 >alter database mount ; Database altered. Elapsed: 00:00:05.45 idle on 23-FEB-10 >alter database open; Database altered. Elapsed: 00:00:22.07 idle on 23-FEB-10 >exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@marte dbs]$ sqlplus "/ as sysdba" SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 23 12:05:57 2010 Copyright (c) 1982, 2009, Oracle.  All rights reserved. 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 sys@DBPOST on 23-FEB-10 >select open_mode from v$database ; OPEN_MODE -------------------- READ WRITE Elapsed: 00:00:00.03 sys@DBPOST on 23-FEB-10 >exit Disconnected from 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 [oracle@marte dbs]$

Links para maiores informações :

http://www.cyberciti.biz/tips/what-is-devshm-and-its-practical-usage.html

http://www.oracle-base.com/articles/linux/LargeSGAOnLinux.php

http://kevinclosson.wordpress.com/2007/08/24/oracle11g-automatic-memory-management-part-ii-automatically-stupid/

metalink.oracle.com/ - Note ID 260152.1

Abs,

Júlio César Corrêa

Oracle Database - Datatype Number - Precisão máxima

Estes dias li um post muito legal do http://laurentschneider.com/ ,sempre leio bem os posts dele pois ele sempre procura coisas "escondidas" dentro do Oracle .Acho interessante a visão dele principalmente nos testes com SQL.

O datatype NUMBER tem algumas coisas interessantes para ser observadas.

Vamos lá!

[oracle@marte ~]$ sqlplus  dbajcc SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 22 13:10:53 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 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 >create table jcc_numx (xnum number(39)) ; create table jcc_numx (xnum number(39))                                    * ERROR at line 1: ORA-01727: numeric precision specifier is out of range (1 to 38) Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >create table jcc_numx (xnum number(38)) ; Table created. Elapsed: 00:00:00.03 dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >desc jcc_numx ;  Name                                                  Null?    Type  ----------------------------------------------------- -------- ------------------------------------  XNUM                                                           NUMBER(38) dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >--insere 38 digitos insert into jcc_numx values(10000000000000000000000000000000000000) ; dbajcc@DBPOST on 22-FEB-10 >  2 1 row created. Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >--insere 39 digitos insert into jcc_numx values(100000000000000000000000000000000000000); dbajcc@DBPOST on 22-FEB-10 >  2 values(100000000000000000000000000000000000000)        * ERROR at line 2: ORA-01438: value larger than specified precision allowed for this column Elapsed: 00:00:00.02 dbajcc@DBPOST on 22-FEB-10 > commit ;dbajcc@DBPOST on 22-FEB-10 > Commit complete. Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >select xnum,length(xnum) from jcc_numx ;       XNUM LENGTH(XNUM) ---------- ------------ 1.0000E+37           38 Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >create table jcc_numx2 (xnum number) ; Table created. Elapsed: 00:00:00.02 dbajcc@DBPOST on 22-FEB-10 >desc jcc_numx2;  Name                                                  Null?    Type  ----------------------------------------------------- -------- ------------------------------------  XNUM                                                           NUMBER dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >--insere 38 digitos insert into jcc_numx2 values(10000000000000000000000000000000000000) ; commit ; dbajcc@DBPOST on 22-FEB-10 >  2 1 row created. Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 > Commit complete. Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 >--insere 39 digitos insert into jcc_numx2 values(100000000000000000000000000000000000000); commit ;dbajcc@DBPOST on 22-FEB-10 >  2 1 row created. Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 > Commit complete. Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 >--insere um googol insert into jcc_numx2 values(10000000000000000000000000000000000000000000000000000000000000 000000000000000000000000000000000000000) ; commit ;dbajcc@DBPOST on 22-FEB-10 >  2 1 row created. Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 > Commit complete. Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >select xnum,length(xnum) from jcc_numx2 ;       XNUM LENGTH(XNUM) ---------- ------------ 1.0000E+37           38 1.0000E+38           39 1.000E+100           40 Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >insert into jcc_numx2 values(11111111111111111111111111111111111111111111111111111111111111 11111111111111111111111111111111111111) ;  2 1 row created. Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >select xnum,length(xnum) from jcc_numx2 ;       XNUM LENGTH(XNUM) ---------- ------------ 1.0000E+37           38 1.0000E+38           39 1.000E+100           40 1.1111E+99           40 Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >commit ; Commit complete. Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 >create table jcc_numx3 (xnum number(*)) ; Table created. Elapsed: 00:00:00.04 dbajcc@DBPOST on 22-FEB-10 >desc jcc_numx3 ;  Name                                                  Null?    Type  ----------------------------------------------------- -------- ------------------------------------  XNUM                                                           NUMBER dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >--insere 126 casas dbajcc@DBPOST on 22-FEB-10 >insert into jcc_numx3 values(111111111111111111111111111111111111111111111111111111111111111111111 111111111111111111111111111111111111111111111111111111111) ; commit ;   2  --insere 127 casas insert into jcc_numx3 values(1111111111111111111111111111111111111111111111111111111111111111111111 111111111111111111111111111111111111111111111111111111111) ; select xnum,length(xnum) from jcc_numx3 ; 1 row created. Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 > Commit complete. Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 >  2  values(11111111111111111111111111111111111111111111111111111111111111111111111 11111111111111111111111111111111111111111111111111111111)        * ERROR at line 2: ORA-01426: numeric overflow Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 >       XNUM LENGTH(XNUM) ---------- ------------ 1.111E+125           40 Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >

Disabling Oracle Restart - 11gr2

Fazendo alguns testes com o Oracle Database 11gr2 surgiu a necessidade de desabilitar o Oracle Restart para um banco de dados.

Para fazer isso utilize dentro da $ORACLE_HOME/bin do GRID o SRVCTL :

Ex.

Linha de comando do SRVCTL

[oracle@marte grid]$ cd bin/ [oracle@marte bin]$ pwd /u01/app/oracle/product/11.2.0/grid/bin [oracle@marte bin]$ [oracle@marte bin]$ ./srvctl Usage: srvctl <command> <object> [<options>]     commands: enable|disable|start|stop|status|add|remove|modify|getenv|setenv|unsetenv|config     objects: database|service|asm|diskgroup|listener|home|ons|eons For detailed help on each command and object and its options use:   srvctl <command> -h or   srvctl <command> <object> -h [oracle@marte bin]$

Verificando o banco de dados :

[oracle@marte bin]$ srvctl status database -d dbpost Database is running. [oracle@marte bin]$

Exibindo os banco de dados gerenciados pelo Oracle Restart

[oracle@marte bin]$ srvctl config database dbpost [oracle@marte bin]$

Verificando a configuração de um banco de dados específico ,no caso dbpost :

[oracle@marte bin]$ [oracle@marte bin]$ srvctl config database -d dbpost Database unique name: dbpost Database name: Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1/ Oracle user: oracle Spfile: Domain: riversideti.com.br Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Disk Groups: Services: dbpost [oracle@marte bin]$

No comando abaixo as opções "-y MANUAL " e "-s NOMOUNT" refere-se a como o Oracle Restart tratará este objeto,com a opção MANUAL o Oracle Restart não tentará subir o banco de dados quando o servidor reiniciar ,mas somente em caso de falha e a opção NOMOUNT será utilizada caso ocorra uma falha o Oracle tentará subir a instance como NOMOUNT.

[oracle@marte bin]$ [oracle@marte bin]$ srvctl modify database -d dbpost -y MANUAL -s NOMOUNT [ENTER] [oracle@marte bin]$

Caso queira retirar o banco de dados da lista do Oracle Restart use a seguinte opção :

srvctl disable database -d database_name [ENTER]

Até,


Júlio César

Oracle Magazine - Março/Abril

Pessoal,

Link para a revista digital : http://www.oraclemagazine-digital.com/oraclemagazine/20100304/?sub_id=BhKhISAYULmr8

Obs .É possível fazer o download .

Abs,

JC

SQL - INTERVAL YEAR TO MONTH e DAY TO SECOND Datatypes

A precisão máxima deste datatype são de 2 digitos para ano e 2 para mês.

Poderiamos usar por exemplo para armazenar vigências de determinado garantia por exemplo.
Comprei um aparelho de barbear e a garantia dele é de por exemplo 1 ano de 6 meses.Uma "super garantia" .

Login:

[oracle@marte ~]$ ps -ef|grep pmon oracle    2997     1  0 08:03 ?        00:00:01 asm_pmon_+ASM oracle    3115     1  0 08:05 ?        00:00:01 ora_pmon_dbpost oracle    5943  5892  0 11:51 pts/1    00:00:00 grep pmon [oracle@marte ~]$ sqlplus dbajcc@dbpost SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 12 11:51:26 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 12-FEB-10 >

Criando a tabela :

dbajcc@DBPOST on 12-FEB-10 >create table super_shavers   2  (name varchar2(30),warranty interval year to month ); Table created. Elapsed: 00:00:00.08 dbajcc@DBPOST on 12-FEB-10 >

Inserindo dados na tabela super_shavers :

dbajcc@DBPOST on 12-FEB-10 >insert into super_shavers   2  values   3  ('shaver_100',to_yminterval('01-06')) ; 1 row created. Elapsed: 00:00:00.02 dbajcc@DBPOST on 12-FEB-10 > dbajcc@DBPOST on 12-FEB-10 >insert into super_shavers   2  values   3  ('shaver_200',to_yminterval('01-00')) ; 1 row created. Elapsed: 00:00:00.01 dbajcc@DBPOST on 12-FEB-10 >commit ; Commit complete. Elapsed: 00:00:00.00 dbajcc@DBPOST on 12-FEB-10 >

Bom, comprei o barbeador hoje e quero ver até que data eu posso levar na assistência tecnica caso aconteça algum defeito de fabrica.

dbajcc@DBPOST on 12-FEB-10 >select sysdate + warranty as "Expiration Date",   2  name as "Product Name"   3  from super_shavers   4  where  name ='shaver_100' ; Expiratio Product Name --------- ------------------------------ 12-AUG-11 shaver_100 1 row selected. Elapsed: 00:00:00.01 dbajcc@DBPOST on 12-FEB-10 >

Há também o datatype INTERVAL DAY TO SECOND que coloca ainda mais precisão  para dia,hora,minutos e segundos.

Criando a tabela de exemplo :

dbajcc@DBPOST on 12-FEB-10 >create table login_expiration   2  (login_name varchar2(50),   3 dbajcc@DBPOST on 12-FEB-10 >create table fool_login   2  (login_name varchar2(30),expiration_dt interval day to second); Table created. Elapsed: 00:00:00.07 dbajcc@DBPOST on 12-FEB-10 >

Inserindo linhas :

dbajcc@DBPOST on 12-FEB-10 > dbajcc@DBPOST on 12-FEB-10 >insert into fool_login   2  values   3  ('rmartin',INTERVAL '30 5:00:06' DAY TO SECOND) ; 1 row created. Elapsed: 00:00:00.03 dbajcc@DBPOST on 12-FEB-10 > dbajcc@DBPOST on 12-FEB-10 >insert into fool_login   2  values   3  ('jccorrea',INTERVAL '75' DAY) ; 1 row created. Elapsed: 00:00:00.00 dbajcc@DBPOST on 12-FEB-10 >commit ; Commit complete. Elapsed: 00:00:00.02 dbajcc@DBPOST on 12-FEB-10 >

Na minha tabela de exemplo vou verificar quanto tempo estes usuários/logins que foram criados hoje/agora possuem até serem bloqueados para entrar em um determinado sistema
Obs. * a tabela foi criada apenas para exemplo *

dbajcc@DBPOST on 12-FEB-10 > dbajcc@DBPOST on 12-FEB-10 >alter session set nls_date_format = 'dd/mm/rrrr hh24:mi' ; Session altered. Elapsed: 00:00:00.01 dbajcc@DBPOST on 12/02/2010 12:42 >select login_name,sysdate + expiration_dt   2  from fool_login   3  ; LOGIN_NAME                     SYSDATE+EXPIRATI ------------------------------ ---------------- rmartin                        14/03/2010 17:43 jccorrea                       28/04/2010 12:43 2 rows selected. Elapsed: 00:00:00.01 dbajcc@DBPOST on 12/02/2010 12:43 >

Esta foi uma breve apresentação dos datatypes INTERVAL YEAR TO MONTH e DAY TO SECOND e duas funções para manipulá - los TO_YMINTERVAL(expressão' ) e INTERVAL 'expressão' .

Estes datatypes estão disponíveis no Oracle Database a partir da versão 9i .

Até,

Júlio César