记一次表空间使用满了 Cause: java.sql.SQLException: ORA-01653: unable to extend table

java报错如下：Cause: java.sql.SQLException: ORA-01653: unable to extend table

经过查询发现是表空间满了

1、查询表空间使用率，查询命令如下：

set linesize 200 pagesize 999
column tablespace_name format a25
column "% Used" format a10
column "Used" format a30
select t.tablespace_name,
       t.mb "TotalMB",
       t.mb - nvl(f.mb, 0) "Used(MB)",
       nvl(f.mb, 0) "Free(MB)",
       lpad(ceil((1 - nvl(f.mb, 0) / decode(t.mb, 0, 1, t.mb)) * 100) || '%', 6) "% Used",
       t.ext "Ext",
       '|' || rpad(lpad('#', ceil((1 - nvl(f.mb, 0) / decode(t.mb, 0, 1, t.mb)) * 20), '#'), 20, ' ') || '|' "Used"
  from (select tablespace_name, trunc(sum(bytes) / 1048576) MB
          from dba_free_space
         group by tablespace_name
        union all
        select tablespace_name, trunc(sum(bytes_free) / 1048576) MB
          from v$temp_space_header
         group by tablespace_name) f,
       (select tablespace_name,
               trunc(sum(bytes) / 1048576) MB,
               max(autoextensible) ext
          from dba_data_files
         group by tablespace_name
        union all
        select tablespace_name,
               trunc(sum(bytes) / 1048576) MB,
               max(autoextensible) ext
          from dba_temp_files
         group by tablespace_name) t
 where t.tablespace_name = f.tablespace_name(+)
 order by 5,1;

结果如下：通过SYSTEM和USERS判断表空间使用率（下面已经扩大后的表空间）

TABLESPACE_NAME              TotalMB   Used(MB)   Free(MB) % Used     Ext Used
------------------------- ---------- ---------- ---------- ---------- --- ------------------------------
UNDOTBS1                          75         17         58    23%     YES |#####               |
USERS                              5          2          3    40%     YES |########            |
SYSAUX                           510        482         28    95%     YES |################### |
SYSTEM                           680        672          8    99%     YES |####################|
TEMP                              29         29          0   100%     YES |####################|

2、查看表空间的名字及文件所在位置

select tablespace_name,
       file_id,
       file_name,
       round(bytes / (1024 * 1024), 0) total_space
  from sys.dba_data_files
 order by tablespace_name;

或者查询单个表空间的位置

或者查看单个表空间位置
select FILE_NAME,TABLESPACE_NAME,bytes/1024/1024 from dba_data_files where TABLESPACE_NAME='SYSTEM';
select FILE_NAME,TABLESPACE_NAME,bytes/1024/1024 from dba_data_files where TABLESPACE_NAME='USERS';

3、查询表空间信息

select username,default_tablespace, t.username, t.USER_ID from dba_users t;
select username,default_tablespace,t.* from dba_users t;

4、表空间扩容 -- 表空间最大的尺寸为32G

alter database datafile '表空间位置' resize 新的尺寸
alter database datafile '/data/oracle/oradata/orcl/system01.dbf' resize 2048M;
alter database datafile '/data/oracle/oradata/orcl/users01.dbf' resize 36721M;

5、新增表空间

alter tablespace SYSTEM add datafile '/data/oracle/oradata/orcl/system02.dbf' size 1024M autoextend off;
alter tablespace USERS add datafile '/data/oracle/oradata/orcl/users02.dbf' size 4096M autoextend off;