Upgrade para Oracle 11 G Release 2

 Esta imagem fornecida pela Oracle mostra o caminho seguro a ser seguindo no caso de upgrade para a versão 11.2 do oracle 11g.Fica a Dica.

Archivelog no banco de dados Oracle - Como Habilitar/Desabilitar

Segue uma dica que me ajudou hoje .

==> Habilitando na sua instancia o modo Archive log do oracle via comando.

SQL> select log_mode from gv$database;

SQL> shutdown immediate;
SQL> startup mount exclusive;
SQL> alter database archivelog;
SQL> alter database open;
SQL> select log_mode from gv$database;

==> Desabilitando modo Archive  Log.

SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;

O que é o modo Arquive Log ?

Archivelog é gerado pelo banco de dados em todas as operações de transações no banco de dados como: INSERT, DROP, ALTER, ETC. No Oracle versão 9i para frente a Oracle recomenda o uso do Backup com RMAN, isto implica dizer que você precisa do seu banco em modo de Archive.

mais informações em :

http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12007.htm  

http://docs.oracle.com/cd/B28359_01/server.111/b28310/archredo002.htm

Verificar Procedures que utilizam Cursos - ERRO ORA-01000 MAXIMUM OPEN CURSORS EXCEEDED

 Segue uma dica para quem possa estar passando pelo erro

  ERRO ORA-01000 MAXIMUM OPEN CURSORS EXCEEDED

 Verifique as procedures do seu SGDB que utilizam CURSOR e que possam estar esquecendo de fechar o cursor.Para descobrir as procedures ,utilize o script abaixo.



SELECT *
FROM DBA_SOURCE U
WHERE U.TYPE = 'PROCEDURE'
AND U.OWNER = :Powner
AND U.TEXT LIKE '%CURSOR%'

Oracle - SAFE - Simple Algorithm for Fragmentation

 Leitores.Segue um material que não foi escrito por mim mas que já me ajudou tanto que acho interessante a leitura de todos.

 Oracle - SAFE - Simple Algorithm for Fragmentation Elimination

Bloquear e desbloquear usuário

Essa foi um pedido de ajuda que recebi hoje e achei legal publicar.Me pediram um sql que mostrasse a lista dos usuários do nosso banco e o seu status atual.

 Segue um querie para ajudar :

 select username           as usuario
, account_status status as status
, created                      as data_de_criacao
, default_tablespace     as  tablespace
from dba_users
order by username

Para bloquear um usuário ou desbloquear o login deste usuáro , segue abaixo o exemplo :


alter user <nome_do_usuário> account lock;

alter user <nome_do_usuário> account unlock;

Verifique o tamanho dos Schemas no seu SGDB

 Seu SGDB cresceu e você não entende o porque ? Procure  o Schema que está ocupando mais espaço nas tablespaces do seu SGDB.


select obj.owner as "Schema",
        obj_cnt as "Qtd_Objects",
        decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj,
  (select owner, ceil(sum(bytes)/1024/1024) seg_size
   from dba_segments group by owner
 ) seg
where obj.owner  = seg.owner(+)

ANALISAR INDICES FRAGMENTADOS

Pessoal.Segue uma dica para analise de fragmentação dos indices .


(1) -- CRIAR A TABELA ONDE ARMAZENAMENTOS AS INFORMAÇÕES 
  
  CREATE TABLE SGDB_INDICE_STATUS (INDEX_NAME VARCHAR(100),PCT_DELETED NUMBER,PCT_USED NUMBER,BLOCK NUMBER,DTEXECUAO DATE DEFAULT SYSDATE) ;
  
  ALTER TABLE SGDB_INDICE_STATUS ADD CONSTRAINT PK_MMED_IND_STATUS PRIMARY KEY (INDEX_NAME,DTEXECUAO) ;  
  
(2) -- RODAR A QUERY ABAIXO E PEGAR O RESULTADO DELA PARA EXECUAR 
  
  SELECT COMM
  FROM(
  SELECT 1 AS ID, 
         ROWNUM AS ID2,
'ANALYZE INDEX ' ||INDEX_NAME || ' VALIDATE STRUCTURE ; ' AS COMM
            FROM DBA_INDEXES 
           WHERE OWNER = 'DBA_CLINICA' 
   
   UNION ALL
   
 SELECT 2 AS ID, 
        ROWNUM ID2, 
'INSERT INTO SGDB_INDICE_STATUS SELECT NAME,TRUNC(100*(NVL(DEL_LF_ROWS,1)/NVL(LF_ROWS,1))) PCT_DELETED, PCT_USED ,BLOCKS ,SYSDATE FROM INDEX_STATS ;' AS COMM
    FROM DBA_INDEXES 
   WHERE OWNER = 'DBA_CLINICA' AND 
    ORDER BY ID2,ID
)   

