quinta-feira, 15 de setembro de 2011

Como reduzir o datafile da tablespace Temporária (TEMP)


O banco de dados pode executar aplicações que possam fazer operações de classificação enormes; (por exemplo, relatórios de fechamento anual). Essa atividade pode aumentar o tamanho da tablespace TEMP ocupando a maior parte do espaço na file system. No exemplo citado o relatório pode executar uma ou duas vezes por ano e não há necessidade de manter / manter um tempfile enorme.

A tablespace TEMP foi criada com arquivos de dados como AUTOEXTEND ON MAXSIZE UNLIMITED para evitar erro:

ORA-1652 Text: unable to extend temp segment by %s in tablespace %s

Foram realizadas tentativas para reduzir o tempfile da tablespace TEMP, usando o comando “alter database tempifle... resize”  que normalmente falham, apresentando a mensagem de erro abaixo:


Error: ORA 3297 : file contains blocks of data beyond requested RESIZE value

Abaixo é apresentada uma solução para reduzir o “tempfile” da tablespace TEMP para utilizar o espaço em disco para outros fins.


Solução:


      1.Verificar a utilização da tablespace TEMP, utilizar os scripts abaixo:

SELECT COUNT(*) FROM DBA_EXTENTS 
 WHERE SEGMENT_TYPE='TEMPORARY';

select tablespace_name,
       CURRENT_USERS,
       TOTAL_BLOCKS,
       USED_BLOCKS,
       FREE_BLOCKS 
  from v$sort_segment;

select * from dba_tablespaces where tablespace_name = 'TEMP'; 

Caso as querys retornem linhas, executar o comando abaixo.

SQL> alter tablespace TEMP default storage (pctincrease 0);

Após execução do comando acima, deve-se verificar o resultado do comando, executando as queries abaixo.

select owner,
       segment_name,
       segment_type
  from dba_segments
 where tablespace_name = 'TEMP';

SELECT s.username,
       u.tablespace,
       u.contents,
       u.extents,
       u.blocks
   FROM v$session s, v$sort_usage u
   WHERE s.saddr=u.session_addr;

2. Na sequência criar uma nova tablespace temporária com tamanho menor.

SQL> create temporary tablespace TEMP1 tempfile 'c:\temp01.dbf' size 100M extent management local uniform size 128K;

3. Se a tablespace (atual) for uma tablespace temporário para todo o banco de dados, deve-se
    definir a  nova  tablespace (TEMP)  como  a  tablespace  temporária  padrão  para  todos  os  
    usuários no banco de dados, usar o comando abaixo.

SQL> alter database default temporary tablespace TEMP1;

4. Se necessário, altere todos os usuários para a nova tablepace temporária, utilizar o script
    abaixo:

SQL> select 'alter user '||username||' temporary tablespace TEMP;' from 
dba_users;

5.Remova a tablespace antiga:

SQL> drop tablespace temp including contents.

Nota:  Para o Oracle versão 9i, o comando “drop” tem uma variação para a remoção do “tempfile” no sistema operacional, é necessário utilizar o comando abaixo.

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;



Rubens Oliveira
DBA Oracle Consultor
olivert.dba@consultant.com



quinta-feira, 1 de setembro de 2011

Removendo sessões no Oracle


Existem várias maneiras para remover sessões no banco de dados Oracle, elas podem ser realizadas tanto dentro do banco de dados quanto no sistema operacional. O presente artigo irá descrever um processo para identificar uma sessão a ser removida do banco de dados, executar a remoção através do banco de dados e também executar a remoção da sessão através dos sistemas operacionais, Windows, Linux e Unix.


Identificando a sessão a ser removida

Remover sessões pode ser destrutivo, deve-se ter cuidado ao identificar a sessão para ser removida. Se uma sessão pertencente a um processo de “background” for removida erroneamente poderá causar um acidente no banco de dados (por ex.: queda ou crash do banco de dados) além de outros danos para as aplicações.

Para identificar quais sessões estão gerando problemas no banco de dados devem-se consultar as visões [G]V$SESSION e [G]V$PROCESS, o prefixo [G] refere-se às visões do banco de dados em ambientes com Oracle RAC.

Abaixo segue um script (básico) para identificação das sessões ativas no banco de dados:

SET LINESIZE 100

COLUMN spid     FORMAT A10
COLUMN username FORMAT A10
COLUMN program  FORMAT A45

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
  FROM gv$session s
  JOIN gv$process p ON p.addr = s.paddr
   AND p.inst_id = s.inst_id
 WHERE s.type != 'BACKGROUND';


