Menu de navegação

[PetersonParticipante]

Bom dia Galera,

tô fazendo um script pra me dar o número de datafiles por tablespace, espaço alocado, usado e livre. Fiz o seguinte código:


SELECT ts.name TS,
COUNT(df.file#) Datafiles,
SUM(df.bytes)/1048576000 Alocado_GB,
(SUM(df.bytes)/1048576000) - (SUM(NVL(dfs.bytes,0))/1048576000 Usado GB,
SUM(NVL(dfs.bytes,0))/1048576000 Livre_GB
FROM v$datafile df
JOIN v$tablespace ts USING(ts#)
JOIN dba_free_space dfs ON dfs.file_id = df.file#
GROUP BY ts.name
ORDER BY 1;


Porém a query me diz que tenho mais de 100 datafiles por TS, quando na verdade tenho apenas 1. Me ajudem aí amigos, valeu!

[Victor ArmbrustMestre]

Peterson,

Eu uso este aqui: (Vai te retornar uma listagem das tablespaces com espaço usado, free, etc.. e vai pedir o nome da Tablespace que vc quer consultar com seus respectivos datafiles)

Código: (Joga dentro de um arquivo .sql)

prompt
prompt ===================================================================
prompt Nome da Instance, Host e Versao
prompt ===================================================================

set lines 200
col versao form a12
col data_atual for a20
col dias_ativos for 999
col startup_time for a20
col host_name for a15
col instance_name for a15

select upper(instance_name) as instance_name, host_name, ‘[‘||version||’]’ VERSAO,
to_char(sysdate,’DD/MM/YYYY HH24:MI’) DATA_ATUAL,
to_char(STARTUP_TIME,’DD/MM/YYYY HH24:MI’) STARTUP_TIME,
ROUND(sysdate-startup_time,1) DIAS_ATIVOS
from v$instance
/
prompt
prompt ===================================================================
prompt Relacao de Tablespaces
prompt ===================================================================

set pagesize 100
set lines 200
column pct_used format 999.9
column pct_used heading “%|Used”
column name format a36 heading “Tablespace Name”
column bytes format 999,999,999,999,999 heading “Total Bytes”
column maxbytes format 999,999,999,999,999 heading “Max Bytes”
column used format 999,999,999,999,999 heading “Used”
column free format 999,999,999,999,999 heading “Free”
column name justify left
column bytes justify right
column used justify right
column free justify right
column pct_used justify right
column dummy noprint
break on report
compute sum label ‘Total Bytes’ of bytes on report
compute sum of free on report
compute sum of used on report
compute sum of maxbytes on report

select a.tablespace_name as name,
b.tablespace_name as dummy,
sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id )/1024/1024 as bytes,
sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id )/1024/1024 –
sum(a.bytes)/count( distinct b.file_id )/1024/1024 as used,
sum(b.maxbytes)/count( distinct a.file_id||’.’||a.block_id )/1024/1024 as maxbytes,
sum(a.bytes)/count( distinct b.file_id )/1024/1024 as free,
100 * ( (sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id )) –
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id )) as pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name
order by pct_used, bytes
/

set lines 500
set pages 400
set trimout on
set trimspool on
set feed on
set term on
set verify off

col tablespace_name for a10 heading “Tablespace”
col file_name for a50 heading “File Name”
col maxbytes for 9999999999999 heading “Max Bytes”
col bytes for 999,999,999,999 heading “Total Bytes”
col bytes_free for 999,999,999,999 heading “Free”
col bytes_used for 999,999,999,999 heading “Used”
col autoextensible for a2 heading “AE”

compute sum label ‘Total Bytes’ of bytes on report
compute sum of bytes_free on report
compute sum of bytes_used on report
break on tablespace_name on report

undef tablespace

prompt ===================================================================
prompt Verfica quais datafiles estao na Tablespace Desejada:
prompt ===================================================================

prompt
accept tablespace prompt ‘Qual Tablespace deseja verificar ? ‘

select a.tablespace_name, a.file_name, a.maxbytes,
a.bytes, nvl(a.bytes-sum(b.bytes),a.bytes) as BYTES_USED,
nvl(sum(b.bytes),0) as BYTES_FREE
from dba_data_files a, dba_free_space b
where b.file_id(+)=a.file_id
and a.tablespace_name like nvl(upper(‘&tablespace’),’%’)
group by a.tablespace_name, a.file_name, a.bytes, a.maxbytes
order by a.file_name asc
/

[vieriParticipante]

este aqui é a query que o enterprise manager usa para montar gráfico dinâmico em java que vc ve bonitinho ai no dbconsole ou no grid control.

Vale a pena rodar o resultado é ótimo visualmente.

break on report
compute sum of tbs_size_mb on report
compute sum of used on report
compute sum of avail on report

column tsname format a20 heading ‘Tablespace Name’
column tbs_size_mb format 999,999 heading ‘Size|(MB)’
column used format 999,999 heading ‘Used|(MB)’
column avail format 999,999 heading ‘Free|(MB)’
column used_visual format a11 heading ‘Used’
column pct_used format 999 heading ‘% Used’
column flname format a50 heading ‘Filename’
column siz format 999,999,990 heading ‘File Size|(MB)’
column maxsiz format 999,999,990 heading ‘Max Size|(MB)’
column pctmax format 990 heading ‘Pct|Max’

set linesize 1000
set trimspool on
set pagesize 32000
set verify off
set feedback off

PROMPT
PROMPT *************************
PROMPT *** TABLESPACE STATUS ***
PROMPT *************************

SELECT df.tablespace_name tsname
, sum(df.bytes)/1024/1024 tbs_size_mb
, nvl(sum(e.used_bytes)/1024/1024,0) used
, nvl(sum(f.free_bytes)/1024/1024,0) avail
, rpad(‘ ‘||rpad(‘X’,round(sum(e.used_bytes)
10/sum(df.bytes),0), ‘X’),11,’-‘) used_visual
, nvl((sum(e.used_bytes)100)/sum(df.bytes),0) pct_used
FROM sys.dba_data_files df
, (SELECT file_id
, sum(nvl(bytes,0)) used_bytes
FROM sys.dba_extents
GROUP BY file_id) e
, (SELECT max(bytes) free_bytes
, file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id(+) = df.file_id
AND df.file_id = f.file_id(+)
GROUP BY df.tablespace_name
ORDER BY 6
/

[PetersonParticipante]

Vieri e Varmbrust,

Muito obrigado pela ajuda, foi muito útil e resolveu meu problema, além de contribuir para meu entendimento das querys.

Obrigado denovo!

[Victor ArmbrustMestre]

[quote=”Peterson”:9rxum4mb]Vieri e Varmbrust,

Muito obrigado pela ajuda, foi muito útil e resolveu meu problema, além de contribuir para meu entendimento das querys.

Obrigado denovo![/quote]

Tranquilo cara, precisando é só falar…
Dá um look depois no meu blog, sempre posto algumas coisas interessantes la…

abs

[Autor]

Posts