CODEWITHSUNDEEP
databaseoracle-databaseoracle12c
scatolone
scatolone

Reputation: 843

ORA-01691: unable to extend lob segment XXXXXXXX by 8192 in tablespace USERS

I get the below error when I try to insert data in the database. 

ORA-01691: unable to extend lob segment XXXXXX by 8192 in tablespace USERS

I'm using an Oracle database (express 12c version). Googling the error I found that it means that there no more space in the tablespace and you need to increase the dimension of your datafile or the number of datafile you are using. I have 5 datafile of 30 GB and one of them is empty so I don't understand what the problem is.

Edit

SYSTEM      793,19      800     99,15   32768   2,42

SYSAUX      2203,56     2320    94,98   32768   6,72

UNDOTBS1    48,13       23345   0,21    32768   0,15

USERS       153534,5    30720   499,79  30720   499,79

USERS       153534,5    30720   499,79  30720   499,79

USERS       153534,5    30720   499,79  30720   499,79

USERS       153534,5    30720   499,79  30720   499,79

USERS       153534,5    30720   499,79  30720   499,79

Upvotes: 5

Views: 50406

Answers (3)

Sidharth K.Burnwal
Sidharth K.Burnwal

Reputation: 546

You can run this query in your sys to alter the tablespace as per your requirement.

ALTER DATABASE DATAFILE '/scratch/...../SCHEMA_NAME.dbf' RESIZE 5G;

Upvotes: 0

Giovanni
Giovanni

Reputation: 4015

Each tablespace (a logical entity) maps to one or more o.s. file (even on raw device or something different on some installations). One file may belong to only one tablespace.

If you have more than one tablespace you may have a lot fo free space in other tablespace but you may not use it.

You can enlarge you data file oo change the tablespace where your table indexes are located.

Your specific error is related to lob (i.e. blob or clob); may be you are inserting a lot of/large binary objects that fill the tablespace; you can "allocate" the lob segment in a different tablespace.

If you are just experimenting I suggest to enlarge the data file, see here for instructions

Upvotes: 2

davegreen100
davegreen100

Reputation: 2115

paste the results of the following

select d.tablespace_name "TS",
   (select round(sum(s.bytes/(1024*1024)),2)
      from dba_segments s
      where (s.tablespace_name = d.tablespace_name)
      group by s.tablespace_name) "Used",
round(d.bytes/(1024*1024)) "FSize",
round((select sum(s.bytes/(1024*1024))
  from dba_segments s
  where s.tablespace_name = d.tablespace_name
  group by s.tablespace_name)*100/(d.bytes/(1024*1024)),2) "% Used",
round(maxbytes/(1024*1024)) "MAX FSize",
round((select sum(s.bytes/(1024*1024))
  from dba_segments s
  where (s.tablespace_name = d.tablespace_name) AND (d.AUTOEXTENSIBLE = 'YES')
  group by s.tablespace_name)*100/(maxbytes/(1024*1024)),2) "% Used of MAX"  from dba_data_files d;

Upvotes: 3

Related Questions