|
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
(comments are locked)
|
|
You can try something like AND 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. 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_lob42mh4vht.dbf' SIZE 104857600 AUTOEXTEND ON NEXT 104857600 MAXSIZE 2097152000, '/u02/oradata/PAISLEY/datafile/o1_mf_rnje_lob4fl7nwvl.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_lob42mh4vht.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 '12 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 '12 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 '12 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 '12 at 10:05 AM
michaelchapman 7202
Glad to know that you sort it out :)
Jan 30 '12 at 02:48 AM
Usman Butt
(comments are locked)
|
