when-to-use-rebuild-vs-coalesce-vs-shrink-优快云博客

本文详细介绍如何使用SQL查询来管理Oracle数据库的空间，包括查询表大小、表空间使用情况及收缩表和索引的方法。通过具体实例展示了如何有效地进行数据库维护。

Find out the table size

<pre> 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; </pre>

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

<pre> 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; </pre>

find out the usage of tablespace

<pre> 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 ; </pre>

Shrink table
alter table STATS$SQL_SUMMARY enable row movement;
alter table STATS$SQL_SUMMARY shrink space;
Shrink index
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