unable to see package body on sqldeveloper - SOLVED

Solução :

grants :

GRANT EXECUTE ON  DBMS_METADATA TO user_unable_to_see ;
GRANT CREATE ANY PROCEDURE TO  user_unable_to_see;

Cabe analisar a segurança ao conceder os privilégios acima.

Oks?!!

Julio Cesar Correa

Uninstalling "schema visualizer" from Oracle SQL Developer

Quem instalou um plugin chamado schema visualizer no Oracle SQL Developer e agora quer desisntalar(principalmente porque ele fica tentando abrir um navegador e ir para a o site do fabricante)  pode realizá-lo desta forma segundo o site do fabricante:


1. Exit Oracle SQL Developer.


2.Delete file sqldev_path/sqldeveloper/extensions/com.sumsoftsolutions.erd4oracle.sqldev.jar


3.Delete directory
sqldev_path/sqldeveloper/extensions/com.sumsoftsolutions.erd4oracle.sqldev
Where sqldev_path is your SQL Developer installation directory.

Para mim funcionou ok!!!

Oracle - SQL - DATE e TIMESTAMP datatypes

Olá,

Vamos falar rapidamente sobre os tipos de dados que armazenam data.
Aqui vamos falar de date,timestamp,timestamp with timezone e timestamp with local timezone.O tipo INTERVAL que armazena intervalo de datas já foi abordado em outro post neste blog.

date : armazena o dia,mes,ano,hora.minuto e segundos.
timestamp : armazena o dia,mes,ano,hora.minuto mais a precisão de segundos que vai de  de 0-9.O default(padrão) é 6 .

timestamp with time zone: armazena o dia,mes,ano,hora.minuto mais a precisao de segundos que vai de  de 0-9.O default(padrão) é 6 .
A diferença aqui é que é armazenado o deslocamento em relação ao UTC(Cordinated Universal Time) or Greenwich Mean Time por exemplo para a cidade de São Paulo temos "-3:00".
O deslocamento é armazenado juntamente com o dado real no banco de dados.

timestamp with local time zone : armazena o dia,mes,ano,hora.minuto mais a precisão de segundos que vai de  de 0-9.O default(padrão) é 6 .
A diferença agora para o tipo acima é que o deslocamento em relação ao UTC não é armazenado juntamente com os dados deste tipo.Funciona assim,de acordo com a configuração de
do time zone de cada sessão do usuário o Oracle mostra o valor correspondente ao que foi armazenado.


Para ficar melhor mostraremos um exemplo:

SQL> create table x_date_datatypes(id number(2),dt date,tmstp timestamp(4),tmstp_local timestamp(6) with local time zone,tmstp_wtmz timestamp(2) with time zone);

Table created.

Elapsed: 00:00:00.08

SQL> insert into x_date_datatypes
values(1,sysdate,systimestamp,systimestamp,systimestamp) ;  2

1 row created.

Elapsed: 00:00:00.03
SQL> commit;

Commit complete.

Elapsed: 00:00:00.28
SQL>

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

Session altered.

Elapsed: 00:00:00.03
SQL>  select * from x_date_datatypes;

 ID DT                  TMSTP                          TMSTP_LOCAL                    TMSTP_WTMZ
--- ------------------- ------------------------------ ------------------------------ ------------------------------------------------------------
  1 03/08/2010 00:44:54 03-AUG-10 12.44.54.3052 AM     03-AUG-10 12.44.54.305240 AM   03-AUG-10 12.44.54.31 AM -03:00

Elapsed: 00:00:00.00
SQL>

Como podemos ver ,a segunda coluna é do tipo date e armazena até a precisado do segundo.
Na terceira coluna o tipo é timestamp e podemos ver que está armazenando a precisão em frações de segundos com 4 posições como definido no comando CREATE TABLE.

Vamos falar sobre a quinta coluna primeiro que é do tipo timestamp with time zone.Podemos ver que ela armazena o deslocamento em relação ao UTC,que para São Paulo é -03:00.
Mesmo trocando o fuso horário da sessão/time zone o valor não é alterado,pois o deslocamento é armazenado juntamente com os dados.

Na quarta coluna que é do tipo timestamp with local timezone é armazenado como no tipo timestamp a precisão em frações de segundos.
Podemos notar no default que não aparece o deslocamento em relação ao UTC,mas se fizermos o seguinte:

SQL> alter session set time_zone='+6:00' ;

Session altered.

Elapsed: 00:00:00.00
SQL> select systimestamp from dual ;

SYSTIMESTAMP
---------------------------------------------------------------------------
03-AUG-10 01.15.20.695118 AM -03:00

