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;