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;