oracle: определение размеров табличных пространств, таблиц и т.д.

--
-- Свободное место в табличных пространствах
--
set linesize 200
set pagesize 49999
set serveroutput on size 1000000


col tablespace_name heading 'Tablespace' format a30
col total heading 'Total (Mb)' format 999999.99
col free heading 'Free (Mb)' format 999999.99
col used heading 'Used (%)' format 99.99

select to_char(sysdate, 'dd-mm-yyyy, HH24:MI:SS') TIME from dual;

select
    tablespace_name,
    sum(size_mb) total,
    sum(free_mb) free,
    round((sum(size_mb)-sum(free_mb))*100/sum(size_mb),2) used
    from (
    SELECT
       f.tablespace_name ,
       round(max(f.bytes)/1024/1024,2) size_mb,
       round(sum(nvl(fs.bytes,0))/1024/1024,2) free_mb
    FROM
        dba_data_files f,
        dba_free_space fs
    WHERE
        f.tablespace_name = fs.tablespace_name
        AND f.file_id=fs.file_id
    GROUP BY
        f.tablespace_name,f.file_id
)
group by
tablespace_name
order by used desc;

--
-- Место, которое занимает объект (таблица)
--
desc dba_tables;
desc dba_segments;
desc dba_extents;

-- инфа по екстентам объекта

select * from dba_extents where owner='GUPPI' and SEGMENT_TYPE='TABLE' and SEGMENT_NAME='T';

-- инфа по сегментам объекта, о размере и количестве блоков объекта

select * from dba_segments where owner='GUPPI' and SEGMENT_TYPE='TABLE' and SEGMENT_NAME='T';

-- инфа о размере и количестве блоков объекта

select OWNER, SEGMENT_NAME, SEGMENT_TYPE, sum(BYTES)/1024 as "Size, KB", sum(BLOCKS) 
from dba_extents
where owner='GUPPI' and SEGMENT_TYPE='TABLE' and SEGMENT_NAME='T'
group by OWNER, SEGMENT_NAME, SEGMENT_TYPE;


--
-- Свободное место (MB_FREE) , количество фрагметов (FRAGMENTS), размер самого 
-- большого фрагмента (BIGGEST_BIT)
-- для табличного пространства
--
select tablespace_name, to_char(sum(bytes)/1048576, '99,999,990.99')
        as MB_FREE, count(*) as FRAGMENTS, to_char(max(bytes)/1048576,'99,999,990.99') as BIGGEST_BIT
from dba_free_space
where tablespace_name='TOOLS'
group by tablespace_name;