oracle 11G 表空间满了怎么处理

oracle 11G 表空间满了怎么办
oracle 11G 表空间满了怎么处理

查看所有的表空间及表空间使用率：

SELECT
     A.tablespace_name
    ,A.bytes /1024/1024 AS "size(M)"
    ,( A.bytes - B.bytes )/1024/1024 AS "used(M)"
    ,B.bytes / 1024 / 1024 AS "idle(M)"
    ,Round((( A.bytes - B.bytes ) / A.bytes ) * 100, 2) AS "used-rate"
FROM  
    (SELECT tablespace_name ,SUM(bytes) bytes
       FROM dba_data_files
   GROUP BY tablespace_name) A,
    (SELECT tablespace_name, SUM(bytes) bytes, Max(bytes) largest
       FROM dba_free_space
   GROUP BY tablespace_name) B
WHERE A.tablespace_name = B.tablespace_name
ORDER BY (( A.bytes - B.bytes)/A.bytes) DESC;

TABLESPACE_NAME 		  size(M)    used(M)	idle(M)  used-rate
------------------------------ ---------- ---------- ---------- ----------
KTS_KD_T1		       32767.9844 32720.9219	47.0625      99.86
KFCS				     3100	2823	    277      91.06
KTS_KD_T2			     7600  6762.9375   837.0625      88.99
USERS				 1784.375  1535.1875   249.1875      86.04
KTS_KD_T3			      300      212.5	   87.5      70.83
SYSAUX				     5300   930.3125  4369.6875      17.55
SYSTEM			       32767.9844 1055.73438   31712.25       3.22
KTS_KD_T4			      100     1.8125	98.1875       1.81
UNDOTBS 			    14537   250.3125 14286.6875       1.72

可以看到，哟孤儿表空间使用率是99.86%，明显是表空间满了

DataFile设定了大小，且设置为自动增长，已经到了32G的文件最大值上限

通过添加数据文件进行解决
Alter tablespace KTS_KD_T1 add datafile ‘/data/oracle/product/11.2.0.4/db_1/oradata/KTS_KD_t1_02.DBF’ size 2048M autoextend on next 500M maxsize UNLIMITED;

如果看到的是表空间没满，可以看一下data文件或者表空间是否设置了自动增长

select tablespace_name,file_name,autoextensible from dba_data_files;
select tablespace_name,autoextensible from dba_data_files

如果没设置自动增长，设置一下就行

alter database datafile '/u01/app/oracle/oradata/orcl/xxxx.dbf' autoextend on;