oracle处理某个表空间满了,表空间满处理方法

最近服务器上的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;