oracle 如何确定现有表空间的 MAXSIZE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2364024/
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 determine MAXSIZE of existing tablespace
提问by The Archetypal Paul
I need to determine the MAXSIZEthat was set for a tablespace when it was created (Oracle 10g)
我需要确定在MAXSIZE创建表空间时为其设置的 (Oracle 10g)
I'm sure I'm missing something obvious, but the information isn't immediately apparent in the information in DBA_TABLESPACES.
我确定我遗漏了一些明显的东西，但信息在DBA_TABLESPACES.
采纳答案by APC
In 11g this query would give you the answer, but I notice you're on 10g and alas the useful column is missing.
在 11g 中，这个查询会给你答案，但我注意到你在 10g 上，可惜缺少有用的列。
select tablespace_name, max_size
from dba_tablespaces
/
In 10g you will have to
在 10g 你将不得不
select tablespace_name
, initial_extent + (next_extent * (max_extents-1)) as calc_max_size
from dba_tablespaces
/
Remember that this is the defaultmaximum size. In practice you will be limited by the size of the datafiles assigned to the tablespace, which might be much less than this theoretical maximum.
请记住，这是默认的最大大小。实际上，您将受到分配给表空间的数据文件大小的限制，该大小可能远小于该理论最大值。
edit
编辑
@Paul 's comment is pertinent. I suppose the correct answer would be to say that the maximum size of a tablespace is a meaningless, indeed almost fictional, concept. The size of a tablespace is actually determined by its datafiles, and its potential maximum maximum size is determined by the maximum number of datafiles which can be assigned. The SQL Referencehas this to say on the topic:
@Paul 的评论是中肯的。我想正确的答案是表空间的最大大小是一个毫无意义的，实际上几乎是虚构的概念。一个表空间的大小实际上是由它的数据文件决定的，它潜在的最大大小是由可以分配的最大数据文件数决定的。该SQL参考中有这样一段对说的话题：
- A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion (232) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.
- A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million ([2 to the power of 22]) blocks.
- 大文件表空间仅包含一个数据文件或临时文件，最多可包含大约 40 亿 (232) 个块。单个数据文件或临时文件的最大大小对于具有 32K 块的表空间为 128 TB，对于具有 8K 块的表空间为 32TB。
- smallfile 表空间是传统的 Oracle 表空间，它可以包含 1022 个数据文件或临时文件，每个数据文件或临时文件最多可以包含大约 400 万（[2 的 22 次方]）块。
So perhaps this is a more useful query ...
所以也许这是一个更有用的查询......
select tablespace_name
, count(*) as no_of_data_files
, sum(maxblocks) as max_size
from dba_data_files
group by tablespace_name
/
...with the caveat that it only applies to the currently assigned datafiles.
...需要注意的是，它仅适用于当前分配的数据文件。
edit 2
编辑 2
MAXSIZE applies to the datafile not the tablespace. That is why the MAXSIZE keyword is discussed in the documentation for the filespec clauserather than under CREATE TABLESPACE.
MAXSIZE 适用于数据文件而不是表空间。这就是为什么在filespec 子句的文档中而不是在 CREATE TABLESPACE 下讨论 MAXSIZE 关键字的原因。
回答by Younes
It all depends on whether the data file is auto extensible or not.
这完全取决于数据文件是否可自动扩展。
So you get the the right information from DBA_DATA_FILES:
所以你从 DBA_DATA_FILES 得到正确的信息：
If AUTOEXTENSIBLE is set to YES then you need the total sum of MAXBYTES.
If AUTOEXTENSIBLE is set to NO then you need the total sum of BYTES.
如果 AUTOEXTENSIBLE 设置为 YES，则您需要 MAXBYTES 的总和。
如果 AUTOEXTENSIBLE 设置为 NO，那么您需要 BYTES 的总和。
MAX_SIZE in DBA_TABLESPACES has nothing to do with the maximum size of the tablespace itself. According to Oracle documenationit is the
DBA_TABLESPACES 中的 MAX_SIZE 与表空间本身的最大大小无关。根据Oracle 文档，它是
"Default maximum size of segments"
“段的默认最大大小”
So the right query is:
所以正确的查询是：
select TABLESPACE_NAME, sum(decode(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) MAX_SIZE
from DBA_DATA_FILES
group by TABLESPACE_NAME;
This has been tested on 11g but it should also work on 10g. It gives you the maximum size of each tablespace in bytes.
这已经在 11g 上测试过，但它也应该在 10g 上工作。它以字节为单位为您提供每个表空间的最大大小。
The same thing goes for TEMP tablespaces:
TEMP 表空间也是如此：
select TABLESPACE_NAME, sum(decode(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) MAX_SIZE
from DBA_TEMP_FILES
group by TABLESPACE_NAME;
回答by Gerrit
select tablespace_name, maxbytes/1024/1024 MAX_SIZE from dba_data_files;
从 dba_data_files 中选择 tablespace_name, maxbytes/1024/1024 MAX_SIZE;
回答by k.honsali
select tablespace_name, round(sum(bytes)/1024/1024, 2) as free_space from dba_free_space group by tablespace_name;
回答by Rob van Laarhoven
Maxsize is an attribute of dba_data_files
maxsize 是 dba_data_files 的一个属性
