在 Oracle 中,要查询表的大小、索引的大小、分区的大小、大字段(LOBs)的大小以及单个表占用的空间总大小,您可以使用以下 SQL 查询。请注意,这些查询需要您具有充足的权限来访问 Oracle 的数据字典视图。
1. 表的大小
查询表的大小可以使用 DBA_SEGMENTS 视图:
- SELECT
- segment_name AS "Table_Name",
- SUM(bytes) / (1024 * 1024) AS "Size_MB"
- FROM
- dba_segments
- WHERE
- segment_type = 'TABLE'
- GROUP BY
- segment_name;
复制代码 2. 索引的大小
查询索引的大小可以使用 DBA_INDEXES 和 DBA_SEGMENTS 视图:
- SELECT
- index_name AS "Index_Name",
- SUM(bytes) / (1024 * 1024) AS "Size_MB"
- FROM
- dba_indexes i,
- dba_segments s
- WHERE
- i.index_name = s.segment_name AND
- s.segment_type = 'INDEX'
- GROUP BY
- index_name;
复制代码 3. 分区的大小
假如表是分区的,可以查询分区的大小:
- SELECT
- table_name,
- partition_name,
- SUM(bytes) / (1024 * 1024) AS "Size_MB"
- FROM
- dba_tab_partitions
- GROUP BY
- table_name, partition_name;
复制代码 4. 大字段(LOBs)的大小
查询 LOB 字段的大小可以使用 DBA_LOBS 视图:
- SELECT
- table_name,
- column_name,
- SUM(bytes) / (1024 * 1024) AS "Size_MB"
- FROM
- dba_lobs
- GROUP BY
- table_name, column_name;
复制代码 5. 单表占用空间的总大小
为了得到单个表及其索引和分区的总大小,您可以联合上述查询的效果:
- WITH Table_Size AS (
- SELECT
- segment_name AS "Table_Name",
- SUM(bytes) / (1024 * 1024) AS "Table_Size_MB"
- FROM
- dba_segments
- WHERE
- segment_type = 'TABLE'
- GROUP BY
- segment_name
- ),
- Index_Size AS (
- SELECT
- i.index_name AS "Index_Name",
- SUM(s.bytes) / (1024 * 1024) AS "Index_Size_MB"
- FROM
- dba_indexes i,
- dba_segments s
- WHERE
- i.index_name = s.segment_name AND
- s.segment_type = 'INDEX'
- GROUP BY
- i.index_name
- ),
- Partition_Size AS (
- SELECT
- table_name,
- SUM(bytes) / (1024 * 1024) AS "Partition_Size_MB"
- FROM
- dba_tab_partitions
- GROUP BY
- table_name
- )
- SELECT
- t."Table_Name",
- COALESCE(t."Table_Size_MB", 0) AS "Table_Size_MB",
- COALESCE(i."Index_Size_MB", 0) AS "Index_Size_MB",
- COALESCE(p."Partition_Size_MB", 0) AS "Partition_Size_MB",
- (COALESCE(t."Table_Size_MB", 0) + COALESCE(i."Index_Size_MB", 0) + COALESCE(p."Partition_Size_MB", 0)) AS "Total_Size_MB"
- FROM
- Table_Size t
- LEFT JOIN
- Index_Size i ON t."Table_Name" = REGEXP_SUBSTR(i."Index_Name", '^.*_IDX$')
- LEFT JOIN
- Partition_Size p ON t."Table_Name" = p.table_name;
复制代码 请注意,上述查询假设索引名称遵照某种定名模式,比方以 _IDX 末端。假如您的索引定名规则不同,请根据现实情况调整 REGEXP_SUBSTR 函数中的正则表达式。
以上查询可以根据您的需求进行调整。假如您需要进一步的帮助
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。 |