表空间满处理方法

最近服务器上的oracle数据库总是满，进行了如下操作，留个笔记：

 

(1)查看表空间的使用情况：
SQL> select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used", round((f.free/a.total)*100) "% Free"   
      from 
          (select tablespace_name, sum(bytes/(1024*1024)) total   
               from dba_data_files group by tablespace_name) a,  
                    (select tablespace_name, round(sum(bytes/(1024*1024))) used   
                          from dba_extents group by tablespace_name) u,  
                               (select tablespace_name, round(sum(bytes/(1024*1024))) free   
                                      from dba_free_space group by tablespace_name) f  
          WHERE a.tablespace_name = f.tablespace_name and a.tablespace_name = u.tablespace_name;  

TABLESPACE_NAME                     TOTAL       USED       FREE     % used
------------------------------ ---------- ---------- ---------- ----------
    % Free
----------
JK_MBFE                              1024         48        976          5
        95

JK_TYDL                               200          0        200          0
       100

UNDOTBS1                             4545       1871       2674         41
        59

TABLESPACE_NAME                     TOTAL       USED       FREE     % used
------------------------------ ---------- ---------- ---------- ----------
    % Free
----------
SYSAUX                                440        425         15         97
         3

USERS                                   5          3          2         60
        40

JK_PERF                               780        769         11         99
         1

TABLESPACE_NAME                     TOTAL       USED       FREE     % used
------------------------------ ---------- ---------- ---------- ----------
    % Free
----------
SYSTEM                                520        512          8         98
         2

EXAMPLE                               100         68         32         68
        32

JK_LINK                             10240        836       9404          8
        92

TABLESPACE_NAME                     TOTAL       USED       FREE     % used
------------------------------ ---------- ---------- ---------- ----------
    % Free
----------
JK_EBIP                              1024          1       1023          0
       100

10 rows selected.

 

(2)SYSTEM表空间满，查看除了系统用户外还有什么用户使用system表空间：
SQL> select username, default_tablespace,temporary_tablespace from dba_users where   (default_tablespace='SYSTEM'   or   temporary_tablespace='SYSTEM')   and   username   not   in   ('SYSTEM','SYS');

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEMPORARY_TABLESPACE
------------------------------
OUTLN                          SYSTEM
TEMP

MGMT_VIEW                      SYSTEM
TEMP

 

select * from dba_tables where tablespace_NAME ='SYSTEM' AND OWNER NOT IN ('SYS','SYSTEM');
select * from dba_indexes where tablespace_NAME ='SYSTEM' AND OWNER NOT IN ('SYS','SYSTEM');

 

 

(3)查看system和sysaux表空间是否是自动扩展的。

SQL> select tablespace_name,AUTOEXTENSIBLE,INCREMENT_BY from dba_data_files;

TABLESPACE_NAME                AUT INCREMENT_BY
------------------------------ --- ------------
USERS                          YES          160
SYSAUX                         YES         1280
UNDOTBS1                       YES          640
SYSTEM                         YES         1280
EXAMPLE                        YES           80
JK_LINK                        YES        12800
JK_EBIP                        YES        12800
JK_MBFE                        YES        12800
JK_TYDL                        YES         2560
JK_PERF                        YES        25600
JK_FHEBIP                      YES        12800

TABLESPACE_NAME                AUT INCREMENT_BY
------------------------------ --- ------------
SYSTEM                         YES         6400
SYSAUX                         YES         6400 

如果为自动扩展那么AUTOEXTENSIBLE字段的值应为YES，是否为NO；INCREMENT_BY 这个为每次自动扩展的空间大小。

 

(4)最终采取的是扩充表空间的做法：
SQL>alter tablespace "SYSTEM" ADD DATAFILE '/oracle/product/oradata/MONITOR/system02.dbf' size 500M autoextend on next 50M maxsize  unlimited;

SQL> alter tablespace "SYSAUX" ADD DATAFILE '/oracle/product/oradata/MONITOR/sysaux02.dbf' size 500M autoextend on next 50M maxsize unlimited;

SQL> alter database datafile '/oracle/product/10.2/db1/dbs/jk_perf.dbf' resize 1024M;
SQL> alter database datafile '/oracle/product/10.2/db1/dbs/jk_perf.dbf' autoextend on next 200M;