quarta-feira, 29 de abril de 2009

Verificação de Espaço

Consultas básicas para verificação de espaços da sua Instância:

Database size

SELECT 'Database Size' "*****"
,round(sum(round(sum(nvl(fs.bytes/1024/1024,0)))) /
sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/
1024 - sum(nvl(fs.bytes/1024/1024,0)))) * 100, 0) "%Free"
,round(sum(round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/
1024,0)))) / sum(round(sum(nvl(fs.bytes/1024/1024,0))) +
round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) * 100,
0) "%Used"
,sum(round(sum(nvl(fs.bytes/1024/1024,0)))) "Mb Free"
,sum(round(df.bytes/1024/1024
- sum(nvl(fs.bytes/1024/1024,0)))) "Mb Used"
,sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/
1024
- sum(nvl(fs.bytes/1024/1024,0)))) "Size"
FROM dba_free_space fs, dba_data_files df
WHERE fs.file_id(+) = df.file_id
GROUP BY df.tablespace_name, df.file_id, df.bytes,
df.autoextensible
ORDER BY df.file_id;

Tablespace size

COL Tablespace FORMAT a30;
SELECT df.tablespace_name "Tablespace"
,round((sum(nvl(fs.bytes,0))/ (df.bytes)) * 100) "%Free"
,round(((df.bytes - sum(nvl(fs.bytes,0)))
/ (df.bytes) ) * 100) "%Used"
,round(sum(nvl(fs.bytes/1024/1024,0))) "Mb Free"
,round(df.bytes/1024/1024
- sum(nvl(fs.bytes/1024/1024,0))) "Mb Used"
FROM dba_free_space fs, dba_data_files df
WHERE fs.file_id(+) = df.file_id
GROUP BY df.tablespace_name, df.file_id, df.bytes,
df.autoextensible
ORDER BY df.file_id;

Extent size

COL Object FORMAT a24;
COL Type FORMAT a5;
SELECT segment_name "Object", segment_type "Type"
,ROUND(SUM(bytes)/1024/1024) "Mb", ROUND(SUM(bytes)/1024) "Kb"
,SUM(bytes) "Bytes", SUM(blocks) "Blocks"
FROM dba_extents
WHERE owner = &OWNER AND segment_type IN ('TABLE','INDEX')
GROUP BY segment_name, segment_type
ORDER BY segment_name, segment_type DESC;

Segment size

COL Object FORMAT a24;
COL Type FORMAT a5;
SELECT segment_name "Object", segment_type "Type"
,ROUND(bytes/1024/1024) "Mb", ROUND(bytes/1024) "Kb"
,bytes "Bytes", blocks "Blocks"
FROM dba_segments WHERE owner = &OWNER
AND segment_type IN ('TABLE','INDEX')
ORDER BY segment_name, segment_type DESC;

Nenhum comentário:

Postar um comentário

Webcenter Content - Increase Number of SDATA Sections when using OracleTextSearch

About SDATA Oracle UCM uses the SDATA section feature in Oracle Text to index important text, date, and integer fields and define them a...