环境：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，之后创建就会报错
原创文章，作者：6024010，如若转载，请注明出处：https://blog.ytso.com/tech/database/272367.html