Query to find the tables size occupied in GB in Oracle SQL for various SCHEMAS

Question

I am trying to find the space occupied by list of tables in Oracle DB (for various SCHEMAS ) ,currently i am using this query

QUERY 1 - DBA_SEGMENTS

SELECT SEGMENT_NAME,SUM (BYTES)/1024/1024/1024 AS GB,
ROUND(100*RATIO_TO_REPORT(GB) OVER (), 4) PERCENTAGE  
FROM DBA_SEGMENTS
WHERE OWNER='SCHEMA_NAME'
AND SEGMENT_TYPE ='TABLE'
AND SEGMENT_NAME
IN
(
'LIST OF TABLES'
)
GROUP BY SEGMENT_NAME,BYTES
ORDER BY GB DESC;

QUERY 2 - DBA_OBJECTS

SELECT DISTINCT OBJECT_NAME FROM DBA_OBJECTS
WHERE OWNER='SCHEMA'
AND OBJECT_NAME IN 
(
'LIST OF TABLES'
)
GROUP BY OBJECT_NAME
ORDER BY OBJECT_NAME;

when i compare the LIST OF TABLES counts it's not matching , what could be the reason ?

can you please suggest is this correct or is there a better way to achieve this ?

Reference https://serverfault.com/questions/132620/oracle-how-to-find-out-storage-space-used-by-a-table

Additional Query 1

SET SERVEROUTPUT ON;
DECLARE
  input_owner         NVARCHAR2(128) := 'SCHEMA_NAME';
  segment_size_blocks NUMBER;
  segment_size_bytes  NUMBER;
  used_blocks         NUMBER;
  used_bytes          NUMBER;
  expired_blocks      NUMBER;
  expired_bytes       NUMBER;
  unexpired_blocks    NUMBER;
  unexpired_bytes     NUMBER;
  total_blocks        NUMBER;
  total_bytes         NUMBER;
  unused_blocks       NUMBER;
  unused_bytes        NUMBER;
  last_ext_file_id    NUMBER;
  last_ext_blk_id     NUMBER;
  last_used_blk       NUMBER;
  result_table        NVARCHAR2(128);
  result_segment_type NVARCHAR2(128);
  result_used_mb      NUMBER;
  result_unused_mb    NUMBER;
  result_total_mb     NUMBER;
  CURSOR cur
  IS
    SELECT
      s.segment_name   AS segment_name,
      s.owner          AS segment_owner,
      s.partition_name AS partition_name,
      s.segment_type   AS segment_type,
      CASE WHEN s.segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
        THEN s.segment_name
      WHEN s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
        THEN (SELECT i.table_name
              FROM dba_indexes i
              WHERE s.segment_name = i.index_name AND s.owner = i.owner)
      WHEN s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
        THEN (SELECT l.table_name
              FROM dba_lobs l
              WHERE s.segment_name = l.segment_name AND s.owner = l.owner)
      WHEN s.segment_type IN ('LOBINDEX')
        THEN (SELECT l.table_name
              FROM dba_lobs l
              WHERE s.segment_name = l.index_name AND s.owner = l.owner)
      ELSE 'Unknown'
      END              AS table_name,
      s.bytes          AS segment_bytes
    FROM dba_segments s
    WHERE owner = input_owner
    and segment_name in 
    (

    )
    ORDER BY table_name, segment_type;
BEGIN
  dbms_output.put_line('table                         ; segment type        ;   used (mb)     ; unused (mb)     ;  total (mb)');

  FOR ro IN cur
  LOOP

    result_table := ro.table_name;
    result_segment_type := ro.segment_type;

    IF ro.segment_type IN ('TABLE', 'INDEX')
    THEN
      dbms_space.unused_space(
          segment_owner             => ro.segment_owner,
          segment_name              => ro.segment_name,
          segment_type              => ro.segment_type,
          total_blocks              => total_blocks,
          total_bytes               => total_bytes,
          unused_blocks             => unused_blocks,
          unused_bytes              => unused_bytes,
          last_used_extent_file_id  => last_ext_file_id,
          last_used_extent_block_id => last_ext_blk_id,
          last_used_block           => last_used_blk);

      result_used_mb := (total_bytes - unused_bytes) / 1024 / 1024;
      result_unused_mb := unused_bytes / 1024 / 1024;
      result_total_mb := total_bytes / 1024 / 1024;

    ELSIF ro.segment_type IN ('LOBSEGMENT')
    THEN
      dbms_space.space_usage(
          segment_owner           => ro.segment_owner,
          segment_name            => ro.segment_name,
          segment_type            => 'LOB',
          partition_name          => ro.partition_name,
          segment_size_blocks     => segment_size_blocks,
          segment_size_bytes      => segment_size_bytes,
          used_blocks             => used_blocks,
          used_bytes              => used_bytes,
          expired_blocks          => expired_blocks,
          expired_bytes           => expired_bytes,
          unexpired_blocks        => unexpired_blocks,
          unexpired_bytes         => unexpired_bytes
      );
      result_used_mb := used_bytes / 1024 / 1024;
      result_unused_mb := (segment_size_bytes - used_bytes) / 1024 / 1024;
      result_total_mb := segment_size_bytes / 1024 / 1024;
    ELSE
      -- TODO ??
      result_used_mb := ro.segment_bytes / 1024 / 1024;
      result_unused_mb := 0;
      result_total_mb := result_used_mb + result_unused_mb;
    END IF;

    dbms_output.put_line(
        RPAD(result_table, 30) || '; ' ||
        RPAD(result_segment_type, 20)|| '; ' ||
        TO_CHAR(result_used_mb  / 1024 / 1024, '999999999990D00')|| '; ' ||
        TO_CHAR(result_unused_mb  / 1024 / 1024, '999999999990D00')|| '; ' ||
        TO_CHAR(result_total_mb / 1024 / 1024, '999999999990D00'));
  END LOOP;
END;

Additional Query 2

WITH DA AS (
 SELECT OWNER, SEGMENT_NAME, SUM(BYTES)/1024/1024 SIZE_MB
   FROM DBA_EXTENTS
   GROUP BY ROLLUP(OWNER, SEGMENT_NAME)
) SELECT OWNER, SEGMENT_NAME, SIZE_MB, ROUND(SIZE_MB/TOTAL_MB*100)
  FROM DA 
    CROSS JOIN (
      SELECT SIZE_MB AS TOTAL_MB 
      FROM DA T WHERE OWNER IS NULL AND SEGMENT_NAME IS NULL
    )
ORDER BY SIZE_MB DESC

Answer 1

Here is a fairly accurate query that takes tables, indexes, and any partitioned tables/indexes into account for all tables for a single schema owner. Note that there will be some additional overhead in actual space used due to blocksize, file headers, etc.

  select segment_name
         , segment_type
         , sum(bytes)/1024/1024/1024 as GB
         , ROUND(100*RATIO_TO_REPORT(bytes) OVER (), 4) PERCENTAGE
  from dba_segments ds
  where
      owner ='SCOTT'
      and (
              segment_type like 'TABLE%'
              and segment_name in ('EMP', 'SALGRADE', 'EMP_BKP', 'DEPT')
          or
              (
                 segment_type like 'INDEX%'
                 and segment_name in (
                         select index_name from dba_indexes di
                             where owner ='SCOTT'
                               and di.table_name in ('EMP', 'SALGRADE', 'EMP_BKP', 'DEPT')
                         )
              -- more fun here for LOBs, etc.
             )
         )
  group by segment_name, segment_type, bytes
  order by GB desc;

Take a look at DBA_LOBS for additional criteria to add to the -- more fun here to pick up LOB information.