oracle 表空间数据文件最大值的计算测试

环境：centos7.6 +oracle 11.2.0.4

数据安装是默认安装的

创建表空间语句参考官方sql文挡

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7003.htm#SQLRF01403

查询数据库块大小

SQL> select name , value from v$parameter where name like '%block%'
  2  /
 
NAME                                     VALUE
---------------------------------------- ------------------------------
db_block_buffers                         0
db_block_checksum                        TYPICAL
db_block_size                            8192
db_file_multiblock_read_count            128
db_block_checking                        FALSE
 
SQL> show parameter block  
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TYPICAL
db_block_size                        integer     8192
db_file_multiblock_read_count        integer     128
SQL> 

查询表空间块大小,类型和状态

SQL> select block_size ,tablespace_name ,bigfile,status from dba_tablespaces;
 
BLOCK_SIZE TABLESPACE_NAME                BIGFILE         STATUS
---------- ------------------------------ --------------- ---------
      8192 SYSTEM                         NO              ONLINE
      8192 SYSAUX                         NO              ONLINE
      8192 UNDOTBS1                       NO              ONLINE
      8192 TEMP                           NO              ONLINE
      8192 USERS                          NO              ONLINE
      8192 T_DATA                         NO              ONLINE
 
6 rows selected.
 
SQL> 

分别创建默认表空间和大文件表空间

SQL> select file_name,file_id,tablespace_name from dba_data_files;
 
FILE_NAME                                                       FILE_ID TABLESPACE_NAME
------------------------------------------------------------ ---------- ------------------------------
/u01/app/oradata/racdg/users01.dbf                                    4 USERS
/u01/app/oradata/racdg/undotbs01.dbf                                  3 UNDOTBS1
/u01/app/oradata/racdg/sysaux01.dbf                                   2 SYSAUX
/u01/app/oradata/racdg/system01.dbf                                   1 SYSTEM
/u01/app/oradata/racdg/t_data.dbf                                     5 T_DATA
/u01/app/oradata/racdg/t_data02.dbf                                   6 T_DATA
/u01/app/oradata/racdg/t_data03.dbf                                   7 T_DATA
/u01/app/oradata/racdg/t_data04.dbf                                   8 T_DATA
/u01/app/oradata/racdg/t_data05.dbf                                   9 T_DATA
 
9 rows selected.
 
SQL>create bigfile  tablespace t_big01 datafile '/u01/app/oradata/racdg/t_big01.dbf' size 10m autoextend on next 10m maxsize unlimited
  2  /
 
Tablespace created.
 
SQL> create smallfile tablespace t_small01 datafile '/u01/app/oradata/racdg/tsmall01.dbf' size 10m autoextend on next 10m maxsize unlimited 
  2  /
 
Tablespace created.
 
SQL>
SQL> select file_name,file_id,bytes/1024/1024 as "bytes MB" ,status,maxblocks,maxbytes/1024/1024/1024 as "maxsize GB" from dba_data_files
  2  /
 
FILE_NAME                                   FILE_ID   bytes MB STATUS     MAXBLOCKS maxsize GB
---------------------------------------- ---------- ---------- --------- ---------- ----------
/u01/app/oradata/racdg/users01.dbf                4         60 AVAILABLE    4194302 31.9999847
/u01/app/oradata/racdg/undotbs01.dbf              3         75 AVAILABLE    4194302 31.9999847
/u01/app/oradata/racdg/sysaux01.dbf               2        540 AVAILABLE    4194302 31.9999847
/u01/app/oradata/racdg/system01.dbf               1        750 AVAILABLE    4194302 31.9999847
/u01/app/oradata/racdg/t_data.dbf                 5        500 AVAILABLE    4194176 31.9990234
/u01/app/oradata/racdg/t_data02.dbf               6        500 AVAILABLE          0          0
/u01/app/oradata/racdg/t_data03.dbf               7        500 AVAILABLE          0          0
/u01/app/oradata/racdg/t_data04.dbf               8        500 AVAILABLE          0          0
/u01/app/oradata/racdg/t_data05.dbf               9         10 AVAILABLE    4194176 31.9990234
/u01/app/oradata/racdg/t_big01.dbf               10         10 AVAILABLE 4294967293      32768
/u01/app/oradata/racdg/tsmall01.dbf              11         10 AVAILABLE    4194302 31.9999847
 
11 rows selected.
 
SQL> 
SQL> select tablespace_name,blocksize ,status,bigfile from dba_tablespaces;
select tablespace_name,blocksize ,status,bigfile from dba_tablespaces
                       *
ERROR at line 1:
ORA-00904: "BLOCKSIZE": invalid identifier
 
 
SQL> c/blocksize/block_size
  1* select tablespace_name,block_size ,status,bigfile from dba_tablespaces
SQL> /
 
