oracle ora 01691,ORA-01691 数据库表空间占满

做藏文网络文本采集过程中发现网页数据无法提交到数据库中，查日志发现ORA-01691

错误，数据库表空间占满了。有下面的解决方法。

=======

ORA-01691:

unable to extend lob segment BPM49_TEST2.SYS_LOB0000059105C00008$$ by 128 in

tablespace JACK_DATA

今天发现这么一个错误，查看错误信息发现原因是表空间无法分配新的空间给表。所以报错。

下面是oracle官方错误代码

ORA-01691

unable to extend lob segment string.string by string in tablespace string

Cause:

Failed to allocate an extent for LOB segment in tablespace.

Action:

Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the

tablespace indicated.

方法1：修改数据文件的扩展性

alter

database datafile '文件路径' autoextend on next 100m maxsize 4000M;

方法2：给表空间增加新的数据文件

alter

tablespace jack_data add datafile '数据文件路径‘ size 1000m autoextend on next

100m maxsize 4000M;

1

要知道表空间大小，就要明白两种增加表空间大小的方式

第一种：格式化数据文件初始大小并设置自增长到最大值

create tablespace d_test1

datafile '/test1_data/datafile01.dbf' size 10m autoextend on next 5m maxsize 100m;

2

第二种：格式化数据文件初始大小不设置自增长，当然也就没有最大值

alter tablespace d_test1 add

datafile '/test1_data/datafile02.dbf' size 10m;

3

4

分别统计增加表空见两种方式的数据文件大小总值

SELECT

t.tablespace_name,sum(t.MAXBYTES/1024/1024/1024) TOTAL FROM DBA_DATA_FILES T

WHERE

T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

AND

T.AUTOEXTENSIBLE='YES' group by tablespace_name

UNION ALL

SELECT

tablespace_name,sum(bytes/1024/1024/1024) TOTAL FROM DBA_DATA_FILES T

WHERE

T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

AND

T.AUTOEXTENSIBLE='NO' group by tablespace_name

5

统计增加表空见两种方式的数据文件大小总值，也就是统计表空间总大小

WITH

TABLESPACE_TOTAL AS

(

SELECT

tablespace_name,sum(MAXBYTES/1024/1024/1024) TOTAL FROM DBA_DATA_FILES T

WHERE

T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

AND

T.AUTOEXTENSIBLE='YES' group by tablespace_name

UNION ALL

SELECT

tablespace_name,sum(bytes/1024/1024/1024) TOTAL FROM DBA_DATA_FILES T

WHERE

T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

AND

T.AUTOEXTENSIBLE='NO' group by tablespace_name

)

SELECT

TABLESPACE_NAME,SUM(TOTAL) TOTAL FROM TABLESPACE_TOTAL GROUP BY TABLESPACE_NAME

6

那么表空间剩余大小如何统计呢?

首先看看表空间已使用的大小

select

TABLESPACE_NAME,sum(bytes/1024/1024/1024) TOTAL from dba_segments

where

tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

GROUP BY TABLESPACE_NAME

7

根据dba_data_files和dba_segments统计表空间总大小和剩余大小

SELECT

A.TABLESPACE_NAME,B.TOTAL/1024/1024/1024||'G',(B.TOTAL-A.USE)/1024/1024/1024||'G' FREE FROM

(

select

TABLESPACE_NAME,sum(bytes) as USE from dba_segments

where

tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

GROUP BY

TABLESPACE_NAME

) A ,

(

WITH

TABLESPACE_TOTAL AS

(

SELECT

tablespace_name,sum(MAXBYTES) TOTAL FROM DBA_DATA_FILES T

WHERE

T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

AND

T.AUTOEXTENSIBLE='YES' group by tablespace_name

UNION ALL

SELECT

tablespace_name,sum(bytes) TOTAL FROM DBA_DATA_FILES T

WHERE

T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

AND

T.AUTOEXTENSIBLE='NO' group by tablespace_name

)

SELECT

TABLESPACE_NAME,SUM(TOTAL) TOTAL FROM TABLESPACE_TOTAL GROUP BY TABLESPACE_NAME

) B

WHERE

A.TABLESPACE_NAME=B.TABLESPACE_NAME

8

注意：dba_free_space统计信息

select

tablespace_name,FILE_ID,bytes/1024/1024 from dba_free_space

where

tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

或者

select

tablespace_name, sum(bytes / 1024 / 1024 / 1024) total_free

from dba_free_space

where tablespace_name NOT IN

('USERS', 'SYSAUX', 'UNDOTBS1',

'SYSTEM', 'UNDOTBS2')

group by tablespace_name

select

tablespace_name,sum(bytes)/1024/1024/1024 G,sum(maxbytes)/1024/1024/1024

mg,(sum(maxbytes)-sum(bytes))/1024/1024/1024 free from dba_data_files

where

tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

group by

tablespace_name

可以看出dba_free_space表是dba_data_files表的各个数据文件bytes列已经真正使用后剩余大小

9

根据dba_data_files和dba_free_space统计表空间大小和表空间剩余大小

SELECT

A.TABLESPACE_NAME,B.TOTAL/1024/1024/1024 ||'G',(B.TOTAL-A.USE)/1024/1024/1024

||'G' FREE FROM

(

select

c.tablespace_name,(c.p_use-d.p_free) as use from

(

select

tablespace_name,sum(bytes) as p_use from dba_data_files

where

tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

group by

tablespace_name

) C,

(

select

tablespace_name,sum(bytes) as  p_free

from dba_free_space

where

tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

group by

tablespace_name

) D

where

C.tablespace_name=D.tablespace_name

) A ,

(

WITH

TABLESPACE_TOTAL AS

(

SELECT

tablespace_name,sum(MAXBYTES) TOTAL FROM DBA_DATA_FILES T

WHERE

T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

AND

T.AUTOEXTENSIBLE='YES' group by tablespace_name

UNION ALL

SELECT

tablespace_name,sum(bytes) TOTAL FROM DBA_DATA_FILES T

WHERE

T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')

AND

T.AUTOEXTENSIBLE='NO' group by tablespace_name

)

SELECT

TABLESPACE_NAME,SUM(TOTAL) TOTAL FROM TABLESPACE_TOTAL GROUP BY TABLESPACE_NAME

) B

WHERE

A.TABLESPACE_NAME=B.TABLESPACE_NAME

不管是根据dba_data_files和dba_free_space统计表空间大小和表空间剩余大小还是根据dba_data_files和dba_segments统计表空间大小和表空间剩余大小,

两种方法的偏差值都不大，当然我还是建议采用根据dba_data_files和dba_free_space统计表空间大小和表空间剩余大