-- zoznam objektov, ktore používajú tabuľkový priestor (tablespace) s menom TBLSPC

SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'TBLSPC';

-- počet MB, ktoré zaberá tabuľka MYTABLE

SELECT (NUM_ROWS * AVG_ROW_LEN)/(1024*1024) "USED [MB]" FROM DBA_TABLES WHERE TABLE_NAME = 'MYTABLE';

-- zoznam všetkých tabuľkových priestorov (tablespaces) v databáze + sumáre jednotlivých súborov (datafiles) každého tabuľkového priestoru

SELECT 
    TABLESPACE_NAME,
    ORDR,
    DATA_FILE,
    GB_MAX,
    GB_ALLOCATED,
    GB_USED,
    ROUND(GB_USED/GB_MAX*100,2) PERCENT_USED,
    GB_MAX-GB_USED AS GB_FREE 
FROM 
( 
    SELECT  
        A.TABLESPACE_NAME, 
        2 ORDR,
        'Summary:' AS DATA_FILE, 
        A.MAXBYTES/(1024*1024*1024) GB_MAX,
        A.BYTES/(1024*1024*1024) GB_ALLOCATED,
        (A.BYTES-B.BYTES)/(1024*1024*1024) GB_USED,
        B.BYTES/(1024*1024*1024) GB_FREE 
    FROM 
        (SELECT 
            TABLESPACE_NAME, 
            SUM(BYTES) BYTES, 
            SUM(MAXBYTES) MAXBYTES 
         FROM DBA_DATA_FILES 
         GROUP BY TABLESPACE_NAME) A, 
        (SELECT 
            TABLESPACE_NAME, 
            SUM(BYTES) BYTES 
         FROM DBA_FREE_SPACE 
         GROUP BY TABLESPACE_NAME) B 
    WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME 
    UNION ALL 
    SELECT  
        A.TABLESPACE_NAME,
        1 ORDR,
        A.FILE_NAME DATA_FILE,
        A.MAXBYTES/(1024*1024*1024) GB_MAX,
        A.BYTES/(1024*1024*1024) GB_ALOCATED,
        NULL AS GB_USED,
        (SELECT SUM(BYTES) BYTES FROM DBA_FREE_SPACE WHERE FILE_ID=A.FILE_ID)/(1024*1024*1024) AS GB_FREE 
    FROM DBA_DATA_FILES A 
)
ORDER BY TABLESPACE_NAME,ORDR,DATA_FILE;

-- pohľad, ktorý zobrazí počet GB využitého, alokovaného a voľného priestoru databázou na disku

SELECT    
    ROUND(SUM(USED.BYTES)/(1024*1024*1024)) || ' GB' "DATABASE SIZE",   
    ROUND(SUM(USED.BYTES)/(1024*1024*1024)) - ROUND(FREE.P/(1024*1024*1024)) || ' GB' "USED SPACE",    
    ROUND(FREE.P/(1024*1024*1024)) || ' GB' "FREE SPACE"
FROM    
    (SELECT BYTES FROM V$DATAFILE
     UNION ALL
     SELECT BYTES FROM V$TEMPFILE
     UNION ALL
     SELECT BYTES FROM V$LOG
    ) USED,    
    (SELECT SUM(BYTES) AS P    
     FROM DBA_FREE_SPACE
    ) FREE
GROUP BY FREE.P;

S pozdravom