(3) -- EXECUTAR O RESULTADO DO PASSO 3 E DAR COMMIT .
  
  COMMIT ;
  
  
(4) -- VERIFICAR OS ÍNDICES COM MAIS DE 30 % DE FRAGMENTAÇÃO 
  
  SELECT DISTINCT A.* ,B.TABLE_NAME
    FROM  SGDB_INDICE_STATUS A
    JOIN  DBA_INDEXES B ON B.INDEX_NAME = A.INDEX_NAME
   WHERE PCT_DELETED > 20 
     AND PCT_DELETED < 100
   ORDER BY PCT_DELETED DESC
   

 (5) -- AJUSTA OS ÍNDICES QUE FORAM ENCONTADOS COM FRAGMENTAÇÃO MAIOR QUE 20 %

  -- RODAR A QUERY ABAIXO E DEPOIS EXECUTAR O RESULTADO
  
   SELECT DISTINCT 'ALTER INDEX ' || A.INDEX_NAME || ' REBUILD TABLESPACE TSP_CLINICA_INDEX ; ' AS RESULTADO
    FROM  SGDB_INDICE_STATUS A
    JOIN  DBA_INDEXES B 
 ON B.INDEX_NAME = A.INDEX_NAME
   WHERE PCT_DELETED > 20 
     AND PCT_DELETED < 100

 

Mover um Datafile de sua Tablespace

 segue um exemplo simples de uma das formas seguras de movermos um datafile .

 (1) Caso de sgdb em modo sem Archive Mode :

  Pare seu banco
  
  shutdown immediate;

 Depois faça a copia do datafile :

 copy  d:/u01/data/datafile.dbf  e:/u01/data/datafile_novo.dbf  

 Inicie o banco em modo mount 

 startup mount;

 faça o rename datafile

 ALTER DATABASE RENAME FILE ‘d:/u01/data/datafile.dbf’ TO ‘e:/u01/data/datafile_novo.dbf’;

 Inicie o banco em modo open ;

ALTER DATABASE OPEN;
  
 (2) Caso de sgdb em modo Archive Mode :

 coloque o seu datafile como offline :

 ALTER DATABASE DATAFILE ‘/u01/data/datafile.dbf’ OFFLINE;


 Depois faça a copia do datafile :

 copy  d:/u01/data/datafile.dbf  e:/u01/data/datafile_novo.dbf  

  faça o rename datafile

 ALTER DATABASE RENAME FILE ‘d:/u01/data/ datafile.dbf’ TO ‘e:/u01/data/datafile_novo.dbf’;

Fazer o recover do datafile:
RECOVER DATAFILE ‘e:/u01/data/datafile_novo.dbf’;



Colocar online o datafile:
ALTER DATABASE DATAFILE ‘ e:/u01/data/datafile_novo.dbf ’ ONLINE;

Oracle Critical Patch Update

 Pessoal.Segue um link onde de forma unificada a Oracle informa o Criticals Paths de seu produtos (incluindo os databases) :

http://www.oracle.com/technetwork/topics/security/alerts-086861.html#CriticalPatchUpdates

Fica a Dica.

Eliminar sessões bloqueadores por comandos DDL

Segue uma dica para momentos difíceis como os lock por ddl :

Eliminar sessões maléficas :

select 'alter system kill session ''' || ddl.session_id || ',' || ses.serial# || ''' immediate;'
from dba_ddl_locks ddl
, v$session ses
where owner like '%userid%'
and ddl.session_id = ses.sid

Rode o resultado da query .


Identificar o lock por ddl :

select decode(lob.kglobtyp, 
  0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
  4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
  7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
  11, 'PACKAGE BODY', 12, 'TRIGGER',
  13, 'TYPE', 14, 'TYPE BODY',
  19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
  22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
  28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
  32, 'INDEXTYPE', 33, 'OPERATOR',
  34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
  40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
  42, 'MATERIALIZED VIEW',
  43, 'DIMENSION',
  44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
  48, 'CONSUMER GROUP',
  51, 'SUBSCRIPTION', 52, 'LOCATION',
  55, 'XML SCHEMA', 56, 'JAVA DATA',
  57, 'SECURITY PROFILE', 59, 'RULE',
  62, 'EVALUATION CONTEXT','UNDEFINED'
 ) object_type
