指定最大大小，促使文件不会自动扩展

--修改当前数据文件maxsize 2048kSQL>  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--查看数据文件的sizeSQL> 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--再次查看数据文件的sizeSQL> 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.8125alter 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.dbfEXAMPLE                             6           5 /oradata/fpyj/datafile/example01.dbfSYSAUX                                1           9 /oradata02/fpyj/sysaux02.dbfSYSAUX                                1           2 /oradata/fpyj/datafile/sysaux01.dbfSYSTEM                                0           1 /oradata/fpyj/datafile/system01.dbfSYSTEM                                0          11 /oradata02/fpyj/system03.dbfSYSTEM                                0           7 /oradata/fpyj/datafile/system02.dbfSYSTEM                                0           8 /oradata02/fpyj/system02.dbfUNDOTBS1                            2           3 /oradata/fpyj/datafile/undotbs01.dbfUSERS                                4           4 /oradata/fpyj/datafile/users01.dbfUSERS                                4           6 /oradata02/fpyj/users02.dbf11 rows selected.

转载于:https://www.cnblogs.com/iyoume2008/p/8490492.html