ORA-1691错误解决办法

最新推荐文章于 2024-03-27 23:06:35 发布

挠背小能手

最新推荐文章于 2024-03-27 23:06:35 发布

阅读量1.9k

点赞数

分类专栏： Oracle日常运维文章标签： oracle

本文链接：https://blog.csdn.net/m0_37625564/article/details/114194837

版权

Oracle日常运维专栏收录该内容

129 篇文章 18 订阅

订阅专栏

ORA-1691一般是由于表空间满了，表空间无法扩展，无法写入数据导致，处理方法可以通过增加数据文件或开启数据文件自动扩展方法解决。

Sat Feb 27 20:24:53 2021
ORA-1691: unable to extend lobsegment EHOSPITAL.SYS_LOB0000095173C00009$$ by 128 in tablespace              TS_YYGL 
ORA-1691: unable to extend lobsegment EHOSPITAL.SYS_LOB0000095173C00009$$ by 1024 in tablespace              TS_YYGL 
ORA-1691: unable to extend lobsegment EHOSPITAL.SYS_LOB0000095173C00009$$ by 128 in tablespace              TS_YYGL 
ORA-1691: unable to extend lobsegment EHOSPITAL.SYS_LOB0000095173C00009$$ by 1024 in tablespace              TS_YYGL 
Sat Feb 27 20:33:14 2021
ORA-1691: unable to extend lobsegment EHOSPITAL.SYS_LOB0000095173C00009$$ by 128 in tablespace              TS_YYGL 
ORA-1691: unable to extend lobsegment EHOSPITAL.SYS_LOB0000095173C00009$$ by 1024 in tablespace              TS_YYGL 
ORA-1691: unable to extend lobsegment EHOSPITAL.SYS_LOB0000095173C00009$$ by 128 in tablespace              TS_YYGL 
ORA-1691: unable to extend lobsegment EHOSPITAL.SYS_LOB0000095173C00009$$ by 1024 in tablespace              TS_YYGL

报错中得知，无法扩展extend，查看表空间使用率

SQL> set linesize 160
SQL> set pagesize 999
SQL> col TABLESPACE_NAME for a18
SQL> col TBS_TOTAL_MB for 9999999
SQL> col TBS_USED_MB for 9999999
SQL> col TBS_FREE_MB for 9999999
SQL> col TBS_RATE for a11           
SQL> col EXTEND_MAX_MB for 9999999999999
SQL> col EXTEND_FREE_MB for 9999999999999
SQL> col EXTEND_RATE for a11
SQL> select a.tablespace_name,
  2        round(current_size / 1024 / 1024, 1) TBS_TOTAL_MB,
  3        round((current_size - b.free_bytes) / 1024 / 1024, 1) TBS_USED_MB,
  4        round(b.free_bytes / 1024 / 1024, 1) TBS_FREE_MB,
  5        round(((current_size - b.free_bytes) / current_size) * 100, 1) || '%' TBS_RATE,
  6        round(a.max_size / 1024 / 1024, 1) EXTEND_MAX_MB,
  7        round((a.max_size - (current_size - b.free_bytes)) / 1024 / 1024, 1) EXTEND_FREE_MB,
  8        round(((current_size - b.free_bytes) / a.max_size) * 100, 1) || '%' "EXTEND_RATE"
  9    from (select tablespace_name,
 10                sum(ddf.bytes) current_size,
 11                sum(case
 12                      when ddf.autoextensible = 'YES' THEN
 13                        DDF.MAXBYTES
 14                      ELSE
 15                        DDF.BYTES
 16                    END) max_size
 17            from dba_data_files ddf
 18          group by tablespace_name
 19          union
 20          select tablespace_name,
 21                sum(ddf.bytes) current_size,
 22                sum(case
 23                      when ddf.autoextensible = 'YES' THEN
 24                        DDF.MAXBYTES
 25                      ELSE
 26                        DDF.BYTES
 27                    END) max_size
 28            from dba_temp_files ddf
 29          group by tablespace_name) a,
 30        (select dfs.tablespace_name, sum(dfs.bytes) free_bytes
 31            from dba_free_space dfs
 32          group by dfs.tablespace_name
 33          union
 34          select tfs.tablespace_name, sum(tfs.BYTES_FREE) free_bytes
 35            from v$TEMP_SPACE_HEADER tfs
 36          group by tfs.tablespace_name) b
 37  where a.tablespace_name = b.tablespace_name(+)
 38  order by 8;

