- Auto-Correlating Session IDs in Oracle Application Test Suite (OATS)
- Removing "Compatibility setting is not supported" Warning before You Do OpenScript Recording
- How to Configure Scripts to use Databanks in OATS
- How to Create Load Testing Scripts Using OpenScript
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:
- How to investigate
- 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.
No comments:
Post a Comment