Elapsed: 00:00:00.00
SQL> select * from x_date_datatypes;

 ID DT                  TMSTP                          TMSTP_LOCAL                    TMSTP_WTMZ
--- ------------------- ------------------------------ ------------------------------ ------------------------------------------------------------
  1 03/08/2010 00:44:54 03-AUG-10 12.44.54.3052 AM     03-AUG-10 09.44.54.305240 AM


Podemos ver que o valor mostrado na coluna TMSTP_LOCAL está calculado segundo a sessão do usuário descontando mais 3 horas em relação ao UTC,pois no valor armazenado no banco de dados
o UTC era -03:00

Dica :Consulte a V$TIMEZONE_NAMES 

Para mais informações sobre estes tipos de data/hora consulte aguide de SQL disponível no tahiti.oracle.com .

Abs,


JC

Oracle PL/SQL - Random number generator

Semana passada eu caí em uma situação que precisa buscar uma porção de dados de variados entre as 5 opções que eu tinha.Fiz um teste com a DBMS_RANDOM usando a função VALUE.
Ficou 10 !Do jeito que eu queria.



select f.column1,
       f.column2 -- itens variados
       f.column3
from dbajcc.my_tab f
where f.column1 = x
and rownum < 1000 -- um range de x linhas
order by  dbms_random.value ;


Esta última linha resolveu sem problema o que eu queria.

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!

PL/SQL Challenge

Para quem curte PL/SQL existe um site/local novo para testar os conhecimentos!

http://plsqlchallenge.com

Peguei esta dica no Linkedin do Felipe da Silva .

Abraços,

JC

Oracle 10g - Capacity Planning for Database Objects - Oracle 10g and later - Part 1

Introdução
Segundo a documentação da Oracle existe duas formas de fazer o "Capacity Planning" usando o próprio Oracle Database :

- Usando a package DBMS_SPACE via código PL/SQL ou
- EM Database Control ou Grid Control

Na própria documentação do Oracle 10g ou 11g temos  exemplos de como fazer isso.

Vamos abordar primeiro via PL/SQL no Oracle 10gr2 - 10.2.0.4 .

É possível estimar tamanho para Tabela e Índice mas para ser prático faremos somente de tabelas,que é normalmente onde se concentrará os esforços para estimar tamanho de schemas e de banco de dados que residirão nos tablespaces.Isto facilitará a organização nos tablespaces e arquivos de dados.

Estimativa para tabelas

Vamos usar um exemplo de uma tabela de pedidos com 7.000 pedidos diários.
A nossa tabela MY_ORDERS(qualquer semelhança é mera coincidência) tem a seguinte estrutura :


 ORDER_ID     NOT NULL NUMBER(12)
 ORDER_DATE   NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZONE
 ORDER_MODE   VARCHAR2(8)
 CUSTOMER_ID  NOT NULL NUMBER(6)
 ORDER_STATUS NUMBER(2)
 ORDER_TOTAL  NUMBER(8,2)
 SALES_REP_ID NUMBER(6)
 PROMOTION_ID NUMBER(6)

Abaixo o resultado para o nosso teste :

Podemos ver na imagem acima que esta tabela crescerá  303104 bytes por dia segundo a média de pedidos/dia e considerando  que este numero de pedidos diários não mudará teremos 8,67MB ou 9MB por mês a mais em dados.O tamanho total ocupado pela nossa tabela ORDERS na tablespace será de 327680 bytes inicialmente ou 320 KB .Relativamente pequeno.Mas se olharmos para um modelos de dados digamos que de médio porte veremos que fica mais trabalhoso fazer as estimativas para um determinado banco de dados.

No próximo exemplo faremos a estimativa de índices usando PL/SQL  e estimativa de tabela e índice usando EM.

Abs,

Julio Cesar Correa

Object Tables - Exemplos usando Oracle 10gR2(10.2.0.4)

Alguém já usou object tables no Oracle?

Vou mostrar alguns exemplos  usando Oracle 10g.

Object table nada mais é que uma tabela criada com base em um tipo definido pelo usuário ou user-defined types um conceito bem conhecido das famosas Classes em Orientação a Objetos popular OO.

Desde a versão 8 do Oracle é possível trabalhar com OO nos códigos PL/SQL e armazenamento dentro do banco de dados.Da versão 8 até a versão atual 11g estas características foram melhoradas.Podemos trabalhar com conceito de classe,atributos e métodos bem como herança ,funções e contrutores dentre outras.

Uma vez definido um TYPE podemos utilizá-los de várias formas e  entre elas a de criar tabelas com base em tipos.

Criando um object type :

