Oracle – Coleta Estatísticas

—– 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

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

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

 

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

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

1 2 3 4 5 6 7 8

[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:

1 2 3 4 5

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:

1 2 3 4 5 6 7

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:

1 2 3 4

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:

1 2 3 4 5 6 7 8

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/

 

Linux – Exemplo contrab

# crontab -l


############ crontab 

# Example of job definition:

# .---------------- minute (0 - 59)

# |  .------------- hour (0 - 23)

# |  |  .---------- day of month (1 - 31)

# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...

# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,f                                                                                                                      ri,sat

# |  |  |  |  |

# *  *  *  *  * user-name command to be executed

Comandos SED

 

 

fontes:

http://thobias.org/doc/sosed.html#toc14

Verificando se servidor é virtual

Comando úteis para verificar se o servidor é virtual

[root@XPTO ~]# virt-what

vmware

[root@XPTO ~]# dmidecode -t system|grep ‘Manufacturer\|Product’        

Manufacturer: VMware, Inc.        

Product Name: VMware Virtual Platform

[root@XPTO ~]# dmesg |grep -i hypervisor

TSC freq read from hypervisor : 2660.000 MHz

[root@XPTO ~]# dmesg | grep -i vmware

DMI: VMware, Inc. VMware Virtual Platform/440BX Desktop Reference Platform, BIOS 6.00 06/22/2012
ACPI: SRAT 00000000bfeefdea 00128 (v02 VMWARE MEMPLUG  06040000 VMW  00000001)
ACPI: HPET 00000000bfeefdb2 00038 (v01 VMWARE VMW HPET 06040000 VMW  00000001)
ACPI: WAET 00000000bfeefd8a 00028 (v01 VMWARE VMW WAET 06040000 VMW  00000001)
ata2.00: ATAPI: VMware Virtual IDE CDROM Drive, 00000001, max UDMA/33
scsi 1:0:0:0: CD-ROM            NECVMWar VMware IDE CDR10 1.00 PQ: 0 ANSI: 5
scsi 2:0:0:0: Direct-Access     VMware   Virtual disk     1.0  PQ: 0 ANSI: 2
scsi 2:0:1:0: Direct-Access     VMware   Virtual disk     1.0  PQ: 0 ANSI: 2
VMware vmxnet3 virtual NIC driver – version 1.1.18.0-k-NAPI

Fontes :
http://unix.stackexchange.com/questions/3685/find-out-if-the-os-is-running-in-a-virtual-environment

SQL Server – verificando emails enviados

— ocorrencias de email reprovados

select * from msdb.dbo.sysmail_faileditems as items  inner join msdb.dbo.sysmail_event_log as l on items.mailitem_id = l.mailitem_id order by 1 desc

SQL Server – criação indices

A. Recriando um índice

O exemplo a seguir recria um índice único.

Transact-SQL

USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

B. Recriando todos os índices em uma tabela e especificando opções

O exemplo a seguir especifica a palavra-chave ALL. Isso recria todos os índices associados à tabela. Três opções são especificadas.

Transact-SQL

USE AdventureWorks2008R2;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
             STATISTICS_NORECOMPUTE = ON);
GO

SQL Server – listando todos os datafiles

—– List datafiles

SELECT db.name AS DBName, type_desc AS FileType, Physical_Name AS Location FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id

 

—– Listando os datafiles de um banco de dados apenas

use tempdb         —  esse é o nome da base que desejo saber os datafiles

go

sp_helpfile

go