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統計表空間大小和表空間剩餘大