, lob.kglnaobj object_name
, pn.kglpnmod lock_mode_held
, pn.kglpnreq lock_mode_requested
, ses.sid
, ses.serial#
, ses.username
from v$session_wait vsw
, x$kglob  lob
, x$kglpn  pn
, v$session ses
where vsw.event = 'library cache lock'
and vsw.p1raw = lob.kglhdadr
and lob.kglhdadr = pn.kglpnhdl
and pn.kglpnmod != 0
and pn.kglpnuse = ses.saddr

Trabalhando com Triggers

Segue algumas dicas para quem trabalha com triggers no seu schema .

Listar as triggers do SGDB por Schema :

set lines 100 pages 999
select trigger_name
, trigger_type
, table_name
, status
from dba_triggers
where owner = '&owner'
order by status, table_name

Listar as triggers relacionadas a uma tabela do seu schema :

set lines 100 pages 999
select trigger_name
, trigger_type
, status
from dba_triggers
where owner = '&owner'
and table_name = '&table'
order by status, trigger_name

Habilitar/desabilitar um trigger :

alter trigger <trigger_name> enable
alter trigger <trigger_name> disable

Tempo que seu SGDB está Ativo

Segue uma dia de como você verificar o tempo que o seu SGDB oracle está ativo.

select startup_time - sysdate
  from v$instance

Oracle 11G - Senhas Case Sensitive

Pessoal.

 Ai vai uma dica boba para quem está migrando de uma versão inferior do Oracle 11 G para o mesmo e está tendo problema com o fato de  nesta versão o padrão ser que as senhas sejam "CASE SENSITIVE".segue o comando para retirar esse padrão .Tratasse de um novo parâmetro.

alter system set sec_case_sensitive_logon=False

Monitoramento de uso de Indices.

Deseja verificar se os índices do Schema estão sendo utilizados ? Siga estes passas

(1) Coloque os indices para ser monitorados :

 Rode a query abaixo,e depois pegue o resultado da mesma
e rode como um script.

SELECT 'ALTER INDEX '||INDEX_NAME||' MONITORING USAGE;' SQL
  FROM USER_INDEXES
 WHERE INDEX_TYPE = 'NORMAL'
 ORDER BY TABLE_NAME, INDEX_NAME;

(2)Deixe por um período de tempo estes indices em monitoramento.Depois disso
você pode executar um select na view V$OBJECT_USAGE e verificar o uso do seu
indice.

select INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORING
  from V$OBJECT_USAGE
 

 (3) Como toda monitoria tem custo,depois disso retire os mesmos indices do
 monitoramento.

  Rode a query abaixo,e depois pegue o resultado da mesma
e rode como um script.

SELECT 'ALTER INDEX '||INDEX_NAME||' NOMONITORING USAGE;' SQL
  FROM USER_INDEXES
 WHERE INDEX_TYPE = 'NORMAL'
 ORDER BY TABLE_NAME, INDEX_NAME;

Dependência entre Funções/Procedures

Pessoal Segue uma query que me ajuda muito para identificar a dependência entre funções/procedures no seu schema. SELECT UO.OBJECT_NAME, UD.NAME FROM USER_OBJECTS UO LEFT JOIN USER_DEPENDENCIES UD ON UO.OBJECT_NAME = UD.REFERENCED_NAME WHERE UO.OBJECT_TYPE IN ('FUNCTION','PROCEDURE') AND UD.REFERENCED_OWNER = :pSchema AND UD.REFERENCED_TYPE IN ('FUNCTION','PROCEDURE') ORDER BY UO.OBJECT_NAME

Analise do seu SGDB com o pacote DBMS_STATS

(1) Todo o seu SGDB Oracle (Tabelas e índices)

EXEC DBMS_STATS.gather_database_stats;

Utilizando somente X % das linhas para definição

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

Utilizando somente X % das linhas para definição e cascade nas estatisticas anteriores.

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

(2) Apenas um schema

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);

(3) Apenas uma tabela do seu schema.

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15, cascade => TRUE);


(4) Atualiação das estatisticas do dicionario de dados (data dictionary).

EXEC DBMS_STATS.gather_dictionary_stats;


(5) Somente os indices de um schema.

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);


(6) Atualizar estatistica do System Stats

EXEC DBMS_STATS.gather_system_stats;

(7) Outras formas de atualizar estatística (legado)


ANALYZE TABLE employees COMPUTE STATISTICS;
ANALYZE INDEX employees_pk COMPUTE STATISTICS;

ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;

EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE');
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_percent => 15);

EXEC DBMS_UTILITY.analyze_database('COMPUTE');
EXEC DBMS_UTILITY.analyze_database('ESTIMATE', estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_database('ESTIMATE', estimate_percent => 15);

(8) Exemplo de criação de job para execução continua de análise.

SET SERVEROUTPUT ON
DECLARE
l_jobEst NUMBER;
BEGIN
DBMS_JOB.submit(l_jobEst,
'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
SYSDATE,
'SYSDATE + 1');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || l_jobEst);
END;
/