TABLESPACE_NAME    TBS_TOTAL_MB TBS_USED_MB TBS_FREE_MB TBS_RATE     EXTEND_MAX_MB EXTEND_FREE_MB EXTEND_RATE
------------------ ------------ ----------- ----------- ----------- -------------- -------------- -----------
TS_CHS5                     512          17         495 3.4%                  4096           4079 .4%
USERS                       400         360          40 90.1%                32768          32408 1.1%
UNDOTBS1                   7775         577        7198 7.4%                 32768          32191 1.8%
**TS_YYGL                   32763       32763           0 100%                 32768              5 100%**
TS_RADTS                   1540        1445          95 93.8%                 8192           6747 17.6%
SYSAUX                     6720        6397         323 95.2%                32768          26371 19.5%
SYSTEM                     1290         847         443 65.7%                32768          31921 2.6%
TEMP                       8190        8190           0 100%                 32768          24578 25%
TS_HISJC                   2048        1068         980 52.1%                32768          31700 3.3%
TS_CBHS                   11376       10479         897 92.1%                32768          22289 32%
TS_TJGL                   15360       12604        2756 82.1%                32768          20164 38.5%

可以看到，TS_YYGL表空间最大可扩展已达到100%，查看该表空间数据文件情况

SQL> col file_name for a45
SQL> set linesize 200
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 size_gb,autoextensible,maxbytes/1024/1024/1024 max_gb from dba_data_files where tablespace_name='TS_YYGL';

TABLESPACE_NAME    FILE_NAME                                        SIZE_GB AUT     MAX_GB
------------------ --------------------------------------------- ---------- --- ----------
TS_YYGL            /u01/app/oracle/oradata/oracle/yygl01.dbf     31.9951172 YES 31.9999847

由查询得知，TS_YYGL表空间目前只有一个数据文件，开启了自动扩展，而且当前已经扩展到最大使用大小，此时通过增加数据文件方式解决。

先查操作系统级别是否剩余空间充足

