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

本文主要是介绍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 为数据文件最大值（字节）

t_big01

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>

t_small01

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添加数据文件执行如下

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 表空间数据文件最大值的计算测试的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

C/C++教程

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

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