[Resolvido] Coleta de Dados

[alphamek 2]

Olá,

 

Segue um script que criei para coletar informações básicas sobre um banco de dados e informações de saúde, como estatísticas de tabelas e índices e espaço utilizado em tablespaces.

 

REM ##############################################REM # COLETA DE DADOS							#REM # ---------------							#REM # Rodrigo Almeida Consultoria					   #REM # Autor : Rodrigo Almeida					#REM # E-mail: dbarodrigo@gmail.com				#REM # Data: 31/10/2006						   #REM # Versão: 1.0								#REM ##############################################set line 150set linesize 150col "Tamanho (MB)" format 999,999,999col SERVIDOR format a20col SO format a30col "MODO ARQUIVAMENTO" format a20col "TOTAL" format 999,999,999spool coleta_dados.txtprompt prompt ===========================================================prompt BANCO DE DADOSprompt ===========================================================prompt set pagesize 10000select a.instance_name as "INSTANCIA", a.host_name as "SERVIDOR",  b.platform_name as "SO", a.status as "STATUS", a.archiver as "MODO ARQUIVAMENTO",to_char(b.created,'DD-MM-RRRR HH24:MI:SS') as "DATA CRIACAO"from v$instance a, v$database bwhere a.instance_name=b.name;prompt prompt ===========================================================prompt BANCO DE DADOSprompt ===========================================================prompt select name, type, value from v$parameter;prompt prompt ===========================================================prompt VERSAO DO BANCO DE DADOSprompt ===========================================================prompt set pagesize 0select * from v$version;prompt prompt ===========================================================prompt VOLUMETRIA FISICA prompt ===========================================================prompt set pagesize 10000select sum(bytes)/1024/1024 as "Tamanho (MB)" from dba_data_files;prompt prompt ===========================================================prompt VOLUMETRIA LOGICA prompt ===========================================================prompt select sum(bytes)/1024/1024 as "Tamanho (MB)" from dba_segments;prompt prompt ===========================================================prompt RESUMO DE OCUPACAO DE ESPACO POR ESQUEMAprompt ===========================================================prompt select owner as "ESQUEMA", segment_type as "TIPO DE OBJETO", sum(bytes)/1024/1024 as "TAMANHO (MB)"from dba_segmentsgroup by owner, segment_typeorder by owner;prompt prompt ===========================================================prompt TABLESPACESprompt ===========================================================prompt select tablespace_name as "TABLESPACE", block_size as "BLOCO DE DADOS", status as "STATUS", logging as "LOGGING",extent_management as "GER.EXTENSAO", allocation_type as "TIPO DE ALOCACAO", segment_space_management as "GER.SEGMENTO",retention as "RETENCAO"from dba_tablespaces;prompt prompt ===========================================================prompt RESUMO POR TABLESPACESprompt ===========================================================prompt select decode(grouping(tablespace_name),0,null,1,'TOTAL (MB) =') as "1",tablespace_name as "TABLESPACE", segment_type as "TIPO DE OBJETO", sum(bytes)/1024/1024 as "TAMANHO (MB)"from dba_segmentsgroup by rollup(tablespace_name, segment_type)order by tablespace_name;prompt prompt ===========================================================prompt VERIFICACAO DE ESTATISTICAS NAS TABELASprompt ===========================================================prompt select decode(to_char(last_analyzed,'DD-MM-RRRR'),null,'SEM ESTATISTICA',to_char(last_analyzed,'DD-MM-RRRR')) as "ANALISE",count(table_name) as "TOTAL DE TABELAS"from dba_tablesgroup by to_char(last_analyzed,'DD-MM-RRRR')order by to_char(last_analyzed,'DD-MM-RRRR');prompt prompt ===========================================================prompt VERIFICACAO DE ESTATISTICAS NOS INDICESprompt ===========================================================prompt select decode(to_char(last_analyzed,'DD-MM-RRRR'),null,'SEM ESTATISTICA',to_char(last_analyzed,'DD-MM-RRRR')) as "ANALISE",count(index_name) as "TOTAL DE INDICES"from dba_indexesgroup by to_char(last_analyzed,'DD-MM-RRRR')order by to_char(last_analyzed,'DD-MM-RRRR');prompt prompt ===========================================================prompt VERIFICACAO DO STATUS NOS INDICESprompt ===========================================================prompt select a.status as "STATUS", count(a.index_name) as "TOTAL", sum(b.bytes)/1024/1024 as "TAMANHO (MB)"from dba_indexes a, dba_segments bwhere a.index_name=b.segment_namegroup by a.status;spool off

Abraços, http://forum.imasters.com.br/public/style_emoticons/default/joia.gif