Tablespace - Espacio - Tablas

• Ver - Tablespace - Espacios - Ocupación

Select
df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Tamaño (Mb)",
Sum(fs.bytes) / (1024 * 1024) "Libre (Mb)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Libre",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Usado"
From  dba_free_space fs,
(Select tablespace_name,SUM(bytes) bytes From  dba_data_files
Group by  tablespace_name ) df
Where
fs.tablespace_name (+)  = df.tablespace_name
Group by  df.tablespace_name,df.bytes

Union All

Select
df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
Sum(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
From  dba_temp_files fs,
( Select tablespace_name,bytes_free,bytes_used From v$temp_space_header
Group by tablespace_name,bytes_free,bytes_used) df
Where
 fs.tablespace_name (+)  = df.tablespace_name
 Group by  df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
Order by 5 Desc;

• Ver Ocupación por Tabla (Incluyendo Indices) del usuario conectado

select sum(Table_Allocation_MB), table_name
 from
(
select sum(user_segments.bytes)/1024/1024 Table_Allocation_MB,user_indexes.table_name
 from user_segments, user_indexes
where user_segments.segment_type in ('INDEX') and user_segments.segment_name=user_indexes.index_name
GROUP BY  user_indexes.table_name
union all
select sum(bytes)/1024/1024 Table_Allocation_MB,segment_name from user_segments
where segment_type in ('TABLE')
GROUP BY segment_name
)
group by table_name order by 1 desc


También podemos agregarle a la consulta la ocupación de los tipos de datos LOB.

select sum(Table_Allocation_MB), table_name from (select sum(user_segments.bytes)/1024/1024 Table_Allocation_MB,user_indexes.table_name from user_segments, user_indexes where user_segments.segment_type in ('INDEX') and user_segments.segment_name=user_indexes.index_nameGROUP BY user_indexes.table_name
union all

select sum(bytes)/1024/1024 Table_Allocation_MB,segment_name from user_segmentswhere segment_type in ('TABLE')GROUP BY segment_name
UNION ALL

select sum(bytes)/1024/1024 Table_Allocation_MB,segment_name from user_segmentswhere segment_type IN ('LOBINDEX','LOBSEGMENT')GROUP BY segment_name) group by table_nameorder by 1 desc