Oracle Database

Using Oracle DBMS_SQLTUNE

A sample usage of DBMS_SQLTUNE

set timing on;
select count(*) from slow_table where col1 = ‘XYZ’ and col2 = ‘XYZ’;
— Elapsed: 00:00:06.631

declare
l_sql clob;
l_task_id clob;
begin
l_sql := ‘select count(*) from slow_table where col1 = :B1 and col2 = :B2’;
l_task_id := dbms_sqltune.create_tuning_task(
sql_text => l_sql,
bind_list => sql_binds(
anydata.ConvertVarchar2(‘XYZ’),
anydata.ConvertVarchar2(‘XYZ’)
),
user_name => ‘OWNER_NAME’,
scope => dbms_sqltune.scope_comprehensive,
time_limit => 300,
task_name => ‘sql_tuning_task_test_01’
);
dbms_output.put_line(l_task_id);
end;
/

select task_id, task_name, status from user_advisor_log where task_name = ‘sql_tuning_task_test_01’;
exec dbms_sqltune.execute_tuning_task(task_name => ‘sql_tuning_task_test_01’);
select task_id, task_name, status from user_advisor_log where task_name = ‘sql_tuning_task_test_01’;
select dbms_sqltune.report_tuning_task(‘sql_tuning_task_test_01’) as recommendations from dual;
exec dbms_sqltune.drop_tuning_task(task_name => ‘sql_tuning_task_test_01’);

How to obtain the certificates from HTTPS websites

In order to get websites’ certificates and then add those certificates to an Oracle Wallet you can use a free tool called OpenSSL. Usually it comes installed with Linux and Mac OS X. For windows it is possible to google it and download the installer.

Here are some samples on how to execute OpenSSL and obtains certificates.

  1. Gmail’s SMTP – openssl s_client -starttls smtp -connect smtp.gmail.com:587 -showcerts
  2. For other sites – openssl s_client -connect google.com:443 -showcerts

Get all certificates starting from the second to put into Oracle’s wallet. The first certificate should not be in there.

Now, lets create the Oracle’s wallet.
orapki wallet create -wallet /path -pwd pwd

Then, lets add a certificate to this wallet.
orapki wallet add -wallet /path -trusted_cert -cert /path/cert1.pem

You can see what’s in your certificate.
orapki wallet display -wallet /path

Thanks to this post: https://fuzziebrain.com/content/id/1720/

 

Oracle PL/SQL code to generate dummy data


with test_data as (
select rownum as id,
initcap(dbms_random.string('l', dbms_random.value(10, 25))) || ' ' ||
initcap(dbms_random.string('l', dbms_random.value(10, 30))) as name,
initcap(dbms_random.string('l', dbms_random.value(10, 50))) as city,
trunc(to_date('01-01-2015', 'DD-MM-YYYY') + dbms_random.value(-36500, 0)) dob
from dual
connect by level <= 100000
)
select id, name, replace(lower(name), ' ') || '@company.com' email, city, dob
from test_data;

Oracle PL/SQL Script to test CPU Performance

The following script can be used to get some reference on the server’s CPU time. I used it to check the difference of execution time between two servers.

SET SERVEROUTPUT ON
SET TIMING ON
 
DECLARE
  n NUMBER := 0;
BEGIN
  FOR f IN 1..10000000
  LOOP
    n := MOD (n,999999) + SQRT (f);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('Res = '||TO_CHAR (n,'999999.99'));
END;
/

This script should work on any Oracle version.

Thanks to http://www.juliandyke.com/CPUPerformance/CPUPerformance.php

How to manually change Oracle Apex internal Admin’s password

Hi. Here it follows the necessary code to be used as SYSDBA in order to change the internal Admin password of your Oracle Apex installation.

— 1st step
alter session set current_schema = APEX_050100;

— 2nd step
select rtrim(min(user_id)) user_id,
nvl(rtrim(min(email_address)), ‘ADMIN’) email_address
from wwv_flow_fnd_user
where security_group_id = 10
and user_name = upper(‘ADMIN’);

— 3rd step
declare
l_g_security_group_id number := wwv_flow_security.g_security_group_id;
l_g_user varchar2(255) := wwv_flow_security.g_user;
begin
wwv_flow_security.g_security_group_id := 10;
wwv_flow_security.g_user := ‘ADMIN’;

wwv_flow_fnd_user_int.create_or_update_user( p_user_id => 1940108883932253,
p_username => ‘ADMIN’,
p_email => ’email@mail.com’,
p_password => ‘password’ );

