when-to-use-rebuild-vs-coalesce-vs-shrink

  • Find out the table size
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name
    FROM DBA_SEGMENTS
    WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',
    'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY', 'LOBINDEX', 'LOBSEGMENT', 'LOB PARTITION')
    --AND TABLESPACE_NAME LIKE 'COSTE%'
    --AND SEGMENT_NAME LIKE 'P2010201%'
    --AND partition_name LIKE 'P20100201%'
    --AND segment_type = 'TABLE'
    --AND OWNER = 'TARGET_POC'
    --AND ROUND(bytes/(1024*1024),2) > 1000
    ORDER BY bytes DESC;

You can group by tablespace, owner and segment type and see the total space occupied in MBytes

1
2
3
4
5
6
7
SELECT tablespace_name, owner, segment_type "Object Type",
   COUNT(owner) "Number of Objects",
   ROUND(SUM(bytes) / 1024 / 1024, 2) "Total Size in MB"
FROM   sys.dba_segments
WHERE  tablespace_name IN ('MPIS')
GROUP BY tablespace_name, owner, segment_type
ORDER BY tablespace_name, owner, segment_type;

  • find out the usage of tablespace

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    select df.tablespace_name "Tablespace",
    totalusedspace "Used MB",
    (df.totalspace - tu.totalusedspace) "Free MB",
    df.totalspace "Total MB",
    round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
    "Pct. Free"
    from
    (select tablespace_name,
    round(sum(bytes) / 1048576) TotalSpace
    from dba_data_files
    group by tablespace_name) df,
    (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
    from dba_segments
    group by tablespace_name) tu
    where df.tablespace_name = tu.tablespace_name order by "Pct. Free";

  • Shrink table

    1
    2
    alter table STATS$SQL_SUMMARY enable row movement;
    alter table STATS$SQL_SUMMARY shrink space;

  • Shrink index

    1
    2
    3
    4
    5
    alter index STATS$SQL_SUMMARY_PK shrink space compact
    alter index STATS$SQL_SUMMARY_PK shrink space;
    alter index STATS$SYSSTAT_PK coalesce
    alter index STATS$SQL_SUMMARY_PK rebuild
    shrink space compact = coalesce

  • Reference
    when-to-use-rebuild-vs-coalesce-vs-shrink-space
    深入理解重建索引
    Alter index coalesce VS shrink space
    alter index coalesce和alter index rebuild的区别