TABLESPACE_NAME                BLOCK_SIZE STATUS    BIGFILE
------------------------------ ---------- --------- ---------------
SYSTEM                               8192 ONLINE    NO
SYSAUX                               8192 ONLINE    NO
UNDOTBS1                             8192 ONLINE    NO
TEMP                                 8192 ONLINE    NO
USERS                                8192 ONLINE    NO
T_DATA                               8192 ONLINE    NO
T_BIG01                              8192 ONLINE    YES
T_SMALL01                            8192 ONLINE    NO
 
8 rows selected.
 
SQL> 

表空间数据文件最大可扩展计算，数据文件最大块数量 * 数据库块size 为数据文件最大值（字节）。

大文件表空间数据文件计算如下列出

SQL> select file_name,file_id,bytes/1024/1024 as "bytes MB" ,status,maxblocks,maxbytes/1024/1024/1024 as "maxsize GB" from dba_data_files
  2  /
 
FILE_NAME                                   FILE_ID   bytes MB STATUS     MAXBLOCKS maxsize GB
---------------------------------------- ---------- ---------- --------- ---------- ----------
/u01/app/oradata/racdg/users01.dbf                4         60 AVAILABLE    4194302 31.9999847
/u01/app/oradata/racdg/undotbs01.dbf              3         75 AVAILABLE    4194302 31.9999847
/u01/app/oradata/racdg/sysaux01.dbf               2        540 AVAILABLE    4194302 31.9999847
/u01/app/oradata/racdg/system01.dbf               1        750 AVAILABLE    4194302 31.9999847
/u01/app/oradata/racdg/t_data.dbf                 5        500 AVAILABLE    4194176 31.9990234
/u01/app/oradata/racdg/t_data02.dbf               6        500 AVAILABLE          0          0
/u01/app/oradata/racdg/t_data03.dbf               7        500 AVAILABLE          0          0
/u01/app/oradata/racdg/t_data04.dbf               8        500 AVAILABLE          0          0
/u01/app/oradata/racdg/t_data05.dbf               9         10 AVAILABLE    4194176 31.9990234
/u01/app/oradata/racdg/t_big01.dbf               10         10 AVAILABLE 4294967293      32768
/u01/app/oradata/racdg/tsmall01.dbf              11         10 AVAILABLE    4194302 31.9999847
 
11 rows selected.
 
 
SQL>  select 4294967293*8192/1024/1024/1024 as "bigfile maxsize (GB)" from dual;
 
bigfile maxsize (GB)
--------------------
               32768
 
SQL> 

小文件表空间数据文件计算如下列出：

SQL> select file_name,file_id,bytes/1024/1024 as "bytes MB" ,status,maxblocks,maxbytes/1024/1024/1024 as "maxsize GB" from dba_data_files
  2  /
 
FILE_NAME                                   FILE_ID   bytes MB STATUS     MAXBLOCKS maxsize GB
---------------------------------------- ---------- ---------- --------- ---------- ----------
/u01/app/oradata/racdg/users01.dbf                4         60 AVAILABLE    4194302 31.9999847
/u01/app/oradata/racdg/undotbs01.dbf              3         75 AVAILABLE    4194302 31.9999847
/u01/app/oradata/racdg/sysaux01.dbf               2        540 AVAILABLE    4194302 31.9999847
/u01/app/oradata/racdg/system01.dbf               1        750 AVAILABLE    4194302 31.9999847
/u01/app/oradata/racdg/t_data.dbf                 5        500 AVAILABLE    4194176 31.9990234
/u01/app/oradata/racdg/t_data02.dbf               6        500 AVAILABLE          0          0
/u01/app/oradata/racdg/t_data03.dbf               7        500 AVAILABLE          0          0
/u01/app/oradata/racdg/t_data04.dbf               8        500 AVAILABLE          0          0
/u01/app/oradata/racdg/t_data05.dbf               9         10 AVAILABLE    4194176 31.9990234
/u01/app/oradata/racdg/t_big01.dbf               10         10 AVAILABLE 4294967293      32768
/u01/app/oradata/racdg/tsmall01.dbf              11         10 AVAILABLE    4194302 31.9999847
 
11 rows selected.
 
SQL> select 4194302*8192/1024/1024/1024 as "smallfile maxsize (GB)" from dual;
 
smallfile maxsize (GB)
----------------------
            31.9999847
 
SQL> 

测试单个表空间最大可创建多少个数据文件,plsql 如下：

declare
str varchar2(200);
begin
for i in 1..1000 loop
--DBMS_OUTPUT.PUT_LINE();
str:='alter tablespace T_SMALL01 add datafile '||''''||'/u01/app/oradata/racdg/T_SMALL0'||i||'.dbf'||''''||' size 1m autoextend on maxsize unlimited';
DBMS_OUTPUT.PUT_LINE(str);
EXECUTE IMMEDIATE str;
end loop;
end ;

操作执行如下，脚本for 循环值增加到1000，向表空间T_SMALL01添加数据文件执行如下

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

