oracle 如何在Oracle的每个表空间中找到最大的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42830329/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find biggest table in each tablespace in Oracle
提问by user3661564
I create following SQL statement but it show me biggest tables in general. I want to see it for each tablespace.
我创建了以下 SQL 语句，但它通常显示了最大的表。我想为每个表空间查看它。
select tablespace_name, max(BYTES/1024/1024), SEGMENT_NAME
from dba_segments
where SEGMENT_TYPE='TABLE'
group by tablespace_name,SEGMENT_NAME,tablespace_name
Any ideas?
有任何想法吗？
回答by Wernfried Domscheit
You can make it shorter when you use FIRSTfunction:
使用FIRST函数时可以缩短它：
SELECT tablespace_name, MAX(BYTES/1024/1024),
MAX(SEGMENT_NAME) KEEP (DENSE_RANK LAST ORDER BY BYTES)
FROM dba_segments
WHERE SEGMENT_TYPE = 'TABLE'
GROUP BY tablespace_name;
Note, due to SEGMENT_TYPE = 'TABLE'your query will not include partitionized tables.
请注意，由于SEGMENT_TYPE = 'TABLE'您的查询将不包括分区表。
Another note, you will get only one table per tablespace in case several tables have exactly the same size. Use solution from APC in cause you need all tables.
另请注意，如果多个表的大小完全相同，每个表空间只能获得一个表。使用 APC 的解决方案，因为您需要所有表。
回答by Ram Limbu
Use Analytical function to rank table in order of size:
使用 Analytical 函数按大小顺序对表进行排名：
SELECT
tablespace_name,segment_name,tab_size_mb
FROM
(
SELECT
tablespace_name,segment_name,bytes/1024/1024 tab_size_mb,
RANK() OVER (PARTITION BY tablespace_name ORDER BY bytes DESC) AS rnk
FROM dba_segments
WHERE segment_type='TABLE'
)
WHERE rnk=1;
