表使用的 Oracle 磁盘空间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3731822/
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
Oracle disk space used by a table
提问by wsb3383
I have a table in an Oracle db that gets a couple of million new rows every month. Each row has a column which states the date when it was created.
我在 Oracle 数据库中有一个表，每个月都会有几百万个新行。每行都有一个列，说明它的创建日期。
I'd like to run a query that gets the disk space growth over the last 6 months. In other words, the result would be a table with two columns where each row would have the month's name and disk space used during that month.
我想运行一个查询来获取过去 6 个月的磁盘空间增长情况。换句话说，结果将是一个包含两列的表，其中每一行都有月份的名称和当月使用的磁盘空间。
Thanks,
谢谢，
回答by Wolph
This article reports a method of getting the table growth: http://www.dba-oracle.com/t_table_growth_reports.htm
本文报告了一种获取表增长的方法：http: //www.dba-oracle.com/t_table_growth_reports.htm
column "Percent of Total Disk Usage" justify right format 999.99
column "Space Used (MB)" justify right format 9,999,999.99
column "Total Object Size (MB)" justify right format 9,999,999.99
set linesize 150
set pages 80
set feedback off
select * from (select to_char(end_interval_time, 'MM/DD/YY') mydate, sum(space_used_delta) / 1024 / 1024 "Space used (MB)", avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.segment_name = '&segment_name'
group by to_char(end_interval_time, 'MM/YY'))
order by to_date(mydate, 'MM/YY');
回答by Gary Myers
DBA_TABLES (or the equivalent) gives an AVG_ROW_LEN, so you could simply multiply that by the number of rows created per month.
DBA_TABLES（或等效的）给出了一个 AVG_ROW_LEN，所以你可以简单地将它乘以每月创建的行数。
The caveats to that are, it assumes that the row length of new rows is similar to that of existing rows. If you've got a bunch of historical data that were 'small' (eg 50 bytes) but new rows are larger (150 bytes), then the estimates will be too low.
需要注意的是，它假定新行的行长与现有行的行长相似。如果您有一堆“小”的历史数据（例如 50 字节）但新行更大（150 字节），那么估计值将太低。
Also, how do updates figure into things ? If a row starts at 50 bytes and grows to 150 two months later, how do you account for those 100 bytes ?
另外，更新如何体现在事物中？如果一行从 50 个字节开始，两个月后增长到 150 个，您如何计算这 100 个字节？
Finally, tables don't grow for each row insert. Every so often the allocated space will fill up and it will go and allocate another chunk. Depending on the table settings, that next chunk may be, for example, 50% of the existing table size. So you might not physically grow for three months and then have a massive jump, then not grow for another six months.
最后，表不会因每行插入而增长。每隔一段时间，分配的空间就会填满，它会去分配另一个块。根据表设置，下一个块可能是现有表大小的 50%。因此，您可能在三个月内身体没有增长，然后有一个巨大的跳跃，然后再有六个月没有增长。
