Show Space Used in tablespace

select Tablespace_Name, /*Tablespace name*/ Owner, /*Owner of the segment*/ Segment_Name, /*Name of the segment*/ Segment_Type, /*Type of segment (ex. TABLE, INDEX)*/ Extents, /*Number of extents in the segment*/ Blocks, /*Number of db blocks in the segment*/ Bytes /*Number of bytes in the segment*/ from DBA_SEGMENTS where owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM'


Sum Space by Tablespace
-----------------------------
select tablespace_name, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS  group by tablespace_name

Space Usage for Database in Meg
------------------------------

tti "Space Usage for Database in Meg"

SELECT Total.name "Tablespace Name",
       nvl(Free_space, 0) Free_space,
       nvl(total_space-Free_space, 0) Used_space,
       total_space
FROM
  (select tablespace_name, sum(bytes/1024/1024) Free_Space
     from sys.dba_free_space
    group by tablespace_name
  ) Free,
  (select b.name,  sum(bytes/1024/1024) TOTAL_SPACE
     from sys.v_$datafile a, sys.v_$tablespace B
    where a.ts# = b.ts#
    group by b.name
  ) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name
/

tti off

Shows current size, used & freespace within the datafiles.
-----------------------------------------------------------------

TTI "Allocated, Used & Free space within datafiles"


COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90


SELECT   SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
         ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
               used_mb,
         NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
    FROM v$datafile df, dba_free_space dfs
   WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;


TTI off

Used space in Meg by segment type

--------------------------------------------------

set pagesize 50000

set line 80

col "Total Used Meg" format 999,999,990

col "Data part" format 999,999,990

col "Index part" format 999,999,990

col "LOB part" format 999,999,990

col "RBS part" format 999,999,990

tti 'Used space in Meg by segment type'

select sum(bytes)/1024/1024 "Total Used",

       sum( decode( substr(segment_type,1,5), 'TABLE',     bytes/1024/1024, 0))

                "Data part",

       sum( decode( substr(segment_type,1,5), 'INDEX',     bytes/1024/1024, 0))

                "Index part",

       sum( decode( substr(segment_type,1,3), 'LOB',       bytes/1024/1024, 0))

                "LOB part",

       sum( decode(segment_type,              'ROLLBACK',  bytes/1024/1024, 0))

                "RBS part",

        sum( decode(segment_type,             'TEMPORARY', bytes/1024/1024, 0))

                "TEMP part"

from   sys.dba_segments

/

tti off

tti "Total database size"

select sum(bytes)/1024/1024 "Total DB size in Meg"

  from sys.v_$datafile

/

tti off