commit;
wwv_flow_security.g_security_group_id := l_g_security_group_id;
wwv_flow_security.g_user := l_g_user;
end;
/

Tested on Oracle Apex 5.1. Good luck!

How to search into all columns from your Oracle Database

Hey there!

This is a good way to search into all columns in your database for a determined word.

declare
  v_search_key varchar2(30) := 'Brasil';
  v_count integer;
begin
  for t in (select owner, table_name from dba_tables /*where owner = 'HR'*/) loop
    for c in (select column_name, data_type from dba_tab_cols where owner = t.owner and table_name = t.table_name) loop
      v_count := 0;
      begin
        if c.data_type in ('BLOB', 'CLOB') then
          execute immediate 'select count(*) from "' || t.owner || '"."' || t.table_name || '" where dbms_lob.instr("' || c.column_name || '", utl_raw.cast_to_raw(''' || v_search_key || ''')) > 0' into v_count;
        elsif c.data_type in ('VARCHAR2', 'NUMBER', 'DATE', 'CHAR', 'NVARCHAR2') then
          execute immediate 'select count(*) from "' || t.owner || '"."' || t.table_name || '" where to_char("' || c.column_name || '") like ''%' || v_search_key || '%''' into v_count;
        end if;
      exception
        when others then
          dbms_output.put_line(t.owner || '.' || t.table_name || '.' || c.column_name || ' data type: ' || c.data_type || ' error: ' || sqlerrm);
      end;
      if v_count > 0 then
        dbms_output.put_line(t.owner || '.' || t.table_name || '.' || c.column_name || ' found: ' || v_count);
      end if;
    end loop;
  end loop;
end;
/

Good luck!

Update Em Tabela Muito, Muito Grande

Se você tiver que fazer um UPDATE em uma tabela muito grande (milhões de registros), uma abordagem aconselhável (vai economizar UNDO e REDO) seria:
    1. criar uma tabela temporária já com o UPDATE desejado:
        create table tb_teste_tmp as select campo, [campo atulizado] from tb_teste;
    2. gravar os relacionamentos e “dropar” a tabela antiga:
        drop table tb_teste cascade constraints;
    3. renomear a tabela nova e refazer as constraints;
        rename tb_teste_tmp to tb_teste;

Teste feito no Oracle 9i.

Oracle Flashback Query

Apartir do Oracle 9i, temos uma funcionalidade muito legal: Oracle Flashback Query. Com ela é possível fazer uma query em uma tabela mostrando dados em versões ao longo do tempo, ou seja, consultar uma tabela como ela se encontrava num determinado momento.

Veja o exemplo:
select * from hr.countries as of timestamp sysdate – (1/24);

Acima consultamos a tabela HR.COUNTRIES e temos os dados como eles se encontravam a 01 hora atrás. Claro que não teremos todo o histórico dos dados de uma tabela de um banco de produção – isso custaria muito em armazenamento -, mas essa possibilidade de “voltar ao passado” é totalmente configurável e depende de parâmetros do seu banco de dados como UNDO_RETENTION (tempo de retenção), UNDO_TABLESPACE, UNDO_MANAGEMENT assim como o acesso a consultar esses dados “antigos” dependem de GRANT.
Teste feito em Oracle 9i, 10g, e XE.

Memória – Quanto Estou Consumindo?

É bom sabermos o quanto de memória privada (PGA) nosso programa está consumindo. Uma maneira de saber é utilizando a função abaixo.
Criei-a no schema SYS e dei GRANT EXECUTE à role PUBLIC, criando também um PUBLIC SYNONYM para que todos os usuários do banco tenham acesso à função. Segue código:
create or replace function sys.fn_pga_utilizada return number is
  v_qtde number;
begin
  select sum(st.value)
    into v_qtde
    from sys.v_$statname nm, sys.v_$sesstat st, sys.v_$session se
   where se.audsid = userenv(‘sessionid’)
     and st.statistic# = nm.statistic#
     and st.sid = se.sid
     and nm.name = ‘session pga memory’;
  return v_qtde;
exception
  when others then return -1;
end;
/
grant execute on sys.fn_pga_utilizada to public;
create or replace public synonym fn_pga_utilizada for sys.fn_pga_utilizada;
Um código para teste da função seria:
declare
  type t_columns is table of all_tab_columns%rowtype;
  v_columns t_columns;
begin
  select * bulk collect into v_columns from all_tab_columns;
  dbms_output.put_line(to_char(fn_pga_utilizada / 1024 / 1024, ‘FM999G999G999G990D00’) || ‘MB’);
end;
Teste feito nas versões Oracle Database 10g e XE.