Thursday, November 7, 2013

ORA-01691: unable to extend lob segment

This article is one of the Oracle Application Test Suite (OATS)[1] series published on Xml and More, which includes the following:
Today, I have seen the following message:
ORA-1691: unable to extend lobsegment OLT.SYS_LOB0000079598C00002$ by 128 in tablespace USERS
from alert/log.xml, which was reported by the Oracle DB of OATS.[1]

In this article, we will show:
  1. How to investigate
  2. How to provide the solution

ORA-01691


If you see "ORA-01691" error, normally this means that the tablespace is full or that there is not an extent (aka chunk) available that fits the size needed.[2]

To investigate further, you can query the DB with the following SQL commands:[2]

SQL> set long 100000
SQL> select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
--------------------------------------------------------------------------------

  CREATE TABLESPACE "USERS" DATAFILE
  '/scratch/aime1/app/oracle11.2.0.3.0/oradata/ps6stg7/users01.dbf' SIZE 5242880

  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  '/scratch/aime1/app/oracle11.2.0.3.0/oradata/ps6stg7/users01.dbf' RESIZE 34359
214080

When I checked the used% of tablespace USERS, it showed that it's almost 100% full. So, the error is due to the tablespace being full.

SQL>  select * from dba_tablespace_usage_metrics order by used_percent desc;

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
USERS                             4194128         4194302   99.9958515

The Solution


You can find out what schema (or user) uses tablespace USERS by issuing the following SQL command:

SQL> select owner, table_name,tablespace_name from dba_tables where tablespace_name='USERS';

From the output, we know tablespace USERS is shared by the following users:
In our environment, we have used OLT to run load tests. For OLT, typically it is the session data that is taking most of the space in the DB. As an immediate workaround, you can try removing sessions from OLT Controller UI. However, it may run very slowly because tablespace USERS has already run out of space. To facilitate the session-removing task from OLT Controller UI, I have added 100MB to the tablespace USERS by giving it a second datafile:

SQL> ALTER TABLESPACE users ADD DATAFILE '/scratch/aime1/app/oracle11.2.0.3.0/oradata/ps6stg7/users02.dbf' size 100m;

Tablespace altered.

By adding some space to the tablespace, it helped smooth out the task of session removing. Admittedly, it was still a slow process. But, the solution worked.

References

  1. Oracle Application Testing Suite
  2. ORA-01691: unable to extend lob segment (OTN)
  3. Administering Tablespaces
  4. SQL Purge command
  5. Folllow @OracleAskTom
  6. Oracle Load Testing Overview
  7. Oracle Test Manager Overview
at

No comments:

Post a Comment