SQL> set serveroutput on 
SQL>declare
  2  str varchar2(200);
  3  begin
  4  for i in 1..1000 loop
  5  --DBMS_OUTPUT.PUT_LINE();
  6  str:='alter tablespace T_SMALL01 add datafile '||''''||'/u01/app/oradata/racdg/T_SMALL0'||i||'.dbf'||''''||' size 1m autoextend on maxsize unlimited';
  7  DBMS_OUTPUT.PUT_LINE(str);
  8  EXECUTE IMMEDIATE str;
  9  end loop;
 10  end ;
 11  /
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL01.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL02.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL03.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL04.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL05.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL06.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL07.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL08.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL09.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL010.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL011.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL012.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL013.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL014.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL015.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL016.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL017.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL018.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL019.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL020.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL021.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL022.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL023.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL024.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL025.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL026.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL027.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL028.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL029.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL030.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL031.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL032.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL033.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL034.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL035.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL036.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL037.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL038.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL039.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL040.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL041.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL042.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL043.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL044.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL045.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL046.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL047.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL048.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL049.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL050.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL051.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL052.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL053.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL054.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL055.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL056.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL057.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL058.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL059.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL060.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL061.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL062.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL063.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL064.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL065.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL066.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL067.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL068.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL069.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL070.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL071.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL072.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL073.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL074.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL075.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL076.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL077.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL078.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL079.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL080.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL081.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL082.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL083.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL084.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL085.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL086.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL087.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL088.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL089.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL090.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL091.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL092.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL093.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL094.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL095.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL096.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL097.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL098.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL099.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0100.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0101.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0102.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0103.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0104.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0105.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0106.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0107.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0108.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0109.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0110.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0111.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0112.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0113.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0114.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0115.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0116.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0117.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0118.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0119.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0120.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0121.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0122.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0123.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0124.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0125.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0126.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0127.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0128.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0129.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0130.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0131.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0132.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0133.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0134.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0135.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0136.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0137.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0138.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0139.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0140.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0141.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0142.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0143.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0144.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0145.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0146.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0147.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0148.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0149.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0150.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0151.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0152.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0153.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0154.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0155.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0156.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0157.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0158.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0159.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0160.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0161.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0162.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0163.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0164.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0165.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0166.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0167.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0168.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0169.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0170.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0171.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0172.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0173.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0174.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0175.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0176.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0177.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0178.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0179.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0180.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0181.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0182.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0183.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0184.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0185.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0186.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0187.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0188.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0189.dbf' size 1m autoextend on maxsize unlimited
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0190.dbf' size 1m autoextend on maxsize unlimited
declare
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
ORA-06512: at line 8
 
 
SQL> ho  ls -l  /u01/app/oradata/racdg/T_SMALL*  | wc  -l
189
 
SQL> show parameter db_files
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200
SQL> ho ls -l /u01/app/oradata/racdg/tsmall01.dbf 
-rw-r----- 1 oracle oinstall 10493952 Jul  8 01:07 /u01/app/oradata/racdg/tsmall01.dbf
 
SQL> select count(*) from dba_data_files where tablespace_name ='T_SMALL01'
  2  /
 
  COUNT(*)
----------
       190
 
SQL> alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0190.dbf' size 1m autoextend on maxsize unlimited
  2  /
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0190.dbf' size 1m autoextend on maxsize unlimited
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
 
 
SQL> 
SQL> select count(*) from dba_data_files;
 
COUNT(*)
----------
200
 
SQL>

经测试，db_files 参数为200,所有表空间数据文件当达到总计为200，之后创建就会报错

解决办法（仅测试库测试用，生产数据库谨慎使用），扩展此参数需要重启数据库

[oracle@oraback ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 8 02:59:13 2022
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0190.dbf' size 1m autoextend on maxsize unlimited
  2  /
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0190.dbf' size 1m autoextend on maxsize unlimited
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
 
 
SQL> alter system set db_files=400 scope=spfile
  2  /
 
System altered.
 
SQL> alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0190.dbf' size 1m autoextend on maxsize unlimited
  2  /
alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0190.dbf' size 1m autoextend on maxsize unlimited
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
 
 
SQL> startup force
ORACLE instance started.
 
Total System Global Area 1586708480 bytes
Fixed Size           2253624 bytes
Variable Size      973081800 bytes
Database Buffers   603979776 bytes
Redo Buffers         7393280 bytes
Database mounted.
Database opened.
SQL> create pfile from spfile;
 
File created.
 
SQL> show parameter db_files
 
NAME                         TYPE      VALUE
------------------------------------ ----------- ------------------------------
db_files                     integer   400
SQL> alter tablespace T_SMALL01 add datafile '/u01/app/oradata/racdg/T_SMALL0190.dbf' size 1m autoextend on maxsize unlimited
  2  /
 
Tablespace altered.
 
SQL> 