如何使用 SQL 查询显示 Oracle 模式大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9671219/
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 display Oracle schema size with SQL query?
提问by Peter Penzov
I have a Oracle schema with 70+ tables. I want to create simple page which can display the HDD space occupied by the tables. How I can get this value with SQL query?
我有一个包含 70 多个表的 Oracle 模式。我想创建一个简单的页面，可以显示表占用的硬盘空间。如何使用 SQL 查询获取此值？
P.S And how I can get the Oracle architecture version?
PS 以及如何获得 Oracle 架构版本？
回答by Justin Cave
You probably want
你可能想要
SELECT sum(bytes)
FROM dba_segments
WHERE owner = <<owner of schema>>
If you are logged in as the schema owner, you can also
如果您以架构所有者身份登录，您还可以
SELECT SUM(bytes)
FROM user_segments
That will give you the space allocated to the objects owned by the user in whatever tablespaces they are in. There may be empty space allocated to the tables that is counted as allocated by these queries.
这将为您提供分配给用户拥有的对象在它们所在的任何表空间中的空间。可能有分配给表的空白空间被这些查询计为已分配。
回答by biology.info
If you just want to calculate the schema size without tablespace free space and indexes :
如果您只想计算没有表空间可用空间和索引的架构大小：
select
sum(bytes)/1024/1024 as size_in_mega,
segment_type
from
dba_segments
where
owner='<schema's owner>'
group by
segment_type;
For all schemas
对于所有模式
select
sum(bytes)/1024/1024 as size_in_mega, owner
from
dba_segments
group by
owner;
回答by Rostislav Matl
select T.TABLE_NAME, T.TABLESPACE_NAME, t.avg_row_len*t.num_rows from dba_tables t
order by T.TABLE_NAME asc
See e.g. http://www.dba-oracle.com/t_script_oracle_table_size.htmfor more options
有关更多选项，请参见例如http://www.dba-oracle.com/t_script_oracle_table_size.htm
回答by Bhaskar
SELECT table_name as Table_Name, row_cnt as Row_Count, SUM(mb) as Size_MB
FROM
(SELECT in_tbl.table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from ' ||ut.table_name)),'/ROWSET/ROW/C')) AS row_cnt , mb
FROM
(SELECT CASE WHEN lob_tables IS NULL THEN table_name WHEN lob_tables IS NOT NULL THEN lob_tables END AS table_name , mb
FROM (SELECT ul.table_name AS lob_tables, us.segment_name AS table_name , us.bytes/1024/1024 MB FROM user_segments us
LEFT JOIN user_lobs ul ON us.segment_name = ul.segment_name ) ) in_tbl INNER JOIN user_tables ut ON in_tbl.table_name = ut.table_name ) GROUP BY table_name, row_cnt ORDER BY 3 DESC;``
Above query will give, Table_name, Row_count, Size_in_MB(includes lob column size) of specific user.
以上查询将给出特定用户的 Table_name、Row_count、Size_in_MB（包括 lob 列大小）。
回答by Dilip Paudel
SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB
FROM DBA_SEGMENTS DS
WHERE SEGMENT_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES) AND SEGMENT_NAME='YOUR_TABLE_NAME'
GROUP BY DS.TABLESPACE_NAME, SEGMENT_NAME;