INST_ID SID SERIAL# SPID USERNAME PROGRAM
------- --- ------- ---- -------- ------------------------------------------
      1  30      15 3859 TEST     sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
      1  23     287 3834 SYS      sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
      1  40     387 4663          oracle@oel5-11gr2.localdomain (J000)
      1  38     125 4665          oracle@oel5-11gr2.localdomain (J001)



Os valores das colunas SID e SERIAL# serão utilizados e deverão ser substituídos nos comandos nas seções seguintes.


Usando o comando ALTER SYSTEM KILL SESSION

A sintaxe básica para a remoção é apresentada abaixo:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

Num ambiente com Oracle RAC, além dos valores SID e SERIAL#, pode-se informar o valor de INST_ID (instrução opcional), que indica qual a instância que se encontra a sessão. Essa opção permite que se remova uma sessão de um Oracle RAC mesmo estando conectado num nó diferente.


SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

NOTA:  O comando KILL SESSION não remove a sessão. O comando simplesmente altera o “status” da sessão para ser removida posteriormente. Em algumas situações, como a espera de uma resposta de um banco de dados remoto ou no “rollback” de transações, a sessão não será removida imediatamente irá aguardar para a conclusão da operação para ser removida. Nestes casos, a sessão terá um status de "marked for kill".

Além da sintaxe descrita acima, pode-se utilizar a cláusula IMMEDIATE.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Essa cláusula retorna o controle de volta para a sessão atual imediatamente, em vez de aguardar a confirmação da remoção da sessão.

SET LINESIZE 200

COLUMN username FORMAT A15

SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

Se o valor USED_UREC for decrescente para a sessão pesquisada, então deve-se aguardar a  sessão completar o “rollback” ao invés de matar a sessão no sistema operacional.

Outra opção também utilizada é realizar um “check point” do banco de dados, com isso as transações e sessões “efetivadas” são gravadas na base de dados e disponibilizadas da memória, usar o comando abaixo:

SQL> ALTER SYSTEM CHECKPOINT;


Usando o comando ALTER SYSTEM DISCONNECT SESSION

O comando ALTER SYSTEM DISCONNECT SESSION  é uma alternativa para remover sessões no banco de dados. Ao contrário do comando KILL SESSION, o comando SESSION DISCONNECT remove o processo servidor dedicado (ou circuito virtual ao usar Shared Sever), que é equivalente a remover o processo do banco de dados através do sistema operacional. A sintaxe básica é semelhante ao comando KILL SESSION com a adição da cláusula POST_TRANSACTION. Os valores das colunas SID e SERIAL #, são referentes à sessão a ser removida do banco de dados.

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

A cláusula POST_TRANSACTION espera as operações que estão em curso serem completadas antes de desconectar a sessão, enquanto a cláusula IMMEDIATE desconecta a sessão e as operações em curso são desfeitas imediatamente. 

As cláusulas POST_TRANSACTION e IMMEDIATE podem ser usadas juntas, mas a documentação afirma que, neste caso, a cláusula IMMEDIATE é ignorada. Além disso, a sintaxe sugere que as duas cláusulas são opcionais, mas, na realidade, um ou ambos devem ser especificados para que a instrução não retorne um erro (apresentado na instrução abaixo).

SQL> alter system disconnect session '30,7';
alter system disconnect session '30,7'
                                     *
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword

Este comando indica que não precisará acessar o sistema operacional para a remoção da sessão, o que reduz as chances de remover o processo errado.


Removendo sessões através do sistema operacional

Windows 


Para remover a sessão através do sistema operacional Windows, primeiro deve-se identificar a sessão, em seguida, substituir o valor da coluna SID e SPID no comando indicado abaixo:

C:\> orakill ORACLE_SID spid

Com a execução do comando acima a sessão será removida do banco de dados imediatamente e todos os recursos liberados.

LINUX ou UNIX

Para remover a sessão nos sistemas operacionais LINUX ou UNIX, primeiro deve-se identificar a sessão, em seguida, substituir os SPIDs relevantes para no comando abaixo:

% kill spid

Se depois de alguns minutos o processo não parou, para encerrar a sessão imediatamente, deve-se usar a opção abaixo, particularmente essa opção é a mais usual.

% kill -9 spid

Em caso de dúvida verifique se o SPID corresponde ao “Process_ID” do  LINUX ou UNIX, usar o comando.

% ps -ef | grep ora

Após a identificação e execução do comando KILL, através do sistema operacional a sessão será removida imediatamente do banco de dados  e todos os recursos liberados.


Rubens Oliveira
DBA Oracle Consultor
olivert.dba@consultant.com