管理表空间（八）--调整表空间的尺寸

 

调整表空间的尺寸有三种方式，如下所示

（1）自动 autoextend 扩展

（2）resize datafile 大小

（3）增加表空间数据文件

1、允许表空间自动扩展

09:33:17 SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE  from dba_data_files;

FILE_ID FILE_NAME                                          TABLESPACE_NAME                AUT

---------- -------------------------------------------------- ------------------------------ ---

1 /u01/app/oracle/oradata/lx02/system01.dbf          SYSTEM                         NO

9 /u01/app/oracle/oradata/lx02/cuug01.dbf            CUUG                           NO

11 /u01/app/oracle/oradata/lx02/lx02.dbf              LX02                           NO

7 /u01/app/oracle/oradata/lx02/OLTP01.DBF            OLTP                           NO

6 /u01/app/oracle/oradata/lx02/indx01.dbf            INDX                           NO

5 /u01/app/oracle/oradata/lx02/example01.dbf         EXAMPLE                        YES

4 /u01/app/oracle/oradata/lx02/user01.dbf            USERS                          NO

3 /u01/app/oracle/oradata/lx02/sysaux01.dbf          SYSAUX                         NO

2 /u01/app/oracle/oradata/lx02/rtbs01.dbf            RTBS                           NO

8 /u01/app/oracle/oradata/lx02/test01.dbf            TEST                           NO

10 /u01/app/oracle/oradata/lx02/lx01.dbf              LX01                           NO

12 /u01/app/oracle/oradata/lx02/undotbs01.dbf         UNDO_TBS01                     NO

12 rows selected.

----------自动扩展

09:33:20 SQL> alter database datafile '/u01/app/oracle/oradata/lx02/test01.dbf'

09:34:47   2   autoextend on next 10m maxsize 500m;

Database altered.

2、手工改变数据文件尺寸

09:34:57 SQL> alter database datafile '/u01/app/oracle/oradata/lx02/test01.dbf' resize 50m;

Database altered.

3、增加数据文件

09:35:35 SQL> alter tablespace test add

09:35:45   2   datafile '/u01/app/oracle/oradata/lx02/test02.dbf' size 10m;

Tablespace altered.

09:36:03 SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;

FILE_ID FILE_NAME                                          TABLESPACE_NAME                AUT       size

---------- -------------------------------------------------- ------------------------------ --- ----------

1 /u01/app/oracle/oradata/lx02/system01.dbf          SYSTEM                         NO         300

9 /u01/app/oracle/oradata/lx02/cuug01.dbf            CUUG                           NO          10

11 /u01/app/oracle/oradata/lx02/lx02.dbf              LX02                           NO          10

7 /u01/app/oracle/oradata/lx02/OLTP01.DBF            OLTP                           NO          48

6 /u01/app/oracle/oradata/lx02/indx01.dbf            INDX                           NO          40

5 /u01/app/oracle/oradata/lx02/example01.dbf         EXAMPLE                        YES        400

4 /u01/app/oracle/oradata/lx02/user01.dbf            USERS                          NO          48

3 /u01/app/oracle/oradata/lx02/sysaux01.dbf          SYSAUX                         NO         100

2 /u01/app/oracle/oradata/lx02/rtbs01.dbf            RTBS                           NO         100

8 /u01/app/oracle/oradata/lx02/test01.dbf            TEST                           YES         50

10 /u01/app/oracle/oradata/lx02/lx01.dbf              LX01                           NO          10

12 /u01/app/oracle/oradata/lx02/undotbs01.dbf         UNDO_TBS01                     NO          10

13 /u01/app/oracle/oradata/lx02/test02.dbf            TEST                           NO          10

13 rows selected.