dbajcc@OCADB on 11-JAN-10 >create type t_dbf as object(file_name varchar2(50),tablespace_name varchar2(30));
  2  /
Type created.
Elapsed: 00:00:00.38

Criando uma object table :

dbajcc@OCADB on 11-JAN-10 >create table tst_dbf_obj of t_dbf ;
Table created.
Elapsed: 00:00:00.70

Inserindo dados em uma object table :

dbajcc@OCADB on 11-JAN-10 >insert into tst_dbf_obj select file_name,tablespace_name from dba_data_files ;
6 rows created.
Elapsed: 00:00:00.04

dbajcc@OCADB on 11-JAN-10 >commit;
Commit complete.
Elapsed: 00:00:00.02


dbajcc@OCADB on 11-JAN-10 >select * from tst_dbf_obj ;

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ---------------
+DATA/ocadb/datafile/users.263.703360157           USERS
+DATA/ocadb/datafile/sysaux.259.703360021          SYSAUX
+DATA/ocadb/datafile/undotbs1.260.703360021        UNDOTBS1
+DATA/ocadb/datafile/system.257.703360021          SYSTEM
+DATA/ocadb/datafile/example.261.703360021         EXAMPLE
+DATA/ocadb/datafile/teste_rename.262.703360117    TESTE_RENAME

6 rows selected.
Elapsed: 00:00:00.01

