--修改当前数据文件maxsize 2048k
SQL> alter database datafile '/u01/app/oracle/oradata/test/test01.dbf' autoextend on maxsize 2048k;
Database altered.
--新增数据文件，并且指定数据文件可自动扩展
SQL> alter tablespace test add datafile '/u01/app/oracle/oradata/test/test02.dbf' size 1024k autoextend on;
Tablespace altered.
--后续的插入数据在第一个数据文件被写满之后 开始写入第二个数据文件
--因此可以保证第一个数据文件 不会随意扩展
[oracle@Oracle11g test]$ ls -ltr |grep test
-rw-r----- 1 oracle oinstall 10272768 Feb 28 13:01 test01.dbf
-rw-r----- 1 oracle oinstall 1056768 Feb 28 13:02 test02.dbf
[oracle@Oracle11g test]$ ls -ltr |grep test
-rw-r----- 1 oracle oinstall 10272768 Feb 28 13:04 test01.dbf
-rw-r----- 1 oracle oinstall 105914368 Feb 28 13:04 test02.dbf
--查看数据文件的size
SQL> select name,bytes/1024/1024 from v$datafile;
NAME BYTES/1024/1024
-------------------------------------------------- ---------------
/u01/app/oracle/oradata/test/test01.dbf 9.7890625
/u01/app/oracle/oradata/test/test02.dbf 101
--再次查看数据文件的size
SQL> select name,bytes/1024/1024 from v$datafile;
NAME BYTES/1024/1024
-------------------------------------------------- ---------------
/u01/app/oracle/oradata/test/test01.dbf 9.7890625
/u01/app/oracle/oradata/test/test02.dbf 209.8125
alter tablespace system add datafile '/oradata02/fpyj/system03.dbf' size 1024m autoextend on;
alter tablespace sysaux add datafile '/oradata02/fpyj/sysaux02.dbf' size 1024m autoextend on;
alter tablespace undotbs1 add datafile '/oradata02/fpyj/undotbs02.dbf' size 1024m autoextend on;
alter tablespace users add datafile '/oradata02/fpyj/users02.dbf' size 1024m autoextend on;
alter tablespace example add datafile '/oradata02/fpyj/example02.dbf' size 1024m autoextend on;
SQL> select tbs.name,db_file.ts#,db_file.file#,db_file.name from v$datafile db_file,v$tablespace tbs where db_file.ts#=tbs.ts# order by tbs.name asc;
NAME TS# FILE# NAME
-------------------------------------------------- ---------- ---------- --------------------------------------------------
EXAMPLE 6 10 /oradata02/fpyj/example02.dbf
EXAMPLE 6 5 /oradata/fpyj/datafile/example01.dbf
SYSAUX 1 9 /oradata02/fpyj/sysaux02.dbf
SYSAUX 1 2 /oradata/fpyj/datafile/sysaux01.dbf
SYSTEM 0 1 /oradata/fpyj/datafile/system01.dbf
SYSTEM 0 11 /oradata02/fpyj/system03.dbf
SYSTEM 0 7 /oradata/fpyj/datafile/system02.dbf
SYSTEM 0 8 /oradata02/fpyj/system02.dbf
UNDOTBS1 2 3 /oradata/fpyj/datafile/undotbs01.dbf
USERS 4 4 /oradata/fpyj/datafile/users01.dbf
USERS 4 6 /oradata02/fpyj/users02.dbf
11 rows selected.