EXEC DBMS_JOB.remove(X);
COMMIT;

Erro ORA-12899 em Import

Hoje tive participei da solução de um problema na execução do comando Imp.exe .No momento do import estávamos recebendo o seguinte erro.

ORA-12899: valor muito grande para a coluna "schema"."tabela"."coluna" (real: 105, máximo: 100).

Vale a pena observar a seguinte mensagem do log do import :


importação realizada nos conjuntos de caracteres WE8MSWIN1252 e NCHAR AL16UTF16
o servidor de importação usa o conjunto de caracteres AL32UTF8 (conversão de charset possível)

O nosso problema aconteceu pelo fato de o banco de origem ter um charset diferente do banco de destino.Qual a solução que tivemos ?No momento do export.exe devemos dizer o charset que queremos para garantir o import.

NLS_LANG="AMERICAN_AMERICA.AL32UTF8"

fica a dica

Configuração do modo ARCHIVELOG

Configuração do modo ARCHIVELOG

Para fazermos backups consistentes de um banco de dados Oracle aberto,precisamos que esta instancia esteja

no modo ARCHIVELOG ativado.Com isso conseguimos fazer o chamado BACKUP ON-LINE.

O Oracle armazena os dados do redo log de forma ciclica.Quando o ultimo arquivo do redo log ativo for

preenchido o processo LGWR (log Write) irá substituir o conteúdo do primeiro arquivo redo.

Se a instancia do banco de dados estiver em modo ARCHIVELOG , o processo ARC (archive) irá fazer uma cópia

redo log depois do processo LGWR terminar de gravar o redo log.

Para fazer uso do modo ARCHIVELOG algumas configurações devem ser feitas.Vamos listar a recomendadas :

(1) Configure o parametro db_recovery_file_dest

ex : log_archive_dest = 'c:\app\oracle\arch'

(2) Defina o parametro Log_arquive_dest_n (pelo menos 1 )

ex : log_archive_dest_1 = 'c:\app\oracle\arch'

log_archive_dest_2 = 'c:\app\oracle\arch\backup'

log_archive_dest_3 = 'c:\app\oracle\arch\backup\bkp'

(3) Ative o modo ARCHIVELOG

sql> shutdown immediate ;

sql> startup mount

sql> alter database archivelog ;

sql> alter database open ;

Materialized view

São nada mais que objetos do banco de dados Oracle que contém dados locais de tabelas remotas ou são usadas pra criar uma agregação de tabelas em um determinado intervalo de tempo..Segue algumas dicas de uso de uma Materialized View :

Criação :

create materialized view Minha_mview

refresh [fast | complete | force]

start with sysdate

next sysdate + 1/24

with primary key

as select * from dba_objects ;

Sendo :

refresh [fast | complete | force] as opções de refresh da view :

Fast = Somente alterações entre o intervalo.

Complete = Recria toda a estrutura da view materializada mesmo que não seja necessário.

Force = Faz o fast de ser for possível,caso contrário faz o processo complete.

start with sysdate :

Data de primeira populacao da view materializada.

next sysdate + 1/24 :

Data da próxima população da view .

WIDTH PRIMARY KEY :

É utilizado para criar a Chave-Primária, que é baseada na chave-primária da tabela master.

View para conhecer as Materialized view do seu database :

dba_mviews

dba_mview_refresh_times

Outros comandos úteis :

Fazer manualmente o refresh fast da MV :

execute dbms_mview.refresh ('');

Fazer manualmente o refresh Complete da MV :

execute dbms_mview.refresh ('','C');

Create Tablespace

Muito importante na criação da sua Tablespace levar em consideração :

(1) O volume de informações que serão armazenado nela

(2) O volume de leituras e escritas que estas informações irão receber.

(3) informações sobre disco e armazenamento físico(Velocidade de disco e etc..)

Digo isso pelo fato de ja ter visto um SGDB oracle com uma tablespace com datafile em um hdb 2.5'' conectado via porta ubs .Um tablespace que era acessada por +- 200 usuários em concorrencia.

Segue um exemplo básico da criação de um tablespace :

create tablespace TSP_AUDT

datafile 'C:\ORACLE\PRODUCT\11XE\TSP__AUDT.dbf'

size 128M

autoextend on

maxsize 1024M

extent management local

uniform size 64K

View para obter maiores informações sobre Tablespaces e Datafiles :

V$datafile

V$tablespace