Também é possível criar constraints em object tables tais como Primary Keys (PK's) e Check constraints .

No exemplo abaixo vamos criar uma object table com uma coluna identificadora :

dbajcc@OCADB on 11-JAN-10 >create type t_automovel as object (id number(2),nome varchar2(10),marca varchar2(15));

  2  /

Type created.

Elapsed: 00:00:00.05

 

dbajcc@OCADB on 11-JAN-10 >

dbajcc@OCADB on 11-JAN-10 >create table frota_taxi of t_automovel (id PRIMARY KEY)

  2  object identifier is primary key ;
Table created.

Elapsed: 00:00:00.13

dbajcc@OCADB on 11-JAN-10 >

Você NÃO PODE excluir um TYPE caso exista um outro objeto definido com base nele :

dbajcc@OCADB on 11-JAN-10 >drop type t_automovel ;

drop type t_automovel

*

ERROR at line 1:

ORA-02303: cannot drop or replace a type with type or table dependents

Elapsed: 00:00:00.02

Pirmeiro excluímos os dependentes e depois  eliminamos  o tipo:

dbajcc@OCADB on 11-JAN-10 >drop table frota_taxi ;

Table dropped.

Elapsed: 00:00:00.81

dbajcc@OCADB on 11-JAN-10 >drop type t_automovel ;

Type dropped.

Elapsed: 00:00:00.58

dbajcc@OCADB on 11-JAN-10 >

Podemos fazer o uso das object tables que possuem colunas que também  são objetc types   :

dbajcc@OCADB on 11-JAN-10 >create type t_bolas_sena

as object (first number(2),second number(2),third number(2),

           fourth number(2),fifth number(2),sixth number(2)) ;

/  2    3    4

Type created.

Elapsed: 00:00:00.06

dbajcc@OCADB on 11-JAN-10 >

--criar um objeto com os atributos de um resultado de jogo da mega sena

 

dbajcc@OCADB on 11-JAN-10

create type t_resultado_sena

as object( concurso number(4),

           local_sorteio varchar2(50),

            bolas_sena  t_bolas_sena ) ;

/dbajcc@OCADB on 11-JAN-10 >dbajcc@OCADB on 11-JAN-10 >  2    3    4    5

Type created.

Elapsed: 00:00:00.05

dbajcc@OCADB on 11-JAN-10 >

 

--criação de uma object table que armazena o type  t_resultado_sena

 

dbajcc@OCADB on 11-JAN-10 >

create table tb_resultado_sena of t_resultado_sena ;

/dbajcc@OCADB on 11-JAN-10 >dbajcc@OCADB on 11-JAN-10 >

Table created.

Elapsed: 00:00:00.05

dbajcc@OCADB on 11-JAN-10 >insert into  tb_resultado_sena

values(t_resultado_sena(1141,

                       'TRAMANDAI/RS',

           t_bolas_sena(09,37,40,50,58,60)

                           )

           ) ;  2    3    4    5    6

1 row created.

Elapsed: 00:00:00.01

 

dbajcc@OCADB on 11-JAN-10 >insert into  tb_resultado_sena

(concurso,local_sorteio,bolas_sena)

values(1142,'PENEDO/AL',

           t_bolas_sena(03,15,26,28,31,58)

      ) ;  2    3    4    5

1 row created.

Elapsed: 00:00:00.00

 

 

dbajcc@OCADB on 11-JAN-10 >

dbajcc@OCADB on 11-JAN-10 >commit ;

Commit complete.

Elapsed: 00:00:00.01

dbajcc@OCADB on 11-JAN-10 >

dbajcc@OCADB on 11-JAN-10 >select * from tb_resultado_sena tbs where tbs.concurso = 1142 ;

  CONCURSO LOCAL_SORTEIO

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

BOLAS_SENA(FIRST, SECOND, THIRD, FOURTH, FIFTH, SIXTH)

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

      1142 PENEDO/AL

T_BOLAS_SENA(3, 15, 26, 28, 31, 58)

Elapsed: 00:00:00.03

dbajcc@OCADB on 11-JAN-10 >select * from tb_resultado_sena ;

  CONCURSO LOCAL_SORTEIO

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

BOLAS_SENA(FIRST, SECOND, THIRD, FOURTH, FIFTH, SIXTH)

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

      1141 TRAMANDAI/RS

T_BOLAS_SENA(9, 37, 40, 50, 58, 60)

      1142 PENEDO/AL

T_BOLAS_SENA(3, 15, 26, 28, 31, 58)

Elapsed: 00:00:00.02

Dúvidas ou sugestões ?Poste o seus comentários!

Abs,

Julio Cesar Correa

Usando o pragma autonomous_transaction

Olá classe,

Vamos a mais um post rápido sobre PL/SQL. Agora com um exemplo simples do uso do pragma autonomous_transaction que nada mais é que uma forma de isolar uma transação de outra.
No caso faremos um teste simples usando insert,commit e rollback.Mas por ele podemos  tirar as conclusões que precisamos e entender como isolar uma transação  .

Lets' go!

Criando a tabela para o teste :

- Criando a procedure 2  que contém o pragma autonomous_transaction

- Criando a procedure 1 que fará a chamada da procedure 2

Agora vamos executar a procedure  1.
Lembrando que o objetivo é isolar a transação da procedure 2.

Ok!

Conclusão

Usando o pragma autonomous_transaction podemos ver que dentro da procedure  prc_tst_auto  a primeira transação foi iniciada com o update 1 e depois finalizada com commit e que outra inicia-se no  update 2  .Podemos ver que a transação 2 mesmo com a execução do commit na procedure prc_tst_auto2 após o update 3  não foi alterada(como mostra a imagem).

Coloquei um rollback no meio do código da procedure 1 para o exemplo seguinte em que mostra que sem o pragma autonomous_transaction não é possével dar rollback no update 2.Podemos perceber que no código da procedure 1 não existe commit após o update 2 portanto olhando para somente este código em si vemos que "poderíamos "  fazer um undo desta transação.Porém isso é ilusão!

Veja :

- Atualizando o código da procedure 2 retirando o pragma

Vamos executar para comprovar o que foi dito acima :

Certo!Mesmo com rollback no código da procedure 1 para o update 2 a execução da procedure 2 força o commit na transação que começou na primeira procedure.

Entendidos?

Usando Bulk Collect no PL/SQL

Usando Bulk Collect no PL/SQL
Olá,
No primeiro post do ano vamos relembrar um pouco de PL/SQL,assim eu não esqueço !
Como SYS :

Este é um detalhe para acessar a view dba_objects via procedure :

Vamos ao trabalho!!
- Criação da tabela de exemplo que receberá os dados da procedure que executará o insert com bulk collect :

- Criação da procedure

Vamos executar agora!

Podemos usar também a clausula LIMIT no bulk collect principalmente quando temos uma quantidade muito grande de dados.Por exemplo se tivéssemos 1.000.000 ao invés das nossas 30.000 linhas  poderíamos colocar um commit a cada 50.000 linhas inseridas.Isso é legal de usar principalmente para não estourarmos a nossa tablespace de undo facilemte gerenciando a frequencia de commits.
Vamos fazer um exemplo com 50.000 linhas inserindo-as efetuando commit a cada 10.000 linhas inseridas :
- Antes um limpezinha !

- criando a procedure

- executando a procedure

- Contando as linhas inseridas :

Por hoje é só.Choveu em São Paulo e quem trabalha por aqui conhece como é!
Obs. Não entramos em nenhum conceito de tuning de código PL/SQL .Mas vale a pena você dar uma revisada nos seus cursores simples que manipulam milhões de linhas uma de cada vez  e ninguém sabe porque demora tanto!!!Já reduzi rotinas que duravam horas para minutos ou até segundos ! Deixa isto para o próximo post.
Abs,
Julio Cesar Correa

A função SYS_CONTEXT

Olá,

A muito tempo atrás estava precisando obter algumas informações para auditoria e como não tinha auditoria habilitada no banco de dados tive que me virar com triggers e e tabelas auxiliares para obter o que eu precisava.
Hoje revisando alguns scripts resolvi postar um trecho de um deles porque pode ser útil para alguém .


 select sys_context('USERENV', 'TERMINAL') as terminal,
       sys_context('USERENV', 'HOST') as host,
       sys_context('USERENV', 'OS_USER') as os_user,
       sys_context('USERENV','IP_ADDRESS') as ip_address,
       sys_context('USERENV','NETWORK_PROTOCOL') as network_protocol,
       sys_context('USERENV', 'SESSION_USER') as session_user
 from dual;

O uso de SYS_CONTEXT e USERENV vai além do escopo deste post.
Como hoje estou compreguiça,nem vou postar exemplo de códigos,vou deixar para outra oportunidade kkk.

Usei muito para montar uma auditoria simples e personalizada onde não poderia usar a auditoria do Oracle.

Para quem estiver mais interessado tem a security guide do Oracle com mais informações :

http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/toc.htm

Julio Cesar Correa

Segmet Advisor em bloco PL/SQL

Olá,

Abaixo segue um exemplo do uso do Segment Advisor dentro do bloco PL/SQL e ao final as views que devem ser consultadas para recuperar as recomendações.


--Inicio do bloco pl/sql

variable id number ;

BEGIN

DECLARE

name_task varchar2(100);
desc_task varchar2(500);
obj_id number ;

begin

execute immediate 'alter table cmt.userdocuments enable row movement';


--name_task := 'Segment Advisor example on userdocuments' ;
name_task :=''; --vai ser gerado pelo creaste task
desc_task := 'Teste do uso de segment advisor na tabela userdocuments';

dbms_advisor.create_task(
advisor_name => 'Segment Advisor',
task_id => :id,
task_name => name_task,
task_desc => desc_task );

/*dbms_advisor.create_object(
task_name => name_task,
object_type =>'TABLE',
attr1 => 'CMT',
attr2 => 'USERDOCUMENTS',
-- attr3 => null,
-- attr4 => null,
-- attr5 => null,
obj_id => obj_id) ;*/

dbms_advisor.create_object(name_task,'TABLE','CMT','USERDOCUMENTS',NULL,NULL,obj_id) ;

dbms_advisor.set_task_parameter(
task_name => name_task,
parameter => 'recommend_all',
value => 'TRUE');

dbms_advisor.execute_task (name_task) ;

end;

END;

select owner,task_id,task_name,type,message,more_info from dba_advisor_findings
where task_id = 28198

select owner,task_id,task_name,benefit_type
from dba_advisor_recommendations
where task_id = 28198

select owner,task_id,task_name,command,attr1
from dba_advisor_actions
where task_id = 28198

DBMS_SCHEDULER - Alteração de atributo do JOB

Um exemplo simples de alteração de um atributo de um JOB via DBMS_SCHEDULER.

C:\Documents and Settings\jcorrea>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sex Mar 6 10:34:37 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

idle> conn jccorrea@cmt
Informe a senha:
Conectado.
jccorrea@CMT> select repeat_interval from dba_scheduler_jos
2
jccorrea@CMT> select repeat_interval from dba_scheduler_jobs
2 where job_name='EXEC_PRC_SEARCH_DUP_TSN';

REPEAT_INTERVAL
------------------------------------------------------------------------------------------
----------
FREQ = WEEKLY ; INTERVAL= 1

jccorrea@CMT> begin
2 dbms_scheduler.set_scheduler_attribute('EXEC_PRC_SEARCH_DUP_TSN','REPEAT_INTERVAL','F
REQ = DAILY; interval=1');
3 end;
4 /
dbms_scheduler.set_scheduler_attribute('EXEC_PRC_SEARCH_DUP_TSN','REPEAT_INTERVAL','FREQ =
DAILY; interval=1');
*
ERRO na linha 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'SET_SCHEDULER_ATTRIBUTE'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

jccorrea@CMT> begin
2 dbms_scheduler.set_attribute('EXEC_PRC_SEARCH_DUP_TSN','REPEAT_INTERVAL','FREQ = DAIL
Y; interval=1');
3 end;
4 /

Procedimento PL/SQL concluÝdo com sucesso.

jccorrea@CMT> select repeat_interval from dba_scheduler_jobs
2 where job_name='EXEC_PRC_SEARCH_DUP_TSN';

REPEAT_INTERVAL
------------------------------------------------------------------------------------------
----------
FREQ = DAILY; interval=1

jccorrea@CMT>

Abs,