Oracle Database Space Usage

Oracle Database Space Usage

1.       Whole Database Space Usage 

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;

2.       Datafiles-wise Usage

SELECT   SUM (a.BYTES) / 1024 / 1024 / 1024 allocated_bytes,

         SUM (b.free_bytes) / 1024 / 1024 / 1024

    FROM dba_data_files a,

         (SELECT   file_id, SUM (BYTES) free_bytes

              FROM dba_free_space b

          GROUP BY file_id) b

   WHERE a.file_id = b.file_id

ORDER BY a.tablespace_name;

3.       How to get Schema Size

set linesize 150

set pagesize 5000

col owner for a15

col segment_name for a30

col segment_type for a20

col TABLESPACE_NAME for a30

clear breaks

clear computes

compute sum of SIZE_IN_GB on report

break on report

select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner;

4.       How to get Particular Table Size

SELECT   segment_name table_name,

         SUM (BYTES) / (1024 * 1024) table_size_meg

    FROM dba_extents

   WHERE segment_type = 'TABLE' and segment_name ='<table-name>'

GROUP BY segment_name;

5.       How to get Size of all tables of particular schemas

SELECT   segment_name table_name,

         SUM (BYTES) / (1024 * 1024) table_size_meg

    FROM dba_extents

   WHERE segment_type = 'TABLE' and owner='TRIP'

GROUP BY segment_name;