一次增加表空间的过程

我就是我 2022-08-07 11:55 158阅读 0赞

1、统计数据库的剩余的使用空间

SELECT             tablespace\_name,  
            trunc ( sum (bytes ) /  (1024\*1024) ) as free\_m,  
            sum ( blocks ) as free\_blk ,             
            max ( bytes ) / (1024) as big\_chunk\_k,  
                 count (\*) as num\_chunks  
FROM             dba\_free\_space  
GROUP BY         tablespace\_name ;

结果：

TABLESPACE\_NAME                    FREE\_M   FREE\_BLK    BIG\_CHUNK\_K NUM\_CHUNKS  
\------------------------------ ---------- ---------- ----------- ----------  
PSAPUNDO                                 6064        776280      2127872         11  
SYSAUX                                      6              880            7040                1  
PSAPSR3                                    3224        412752      673728            8  
PSAPSR3USR                             10            1384          11072              1  
SYSTEM                                     1229        157320       1258432          2  
PSAPSR3700                             5759         737248      865216            12

2、需要增加SYSAUX空间

select file\_name,bytes/1024/1024 from dba\_data\_files   where tablespace\_name like '%SYS%';

FILE\_NAME                                                                         BYTES/1024/1024  
\---------------------------------------------------------------------------------------------------------  
E:/ORACLE/PII/SAPDATA1/SYSTEM\_1/SYSTEM.DATA1           1000  
  
E:/ORACLE/PII/SAPDATA1/SYSAUX\_1/SYSAUX.DATA1            200

3、找到表空间文件

ALTER DATABASE DATAFILE 'E:/ORACLE/PII/SAPDATA1/SYSAUX\_1/SYSAUX.DATA1' RESIZE 2G;

4、检查一下增加的情况

结果：

TABLESPACE\_NAME                    FREE\_M   FREE\_BLK BIG\_CHUNK\_K NUM\_CHUNKS  
\------------------------------ ---------- ---------- ----------- ----------  
PSAPUNDO                                 6064     776280       2127872         11  
SYSAUX                                     1854        237392      1899136          1  
PSAPSR3                                   3224       412752      673728            8  
PSAPSR3USR                             10           1384          11072              1  
SYSTEM                                     1229       157320      1258432          2  
PSAPSR3700                              5759      737248      865216           12

万事大吉，sysaux空间已足够打了。。。