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添加数据文件 执行如下
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>
浙公网安备 33010602011771号