Oracle Database 11g: Interactive Quick Reference

Ola .Para os que gostariam de ter um bom material de apoio para conhecimento do banco de Dados Oracle 11G segue uma dica. A oracle lancou o Oracle Database 11g: Interactive Quick Reference.


Segue o Link

http://landingpad.oracle.com/webapps/dialogue/ns/dlgwelcome.jsp?p_ext=Y&p_dlg_id=9575302&src=7027600&Act=54

Parametros de Inicialização não Default

Veja os parametros que não são default do seu orcle que estao configurados.

set pages 999 lines 100
col name format a30
col value format a50
select  name
, value
from v$parameter
where isdefault = 'FALSE'
and value is not null
order by name

Verifique Fragmentação de objetos do seu Schema

Nota: quantos mais extents, maior será a fragmentação

col MAX_EXTENTS format 999999999999
break on OWNER;

select substr(owner,1,15), substr(SEGMENT_NAME, 1,30), substr(SEGMENT_TYPE,1,10),
substr(TABLESPACE_NAME,1,20), EXTENTS, MAX_EXTENTS
from dba_segments
where owner like ('P%')
order by owner, extents desc;

Veja Sessões de sua instancia

Veja algumas informações de sua intancia oracle

select p.username pu
, s.username su
, s.status stat
, s.sid ssid
, s.serial# sser
, substr(p.spid,1,8) spid
, substr(sa.sql_text,1,2000) txt
from v$process p
, v$session s
, v$sqlarea sa
where p.addr = s.paddr
and s.username is not null
and s.sql_address = sa.address(+)
and s.sql_hash_value = sa.hash_value(+)
and s.status = 'ACTIVE'
order by 1,2,7

Veja as sessões TOP WAITERS

select sid,username||'@'||machine username,substr(module,1,40) module,
to_char(logon_time,'RRRR-MM-DD HH24:MI:SS') logon_time_txt
,lpad(trunc(last_call_et/3600)||'h'||lpad(mod(trunc(last_call_et/60),60),2,'0')||'m'||lpad(mod(last_call_et,60),2,'0')||'s',12,' ') last_call_et_txt
,lpad(' ('||last_call_et||' secs)',16,' ') last_call_et_secs
from v$session
where status='ACTIVE'
and type<>'BACKGROUND'
and username is not null and nvl(module,'XXX') not like 'backup incr datafile%'
and last_call_et>0
order by last_call_et desc;

CRIAR INDICE PARA FOREING KEY SEM INDICE

CREATE SEQUENCE SEQ_INDX START WITH 1 ;

SELECT 'CREATE INDEX IDX_TS_' || TO_CHAR(SEQ_INDX.NEXTVAL) || ' ON ' || TABLE_NAME || '(' || COLUMN_NAME || ') TABLESPACE SYSTEM PCTFREE 10 STORAGE(INITIAL 81920 ) ;'
FROM
(
SELECT DISTINCT X.COLUMN_NAME,X.TABLE_NAME
FROM DBA_CONS_COLUMNS X
JOIN DBA_CONSTRAINTS Z ON Z.CONSTRAINT_NAME = X.CONSTRAINT_NAME
AND Z.CONSTRAINT_TYPE = 'R'
WHERE X.OWNER = '&owner'
AND NOT EXISTS ( SELECT TABLE_NAME ,COLUMN_NAME
FROM DBA_IND_COLUMNS Y
WHERE Y.TABLE_NAME = X.TABLE_NAME
AND Y.COLUMN_NAME = X.COLUMN_NAME
)
ORDER BY TABLE_NAME,COLUMN_NAME
) ;


DEPOIS É RODAR O RESULTADO DA QUERIE ACIMA

Estatísticas

Atualiza a estatistica de um schema :

execute dbms_stats.gather_schema_stats('SCOTT');

Atualize a estatistica da sua intancia :

execute dbms_stats.gather_database_stats;

Atualize a estatistica de uma tabela do seu schema

exec dbms_stats.gather_table_stats('', '');

Analise com anda a estatistica dos objetos 

set pages 999 lines 100
select a.owner
, a.total_tables tables
, nvl(b.analyzed_tables,0) analyzed
from (select owner
 , count(*) total_tables
 from dba_tables
 group by owner) a
, (select owner
 , count(last_analyzed) analyzed_tables
 from dba_tables
 where last_analyzed is not null
 group by owner) b
where a.owner = b.owner (+)
and a.owner not in ('SYS', 'SYSTEM')
order by a.total_tables - nvl(b.analyzed_tables,0) desc
/

Indexação em Tabelas/Colunas

Veja suas colunas indexadas

select  INDEX_NAME,TABLE_OWNER,TABLE_NAME,column_name
from  dba_ind_columns
order  by column_position

Veja suas Tabelas indexadas

select i.index_name
, i.tablespace_name
, ceil(s.bytes / 1048576) "Size MB"
from  dba_indexes i
, dba_segments s
where  i.index_name = s.segment_name
order  by 2, 1

Querys em execução na sua Instancia

select sql_text
from v$sqlarea
where users_executing > 0 ;

Espaço Livre das Tablespaces

set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/

Tamanho da sua Intancia

Veja o tamanho da instancia do seu oracle :

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
 round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select bytes
 from v$datafile
 union all
 select bytes
 from  v$tempfile
 union  all
 select  bytes
 from  v$log) used
, (select sum(bytes) as p
 from dba_free_space) free
group by free.p

/

Verificar Sessões Bloqueadoras/bloqueadas

select 'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

Sql para mostrar todas as sessões conectadas

set lines 100 pages 999
col ID format a15
select username
,      sid || ',' || serial# "ID"
,      status
,      last_call_et "Last Activity"
from   v$session
where  username is not null
order by status desc
,        last_call_et desc
/