Oracle – Tamanho Tablespaces

January 31, 2017 / robertomagalhaes / Leave a comment

------------------>TAMANHO TABLESPACES

set lines 132 
set pages 105
set pause off
set echo off
set feedb on

column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00


select a.tablespace_name, a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)", a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)", nvl(b.tot_used,0)/(1024*1024) "USED (MB)", (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from 
 (select tablespace_name, sum(bytes) physical_bytes, sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
 from dba_data_files
group by tablespace_name ) a, 
 (select tablespace_name, sum(bytes) tot_used from dba_segments group by tablespace_name ) b
where 
 a.tablespace_name = b.tablespace_name (+) and 
 a.tablespace_name not in (select distinct tablespace_name from dba_temp_files) 
 and a.tablespace_name not like 'UNDO%' 
order by 1;

Oracle – Coleta Estatísticas

November 23, 2016November 23, 2016 / robertomagalhaes / Leave a comment

—– Informações sobre coletas de estáticas de um schema

select
‘ – OWNER -> ‘||OWNER,
‘ – TABLE_NAME -> ‘||TABLE_NAME,
‘ – LAST_ANALYZED -> ‘||to_char(LAST_ANALYZED,’YYYY/MM/DD HH24:MI:SS’)
FROM SYS.DBA_TAB_STATISTICS where owner = ‘ORCL’;

—- gather tabelas particionadas

EXEC DBMS_STATS.GATHER_TABLE_STATS (‘SCOTT’, ‘MENSAL’, ‘JAN_2016’, GRANULARITY => ‘PARTITION’);
EXEC DBMS_STATS.GATHER_TABLE_STATS (‘SCOTT’, ‘MENSAL’, ‘FEV_2016’, GRANULARITY => ‘PARTITION’);

—- coleta estatísticas de um determinado schema e/ou de toda a base

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ‘SCOTT’);

exec dbms_stats.gather_database_stats;

—- Criando um Job para coletar estatísticas diariamente

begin
dbms_scheduler.create_job(job_name => ‘SCOTT.JOB_COLETA_STATS_DB_FPW’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘begin DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ”SCOTT”); end;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY; BYHOUR=03;BYMINUTE=57’,
enabled => TRUE,
auto_drop => FALSE,
comments => ‘Cria estatisticas para o database ORCL schema SCOTT todos os dias as 03:57 AM’);
end;
/

—– Criando um job para coletar a estatística de todo o database diariamente

begin
dbms_scheduler.create_job(job_name => ‘JOB_COLETA_STATS_DB’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘begin dbms_stats.gather_database_stats; end;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY; BYHOUR=22;’,
enabled => TRUE,
auto_drop => FALSE,
comments => ‘Cria estatisticas para o database’);
end;
/

Oracle – verificando se há tabelas utilizando partições

November 23, 2016 / robertomagalhaes / Leave a comment

set long 30

select substr(tp.TABLE_OWNER,1,25) “Owner”, tp.TABLE_NAME, substr(tp.PARTITION_NAME,1,28) “Partition name”, tp.HIGH_VALUE, substr(tp.TABLESPACE_NAME,1,28) “Tablespace” from dba_tab_partitions tp where tp.PARTITION_NAME = (select max(x.partition_name)
from dba_tab_partitions x
where x.table_owner = tp.table_owner and x.table_name = tp.table_name)
and tp.table_owner <> ‘SYSTEM’
order by table_owner, table_name, partition_name;

Oracle – Formatações

November 23, 2016November 23, 2016 / robertomagalhaes / 1 Comment

alter session set current_schema = BERTAO;

alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’ ;

set lines 450 pages 1000

col file_name format a70 ;

col member format a70 ;

obs:

Essas configurações podem ser adicionados no arquivo $ORACLE_HOME/sqlplus/glogin.sql , assim todas as vezes que houver uma query com campo tipo date, será exibido no format conforme acima (‘dd/mm/yy hh24:mi:ss’)

—- abaixo utilizado para o RMAN

export NLS_DATE_FORMAT=’dd/mm/yy hh24:mi:ss’

Obs:

Esse export pode ser adicionado no arquivo ~/.bash_profile (/home/oracle/.bash_profile)

—- outros

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS ;

Alterando o endereço do SCAN Listener

November 11, 2016November 11, 2016 / robertomagalhaes / Leave a comment

Abaixo segue exemplo de como trocar o endereço(single name) do SCAN:

Testando a resolução do novo single name utilizando o nslookup:

[root@teste01 ~]$ nslookup oracle-scantst2

Server: 192.168.250.203

Address: 192.168.250.203#53

Name: oracle-scantst2.oraclehome.com.br

Address: 192.168.250.197

Name: oracle-scantst2.oraclehome.com.br

Address: 192.168.250.198

Logado com o usuário GRID:

GRID-> srvctl config scan

SCAN name: oracle-scantst, Network: 1/192.168.250.0/255.255.255.0/bond0

SCAN VIP name: scan1, IP: /oracle-scantst/192.168.250.196

SCAN VIP name: scan2, IP: /oracle-scantst/192.168.250.195

GRID->

Parar o scan e scan listener:

GRID-> srvctl stop scan_listener

GRID-> srvctl stop scan

GRID-> srvctl status scan

SCAN VIP scan1 is enabled

SCAN VIP scan1 is not running

SCAN VIP scan2 is enabled

SCAN VIP scan2 is not running

Logado como ROOT alterar o endereço “single name“:

1	ROOT-> srvctl modify scan -n oracle-scantst2

Logando novamente como GRID, verificar as alterações:

GRID-> srvctl config scan

SCAN name: oracle-scantst2, Network: 1/192.168.250.0/255.255.255.0/bond0

SCAN VIP name: scan1, IP: /oracle-scantst2/192.168.250.198

SCAN VIP name: scan2, IP: /oracle-scantst2/192.168.250.197

Efetuar o update da configuração do serviço (-u) e iniciar:

GRID-> srvctl modify scan_listener -u

GRID-> srvctl start scan_listener

GRID-> srvctl status scan

SCAN VIP scan1 is enabled

SCAN VIP scan1 is running on node teste02

SCAN VIP scan2 is enabled

SCAN VIP scan2 is running on node teste01

GRID->

fontes :

http://www.oraclehome.com.br/2015/03/16/alterando-o-endereco-do-scan-listener/

Oracle Relatório Statspack

October 28, 2016October 28, 2016 / robertomagalhaes / Leave a comment

Pessoal,

Existem 2 relatórios no Oracle , AWR e Statspack

O Statspack é a versão gratuita, sem custos adicionais.

Abaixo o procedimento de instalação.

—-

create tablespace statspack datafile size 100m autoextend on next 100m maxsize 5000m;

@?/rdbms/admin/spcreate.sql

— criar automatico

@?/rdbms/admin/spauto.sql

—-Na execução do script será solicitado o intervalo de snapshots a serem removidos.

SQL> select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT order by 2

—– realizar purge manual

@$ORACLE_HOME/rdbms/admin/sppurge.sql

—–Para consultar os snpshots existentes, podemos usar o comando abaixo, conectados com o owner perfstat.

SQL> select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;

—- gerar relatorio

@?/rdbms/admin/spreport.sql

—-Para remover o statspack, baste executar o comando spdrop.sql.

@?/rdbms/admin/spdrop.sql

Fontes :

http://www.oraclehome.com.br/2014/04/16/implementando-o-statspack-oracle-1011g/

http://docs.oracle.com/cd/B10500_01/server.920/a96533/statspac.htm

Roberto Magalhães – Dicas

Oracle Database