Check table size

Main script


create table WGPOMSUSR_size as

SELECT table_name "SEGMENT_NAME",
       trunc(SUM(bytes) / 1024 / 1024) "SIZE_MB",owner
FROM   (SELECT segment_name table_name,
               owner,
               bytes
        FROM   dba_segments
        WHERE  segment_type = 'TABLE'
        UNION ALL
        SELECT i.table_name,
               i.owner,
               s.bytes
        FROM   dba_indexes  i,
               dba_segments s
        WHERE  s.segment_name = i.index_name
        AND    s.owner = i.owner
        AND    s.segment_type = 'INDEX'
        UNION ALL
        SELECT l.table_name,
               l.owner,
               s.bytes
        FROM   dba_lobs     l,
               dba_segments s
        WHERE  s.segment_name = l.segment_name
        AND    s.owner = l.owner
        AND    s.segment_type = 'LOBSEGMENT'
        UNION ALL
        SELECT l.table_name,
               l.owner,
               s.bytes
        FROM   dba_lobs     l,
               dba_segments s
        WHERE  s.segment_name = l.index_name
        AND    s.owner = l.owner
        AND    s.segment_type = 'LOBINDEX')
WHERE  owner ='WGPOMSUSR'
GROUP  BY table_name,
          owner order by "SIZE_MB" desc;

**********************************************************************************
SELECT
sum(bytes/1048576) MB, sysdate
from DBA_SEGMENTS
WHERE
OWNER = 'HMA_TM_PROD_GEN2_MONITOR' AND
SEGMENT_NAME = 'ZZT_SERVICE_LOG' AND
SEGMENT_TYPE = 'TABLE PARTITION'
/


Including LOBS
SELECT SUM(bytes)/1024/1024/1024 gb
  FROM dba_segments
 WHERE (owner = 'HMA_TM_PROD_GEN2_MONITOR' and
       segment_name = 'ZZT_SERVICE_LOG')
    OR (owner, segment_name) IN (
        SELECT owner, segment_name
          FROM dba_lobs
         WHERE owner = 'HMA_TM_PROD_GEN2_MONITOR'
            AND table_name = 'ZZT_SERVICE_LOG' )


Including everything

SELECT segment_name, sum(size_mb) size_mb
FROM (
SELECT CASE WHEN x.segment_type in ('LOBSEGMENT','LOB PARTITION') THEN
  (SELECT table_name FROM dba_lobs y WHERE y.segment_name= x.segment_name)
  WHEN x.segment_type='LOBINDEX' THEN
  (SELECT table_name FROM dba_lobs y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in('INDEX','INDEX PARTITION','INDEX SUBPARTITION') THEN
  (SELECT y.table_name FROM dba_indexes y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in ('TABLE SUBPARTITION', 'TABLE PARTITION','TABLE')
  THEN x.segment_name END segment_name
,round(sum(x.bytes)/(1024*1024),2) size_mb
FROM dba_segments x
WHERE x.owner ='HMA_TM_PROD_GEN2_MONITOR'
GROUP BY x.segment_name, x.segment_type
)
GROUP BY segment_name;



Everything round to 2 decimals in GB

SELECT segment_name, round(sum(size_mb/1024),2) size_Gb
FROM (
SELECT CASE WHEN x.segment_type in ('LOBSEGMENT','LOB PARTITION') THEN
  (SELECT table_name FROM dba_lobs y WHERE y.segment_name= x.segment_name)
  WHEN x.segment_type='LOBINDEX' THEN
  (SELECT table_name FROM dba_lobs y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in('INDEX','INDEX PARTITION','INDEX SUBPARTITION') THEN
  (SELECT y.table_name FROM dba_indexes y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in ('TABLE SUBPARTITION', 'TABLE PARTITION','TABLE')
  THEN x.segment_name END segment_name
,round(sum(x.bytes)/(1024*1024),2) size_mb
FROM dba_segments x
WHERE x.owner ='HMA_MDM' and SEGMENT_NAME in ('C_CUSTOMER','C_CUSTOMER_ADDRESS','C_PHONE','C_EMAIL','C_CUSTOMER_DEMOGRAPHICS','C_CUSTOMER_MODEL_SCORE','C_CUSTOMER_STATUS','C_CUSTOMER_VEHICLE','C_GROUP','C_GROUP_CUSTOMER','C_GROUP_VEHICLE','C_HOUSEHOLD','C_HOUSEHOLD_CUSTOMER','C_HOUSEHOLD_VEHICLE'
)
GROUP BY x.segment_name, x.segment_type
)
GROUP BY segment_name;





****************************************************************************
Top tables in database
SELECT * FROM (
  SELECT
    OWNER, SEGMENT_NAME, round((BYTES/1024/1024/1024),2) SIZE_GB
  FROM
    DBA_SEGMENTS
WHERE
      SEGMENT_TYPE = 'TABLE'
  ORDER BY
    BYTES/1024/1024/1024  DESC ) WHERE ROWNUM <= 100;


******************************************************************************

DEFINE schema_name = 'replace_with_your_schema_name'

SELECT * FROM (
  SELECT
    owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg,
    tablespace_name, extents, initial_extent,
    ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg
  FROM (
    -- Tables
    SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,
          segment_name AS table_name, bytes,
          tablespace_name, extents, initial_extent
    FROM   dba_segments
    WHERE  segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
    UNION ALL
    -- Indexes
    SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,
          i.table_name, s.bytes,
          s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_indexes i, dba_segments s
    WHERE  s.segment_name = i.index_name
    AND    s.owner = i.owner
    AND    s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
    -- LOB Segments
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,
          l.table_name, s.bytes,
          s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.segment_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBSEGMENT'
    -- LOB Indexes
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,
          l.table_name, s.bytes,
          s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.index_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBINDEX'
  )
  WHERE owner in UPPER('&schema_name')
)
WHERE total_table_meg > 10
ORDER BY total_table_meg DESC, meg DESC
/


Credits: https://ozmoroz.com/2013/08/how-to-get-size-of-all-tables-in-oracle.html#.Wpnb7IPwaot


******************************************************************************
select segment_name, round(size_mb/1024 ,2)"GB" from (SELECT segment_name, sum(size_mb) size_mb
FROM (
SELECT CASE WHEN x.segment_type in ('LOBSEGMENT','LOB PARTITION') THEN
  (SELECT table_name FROM dba_lobs y WHERE y.segment_name= x.segment_name)
  WHEN x.segment_type='LOBINDEX' THEN
  (SELECT table_name FROM dba_lobs y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in('INDEX','INDEX PARTITION','INDEX SUBPARTITION') THEN
  (SELECT y.table_name FROM dba_indexes y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in ('TABLE SUBPARTITION', 'TABLE PARTITION','TABLE')
  THEN x.segment_name END segment_name
,round(sum(x.bytes)/(1024*1024),2) size_mb
FROM dba_segments x
WHERE x.owner ='ICON_ADMIN_TM'
GROUP BY x.segment_name, x.segment_type
)
GROUP BY segment_name) where segment_name='ZZT_SERVICE_LOG';