oracle 表空间可以自动添加小文件数据文件吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5167134/
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
Can oracle tablespaces automatically add smallfile datafiles?
提问by Adam Butler
Using Oracle managed files I can create my tablespace like this:
使用 Oracle 管理的文件，我可以像这样创建我的表空间：
CREATE TABLESPACE users;
It will autoextend but as I understand it smallfile datafiles have a maximum size. So I then need to do:
它会自动扩展，但据我所知，小文件数据文件有最大大小。所以我需要这样做：
ALTER TABLESPACE users ADD DATAFILE;
ALTER TABLESPACE users ADD DATAFILE;
ALTER TABLESPACE users ADD DATAFILE;
Just wondering if there was a way to get oracle to automatically add another datafile when it tries to autoextend past its largest possible size?
只是想知道是否有办法让 oracle 在尝试自动扩展超过其最大可能大小时自动添加另一个数据文件？
回答by baklarz2048
There is no real limit in Oracle
Oracle 中没有真正的限制
- http://forums.oracle.com/forums/thread.jspa?messageID=1898237�
- http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch43.htm#287916
- http://forums.oracle.com/forums/thread.jspa?messageID=1898237
- http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch43.htm#287916
ALTER DATABASE DATAFILE <'data_file_name' | data_file_number> AUTOEXTEND K|M|G|T|P|E MAXSIZE <UNLIMITED| K|M|G|T|P|E>;
ALTER DATABASE DATAFILE <'data_file_name' | data_file_number> AUTOEXTEND K|M|G|T|P|E MAXSIZE <无限制| K|M|G|T|P|E>;
OS or os operator can stop You and database. Than You can write job that wiil be automaticly add new files to tablespace after % of usage. Something like this (pseudo code).
OS 或 os 操作员可以停止您和数据库。比您可以编写在使用率达到 % 后自动将新文件添加到表空间的作业。像这样的东西（伪代码）。
SELECT tablespace_name,maxbytes,bytes INTO v_tbs_info FROM dba_data_files;
FOR tbs_inf IN v_tbs_info LOOP
IF tbs_inf.bytes/tbs_inf.maxbytes > 0.99
excecute immediate 'ALTER TABLESPACE '|| tbs_inf.tablespcae_name ||' ADD DATAFILE SIZE 1M aUTOEXTEND ON NEXT 10M MAXSIZE 10G';
END LOOP;
Of course You shouldn't write this job and use it. You should provide the amount of space occupied by the data and allocate the appropriate number of files to the tablespace.
当然你不应该写这个作业并使用它。您应该提供数据占用的空间量，并为表空间分配适当数量的文件。
回答by Jonathan Lynch
There is no built-in method for oracle to auto-add datafiles.
oracle 没有内置的方法来自动添加数据文件。
BEWARE: The "unlimited" operator in oracle is not actually unlimited. It only means unlimited up to oracle's hard cap on datafile sizes, which depends on your db_block_sizeparameter. A default database is going to have an 8k block size and use smallfile tablespaces, which means the datafiles won't grow past 32GB.
当心：oracle 中的“unlimited”运算符实际上并不是unlimited。它仅意味着无限制地达到 oracle 对数据文件大小的硬上限，这取决于您的db_block_size参数。默认数据库将具有 8k 块大小并使用小文件表空间，这意味着数据文件不会增长超过 32GB。
Once the datafile reaches the hard limit, even when you have AUTOEXTEND MAXSIZE UNLIMITEDset, you will still have to add a new datafile by hand.
一旦数据文件达到硬限制，即使您已经AUTOEXTEND MAXSIZE UNLIMITED设置，您仍然需要手动添加新的数据文件。
For more information see the table in this post:
有关更多信息，请参阅此帖子中的表格：
回答by ik_zelf
sure we can but why should we? Normally there is a reason for a limit. And for the max sizes of the datafiles, we can create a bigfile tablespace that has stretched the filesize limits a bit. This has impact on backup/recovery but since 11gR2 we can have those files backedup in parallel, where the bigfiles are chopped in chunks and distributed over the various channels.
我们当然可以，但我们为什么要这样做？通常有一个限制的原因。对于数据文件的最大大小，我们可以创建一个大文件表空间，它稍微扩展了文件大小限制。这对备份/恢复有影响，但从 11gR2 开始，我们可以并行备份这些文件，其中大文件被切成块并分布在各个通道上。
Also, a question what is the largest possible size? Is it the size that is supported by the filesystem, by ASM, by the database or is it the maxsize that is set by the dba?
另外，一个问题是最大可能的尺寸是多少？它是由文件系统、ASM、数据库支持的大小还是由 dba 设置的最大大小？
Ronald.
罗纳德。
