增加表空间大小

表空间是对数据文件的逻辑管理，随着数据的不断增加，表空间的空闲空间会不断变小，下面介绍增加表空间的三种方法
1  增加数据文件大小

点击(此处)折叠或打开

1. SQL> select file_id , file_name , tablespace_name , bytes/1024/1024 from dba_data_files;
   
2. 
   
3.    FILE_ID FILE_NAME TABLESPACE BYTES/1024/1024
   
4. ---------- -------------------------------------------------- ---------- ---------------
   
5.          1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
   
6.          2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
   
7.          3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
   
8.          4 /u01/app/oracle/oradata/PROD/disk3/system02.dbf UNDOTBS2 10
   
9.          5 /u01/app/oracle/oradata/PROD/disk4/undotbs02.dbf UNDOTBS3 10
   
10.          6 /u01/app/oracle/oradata/PROD/disk3/user01.dbf USERS 10
    
11. 
    
12. 6 rows selected.
    
13. 
    
14. SQL> alter database datafile 6 resize 15m;
    
15. 
    
16. Database altered.
    
17. 
    
18. SQL> select file_id , file_name , tablespace_name , bytes/1024/1024 from dba_data_files;
    
19. 
    
20.    FILE_ID FILE_NAME TABLESPACE BYTES/1024/1024
    
21. ---------- -------------------------------------------------- ---------- ---------------
    
22.          1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
    
23.          2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
    
24.          3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
    
25.          4 /u01/app/oracle/oradata/PROD/disk3/system02.dbf UNDOTBS2 10
    
26.          5 /u01/app/oracle/oradata/PROD/disk4/undotbs02.dbf UNDOTBS3 10
    
27.          6 /u01/app/oracle/oradata/PROD/disk3/user01.dbf USERS 15
    
28. 
    
29. 6 rows selected.

2  设置表空间为自动增长的方式

点击(此处)折叠或打开

1. SQL> alter database datafile 6 autoextend on next 5m maxsize 50m;
   
2. 
   
3. Database altered.

3  添加数据文件

点击(此处)折叠或打开

1. SQL> alter tablespace users add datafile \'/u01/app/oracle/oradata/PROD/disk4/user02.dbf\' size 10m;
   
2. 
   
3. Tablespace altered.
   
4. 
   
5. SQL> select file_id , file_name , tablespace_name , bytes/1024/1024 from dba_data_files;
   
6. 
   
7.    FILE_ID FILE_NAME TABLESPACE BYTES/1024/1024
   
8. ---------- -------------------------------------------------- ---------- ---------------
   
9.          1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
   
10.          2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
    
11.          3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
    
12.          4 /u01/app/oracle/oradata/PROD/disk3/system02.dbf UNDOTBS2 10
    
13.          5 /u01/app/oracle/oradata/PROD/disk4/undotbs02.dbf UNDOTBS3 10
    
14.          6 /u01/app/oracle/oradata/PROD/disk3/user01.dbf USERS 15
    
15.          7 /u01/app/oracle/oradata/PROD/disk4/user02.dbf USERS 10
    
16. 
    
17. 7 rows selected.

以上即为增加表空间的三种方法
然而，第一种方法空间很固定，有一定的局限行，建议选第二种或第三种方法

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29598413/viewspace-1140939/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29598413/viewspace-1140939/