on 05-26-2013 8:50 AM
Dear All,
Last week we faced one issue, users were not able to update anything in database in sm13 update was getting deactivated and system was throwing dump and all the work process were in running status.
When we checked alert log we came to know that one of the tablespace was full
ORA-1691: unable to extend lobsegment SAPSR3.SYS_LOB0000189627C00005$$ by 8192 in tablespace PSAPSR3701X
Hence we increased tablespace PSAPSR3 and issue was solved partially as there were some network issue as well.
But my question is all tablespace have Autoextend then how come tablespace got full?
Please see below screen shot from db02 and let us know is there any other tablespace is getting full because as per my understanding if we keep autoextend on then it should extent automatically?
Kindly advice
Thanks
Tabrayz
Hi Tabrayz,
It is better to extend SYSTEM tablespace by %20 at least and I guess you can delete the tablespace PSAPSR3701. Did you upgrade your system?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi again,
General recoomendation is to keep empty space for monthly growth. So, for each tablespace you can check the weekly, monthly growth via TCODE: DB02. Take an annual average for monthly growth and always keep that empty space, even your tablespaces are on autoextend.
Autoextent has some side effects. THe tablespace grows on datafiles which are set for autoextent. But you may need to consider growth on new datafiles to distribute load on different disks.
Thats why I always do the growth manually.
Answer to your second question is: Do the resizing while there is less or no activity as during extend operation, Oracle was putting lock on that datafile (At least in old releases AFAIR)
Regards
Hi Tabrayz Abdul ,
See these three columns - Size (MB), Used(%),Total Size(MB).
Here in case of PSAPSR3 tablespace - Size of tablespace is 209152 MB and Total Size of tablespace is 221440 MB. That means when your tablespace used (% ) gets around 98 or 99% then Size (MB) will increase automatically. This can extend maximum up to Total Size ( i.e. 221440 MB).
When your PSAPSR3 tablespace size (MB) = Total Size(MB) , then you have to increase tablespace manually by adding datafile manually.
Here in case of tablespace PSAPSR3701X , it can autoextend up to 1000 MB (121000-120000 = 1000 MB) and also used (%) is 89. So its better to increase tablespace by adding datafile.
PSAPSR3 & PSAPSR3701X tablespace are main in this case.
Regards
Yogesh Garg
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
But my question is all tablespace have Autoextend then how come tablespace got full?
Autoextend option has maxsize clause with which you can limit file size growth to specified value. In that case tablespace can be expanded but has limit as
limit = 0;
foreach datafile in tablespace
if autoexend on
limit += datafile.maxsize;
else
limit += datafile.size;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello
ORA-1691: unable to extend lobsegment SAPSR3.SYS_LOB0000189627C00005$$ by 8192 in tablespace PSAPSR3701X
Hence we increased tablespace PSAPSR3 and issue was solved partially as there were some network issue as well.
Are you sure that the problem is NOT with the PSAPSR3701X tablespace and it is with PSAPSR3.
The ORA error points to tablespace PSAPSR3701X
But my question is all tablespace have Autoextend then how come tablespace got full?
Check whether all the datafiles are in Auto Extend mode and there is space in the file system they reside.
Cheers
RB
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.