Como alterar o valor de um parâmetro de outra sessão

Imaginem um cenário onde é necessário alterar o valor de um parâmetro de uma sessão que já está aberta no banco de dados e sem uma interface SQL para realizar um ALTER SESSION.

Para resolver este problema, podemos utilizar as procedures SET_INT_PARAM_IN_SESSION e SET_BOOL_PARAM_IN_SESSION da package DBMS_SYSTEM.

dbms_system.set_bool_param_in_session(
sid IN NUMBER,
serial# IN NUMBER,
parnam IN VARCHAR2,
bval IN BOOLEAN);

dbms_system.set_int_param_in_session(
sid IN NUMBER,
serial# IN NUMBER,
parnam IN VARCHAR2,
intval IN BINARY_INTEGER);

Neste exemplo, vamos alterar o parâmetro sort_area_size da sessão abaixo:

[oracle@oracle01 ~]$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 20 11:08:29 2014

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

Last Successful login time: Thu Nov 20 2014 11:07:59 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show parameter sort_area

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     65536

Buscando as informações e alterando o valor do parâmetro SORT_AREA_SIZE da sessão do usuário SCOTT:

[oracle@oracle01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 20 11:13:08 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select sid,serial# from v$session where username='SCOTT';

SID        SERIAL#
---------- ----------
237        49369

SQL> exec dbms_system.SET_INT_PARAM_IN_SESSION(237, 49369, 'SORT_AREA_SIZE',131072);

PL/SQL procedure successfully completed.

Verificando o novo valor do parâmetro SORT_AREA_SIZE na sessão do usuário SCOTT:

SQL> select name, value
from V$SES_OPTIMIZER_ENV
where sid=237
and name='sort_area_size';

NAME                                     VALUE
---------------------------------------- -------------------------
sort_area_size                           131072

Abraço,

Alex Zaballa.

Read More

Connect 2014 - SAOUG - Review

De 09/11 à 11/11 participei do evento Connect 2014 do SAOUG (Grupo de Usuários Oracle da África do Sul).

O evento ocorreu no CHAMPAGNE SPORTS RESORT, que fica a cerca de 5 horas de carro do aeroporto OR Tambo.

Entrada do Hotel:

Chalets (os quartos do hotel já estavam lotados e a opção dada, foi ficar nos chalets) :

Montagem dos expositores:

Credenciamento:

 Material:

Domingo

Abertura:

Cocktail de abertura:

Segunda

Keynote - Digital Disruption:

New Innovations in 12C:

Living with Exadata:

Why SQL Server customers are migration to Oracle:

Want to secure your database? Ask me HOW!

Oracle Database Cloud:

 Keynote - Big Data:

Terça

Keynote - How BIG technology trends are being leveraged by large companies to achieve “Digital Transformation”

Upgrade, Migrate & Consolidate to Oracle Database 12c: WHY?

AutoCloning Without Dataguard:

Nossa sessão sobre Flex Cluster and Flex ASM:

ODA Experience:

Keynote - Oracle Database 12c innovations - Demonstration challenge!

Encerramento:

Até 2015 :)

Presentation Download

Read More

Como remover um SQL Statement da Shared Pool

Imaginem um cenário onde é necessário limpar da shared pool um comando SQL, para que na próxima execução seja realizado um hard parse deste SQL.

Sabemos que para limpar toda a shared pool, podemos utilizar o comando ALTER SYSTEM FLUSH SHARED_POOL. Porém, ao executar este comando em um ambiente de produção haverá um impacto significativo.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.

Para resolver este problema, no Oracle11G, foi introduzida a procedure PURGE na package DBMS_SHARED_POOL.

De acordo com a documentação, para limpar um SQL Statement, o parâmetro name, que é  obrigatório, é a concatenação das colunas address e hash_value da view V$SQL_AREA. O parâmetro flag deve ser setado para um valor diferente de P, T ,R ou Q. Então, nesse caso vamos utilizar ‘C‘.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '9uay37ag292zh';

ADDRESS HASH_    VALUE
---------------- ----------
00000000DA8B10F0 2653195248

SQL> exec DBMS_SHARED_POOL.PURGE ('00000000DA8B10F0, 2653195248','C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '9uay37ag292zh'; 

no rows selected

Mudando o parâmetro flag, também podemos remover outros tipos de objetos, como packages, procedures, functions, types, triggers e sequences.

O  valor default para o parâmetro flag é 'P', mas podemos utilizar:

• P - nome da package/procedure/function
• T - nome do type
• R - nome da trigger
• Q - nome da sequence

Limpando uma procedure da shared pool:

CREATE OR REPLACE PROCEDURE procedure_teste IS
BEGIN
NULL;
END;
/
Procedure created.


SQL> exec procedure_teste;
PL/SQL procedure successfully completed.

SQL> exec dbms_shared_pool.purge('PROCEDURE_TESTE');
PL/SQL procedure successfully completed.

Para algumas versões do banco 10GR2, isto está disponível através do Patch 5614566.

Referências:

  http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_shared_pool.htm#ARPLS055

Abraço,

Alex Zaballa.

Read More