一次增加表空间的过程

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、检查一下增加的情况

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                                     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空间已足够打了。。。