x

Unable to extend logsegment

Oracle experts, I am getting the following message in the alert log:

ORA-1691: unable to extend lobsegment NAVIGATOR.SYS_LOB0000058142C00004$$ by 128 in tablespace RNJE_LOB ORA-1691: unable to extend lobsegment NAVIGATOR.SYS_LOB0000058142C00004$$ by 8192 in tablespace RNJE_LOB

I have checked the dba_tablespace_usage_metrics and the TS is 99% used, there are 4 datafiles associated with this TS. I have checked the web for a resolution as I am not an Oracle DBA, and did not find a way to determine exactly which file is under stress, also whether doing a coalesce, extending the file or creating a new datafile was the answer. any help or direction would be greatly appreciated. Regards
more ▼

asked Jan 25, 2012 at 11:41 AM in Default

michaelchapman 7202 gravatar image

michaelchapman 7202
112 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You can try something like

select dbms_metadata.get_ddl('TABLESPACE','RNJE_LOB') from dual;

AND

SELECT * FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'RNJE_LOB'

to find the detailed information. Based upon the MAXSIZE and AUTOEXTEND property you can sort out what to do. Locally Managed tablespaces generally do not need to coalesce free extents. And since you have already said that it is 99 percent full so most probably the Extension of the data file size is required.

Generally, people stay away from adding a new data file, as this could mean more administrative cost and if added, then as per best practice a backup should be done immediately. Hope it helps.
more ▼

answered Jan 26, 2012 at 04:05 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

Usman, I have run the query to extract the ddl for the tablespace, I am trying to determine which one of the files is the one I need to extend, or if I need to extend all of them. There are 4 files associated with this tablespace, the ddl I extracted follows:

SQL> select dbms_metadata.get_ddl('TABLESPACE','RNJE_LOB') from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','RNJE_LOB')

CREATE TABLESPACE "RNJE_LOB" DATAFILE '/u02/oradata/PAISLEY/datafile/o1_mf_rnje_lob_42mh4vht_.dbf' SIZE 104857600 AUTOEXTEND ON NEXT 104857600 MAXSIZE 2097152000, '/u02/oradata/PAISLEY/datafile/o1_mf_rnje_lob_4fl7nwvl_.dbf' SIZE 10737418240,

'/u02/oradata/PAISLEY/datafile/RNJE_LOB_2.dbf' SIZE 3221225472 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M, '/u02/oradata/PAISLEY/datafile/rnje_lob_3.dbf' SIZE 3221225472 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

DBMS_METADATA.GET_DDL('TABLESPACE','RNJE_LOB')

ALTER DATABASE DATAFILE '/u02/oradata/PAISLEY/datafile/o1_mf_rnje_lob_42mh4vht_.dbf' RESIZE 3430940672 0 ALTER DATABASE DATAFILE '/u02/oradata/PAISLEY/datafile/RNJE_LOB_2.dbf' RESIZE 34292695040 ALTER DATABASE DATAFILE '/u02/oradata/PAISLEY/datafile/rnje_lob_3.dbf' RESIZE 10737418240

Thanks for your help. Regards
Jan 26, 2012 at 06:05 AM michaelchapman 7202
You could have run the other query to find the free space in the data files. That will give more clear picture.
Jan 26, 2012 at 06:18 AM Usman Butt
If your environment allows you to increase all the files, then you can go for decent size for all the files. But if you are low on disk storage, then you could add a decent size to only one of the files. Moreover, if you do not have the space on the partition, then you would have to add a data file on other partition. Hope it helps.
Jan 26, 2012 at 06:36 AM Usman Butt
Discovered 2 of the 4 files associated with the RNJE_LOB tablespace did not have autoextend set on, after turning the parameter on the files were able to extend. Thanks for the help. Regards
Jan 27, 2012 at 10:05 AM michaelchapman 7202
Glad to know that you sort it out :)
Jan 30, 2012 at 02:48 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x378
x4

asked: Jan 25, 2012 at 11:41 AM

Seen: 1885 times

Last Updated: Jan 25, 2012 at 01:37 PM