SQL> !df -Th
df: `/root/.gvfs': Permission denied
Filesystem                       Type     Size  Used Avail Use% Mounted on
**/dev/mapper/vg_oaserver-LogVol01 ext4     390G  150G  221G  41% /**
tmpfs                            tmpfs    3.9G  372K  3.9G   1% /dev/shm
/dev/sda1                        ext4     194M   35M  150M  19% /boot
/dev/sr0                         iso9660   61M   61M     0 100% /media/VMware Tools

物理空间还剩390GB，剩余空间充足，执行添加数据文件操作

SQL> alter tablespace TS_YYGL add datafile '/u01/app/oracle/oradata/oracle/yygl02.dbf' size 10g autoextend on;

Tablespace altered.

SQL> alter tablespace TS_YYGL add datafile '/u01/app/oracle/oradata/oracle/yygl03.dbf' size 10g autoextend on;

Tablespace altered.

查看该表空间数据文件情况

SQL> col file_name for a45
SQL> set linesize 200
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 size_gb,autoextensible,maxbytes/1024/1024/1024 max_gb from dba_data_files where tablespace_name='TS_YYGL';

TABLESPACE_NAME    FILE_NAME                                        SIZE_GB AUT     MAX_GB
------------------ --------------------------------------------- ---------- --- ----------
TS_YYGL            /u01/app/oracle/oradata/oracle/yygl01.dbf     31.9951172 YES 31.9999847
TS_YYGL            /u01/app/oracle/oradata/oracle/yygl02.dbf             10 YES 31.9999847
TS_YYGL            /u01/app/oracle/oradata/oracle/yygl03.dbf             10 YES 31.9999847

检查表空间使用率

SQL> set linesize 160
SQL> set pagesize 999
SQL> col TABLESPACE_NAME for a18
SQL> col TBS_TOTAL_MB for 9999999
SQL> col TBS_USED_MB for 9999999
SQL> col TBS_FREE_MB for 9999999
SQL> col TBS_RATE for a11           
SQL> col EXTEND_MAX_MB for 9999999999999
SQL> col EXTEND_FREE_MB for 9999999999999
SQL> col EXTEND_RATE for a11
SQL> select a.tablespace_name,
  2        round(current_size / 1024 / 1024, 1) TBS_TOTAL_MB,
  3        round((current_size - b.free_bytes) / 1024 / 1024, 1) TBS_USED_MB,
  4        round(b.free_bytes / 1024 / 1024, 1) TBS_FREE_MB,
  5        round(((current_size - b.free_bytes) / current_size) * 100, 1) || '%' TBS_RATE,
  6        round(a.max_size / 1024 / 1024, 1) EXTEND_MAX_MB,
  7        round((a.max_size - (current_size - b.free_bytes)) / 1024 / 1024, 1) EXTEND_FREE_MB,
  8        round(((current_size - b.free_bytes) / a.max_size) * 100, 1) || '%' "EXTEND_RATE"
  9    from (select tablespace_name,
 10                sum(ddf.bytes) current_size,
 11                sum(case
 12                      when ddf.autoextensible = 'YES' THEN
 13                        DDF.MAXBYTES
 14                      ELSE
 15                        DDF.BYTES
 16                    END) max_size
 17            from dba_data_files ddf
 18          group by tablespace_name
 19          union
 20          select tablespace_name,
 21                sum(ddf.bytes) current_size,
 22                sum(case
 23                      when ddf.autoextensible = 'YES' THEN
 24                        DDF.MAXBYTES
 25                      ELSE
 26                        DDF.BYTES
 27                    END) max_size
 28            from dba_temp_files ddf
 29          group by tablespace_name) a,
 30        (select dfs.tablespace_name, sum(dfs.bytes) free_bytes
 31            from dba_free_space dfs
 32          group by dfs.tablespace_name
 33          union
 34          select tfs.tablespace_name, sum(tfs.BYTES_FREE) free_bytes
 35            from v$TEMP_SPACE_HEADER tfs
 36          group by tfs.tablespace_name) b
 37  where a.tablespace_name = b.tablespace_name(+)
 38  order by 8;

TABLESPACE_NAME    TBS_TOTAL_MB TBS_USED_MB TBS_FREE_MB TBS_RATE     EXTEND_MAX_MB EXTEND_FREE_MB EXTEND_RATE
------------------ ------------ ----------- ----------- ----------- -------------- -------------- -----------
TS_CHS5                     512          17         495 3.4%                  4096           4079 .4%
USERS                       400         360          40 90.1%                32768          32408 1.1%
UNDOTBS1                   7775         576        7199 7.4%                 32768          32192 1.8%
TS_RADTS                   1540        1445          95 93.8%                 8192           6747 17.6%
SYSAUX                     6720        6397         323 95.2%                32768          26371 19.5%
SYSTEM                     1290         847         443 65.7%                32768          31921 2.6%
TEMP                       8190        8190           0 100%                 32768          24578 25%
TS_HISJC                   2048        1068         980 52.1%                32768          31700 3.3%
TS_CBHS                   11376       10479         897 92.1%                32768          22289 32%
**TS_YYGL                   53243       32765       20478 61.5%                98304          65539 33.3%**
TS_TJGL                   15360       12604        2756 82.1%                32768          20164 38.5%

11 rows selected.

表空间剩余充足，检查点切换，检查alert日志

SQL> alter system switch logfile;

System altered.

SQL> alter system archive log current;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> !tail -30f alert*
alter tablespace TS_YYGL add datafile '/u01/app/oracle/oradata/oracle/yygl02.dbf' size 10g autoextend on
Sat Feb 27 21:55:20 2021
Completed: alter tablespace TS_YYGL add datafile '/u01/app/oracle/oradata/oracle/yygl02.dbf' size 10g autoextend on
Sat Feb 27 21:55:51 2021
alter tablespace TS_YYGL add datafile '/u01/app/oracle/oradata/oracle/yygl03.dbf' size 10g autoextend on
Sat Feb 27 21:56:10 2021
Completed: alter tablespace TS_YYGL add datafile '/u01/app/oracle/oradata/oracle/yygl03.dbf' size 10g autoextend on
Sat Feb 27 22:05:00 2021
Thread 1 advanced to log sequence 9276 (LGWR switch)
  Current log# 1 seq# 9276 mem# 0: /u01/app/oracle/oradata/oracle/redo01.log
Sat Feb 27 22:05:01 2021
Archived Log entry 9115 added for thread 1 sequence 9275 ID 0x6fc324bd dest 1:
Sat Feb 27 22:05:10 2021
ALTER SYSTEM ARCHIVE LOG
Thread 1 advanced to log sequence 9277 (LGWR switch)
  Current log# 2 seq# 9277 mem# 0: /u01/app/oracle/oradata/oracle/redo02.log
Archived Log entry 9116 added for thread 1 sequence 9276 ID 0x6fc324bd dest 1:
Sat Feb 27 22:05:15 2021
Thread 1 advanced to log sequence 9278 (LGWR switch)
  Current log# 3 seq# 9278 mem# 0: /u01/app/oracle/oradata/oracle/redo03.log
Sat Feb 27 22:05:15 2021
Archived Log entry 9117 added for thread 1 sequence 9277 ID 0x6fc324bd dest 1:

报错消失，问题解决