Question:
from sapdba,
Alert - CRITICAL SEGMENTS:
SEGMENT(S) TST03 WOULD CAUSE A TABLESPACE OVERFLOW BY ALLOC. UP TO 2
NEXT EXTENT(S)
TABLESPACE SEGMENT TYPE EXTENTS NEXT (K)
-------------------------------------------------------------------------------
PSAPPROTD TST03 TABLE 2<= 2 1310720
info of tablespace
TABLESPACE_NAME INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT
------------------------------ ---------- ---------- ---------- ----------
PSAPPROTD 1048576 1048576 1 300
info of segments
select bytes, blocks, extents, initial_extent, next_extent, min_extents, max_extents from dba_segments
where segment_name='TST03';
BYTES BLOCKS EXTENTS INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT
---------- ---------- ---------- ---------- ---------- ---------- ----------
4914339840 599895 75 1181401088 1342177280 3 999
1 oracle data block is 8kb
so no of blocks (599895) * size of oracle data block (8kb) = 4799160kb = 4914339840 b
in sapdba, check free space for PSAPPROTD
Total tablespace size : 1230280 blocks / 9842240 K
Total free space : 580369 blocks / 4642952 K
Largest free space area : 261118 blocks / 2088944 K
when i add 2 extents, the size required will be (next_extent * 2) which is 1310720 * 2 = 2621440 and this is greater than the value of the largest free space area and hence sapdba will detect the error.
is my analysis correct ?
Answer:
hey, guys.. i have found this
http://help.sap.com/sapdocu/core/470/helpdata/EN/0d/d2f97f4a0c11d182b80000e829fbfe/frameset.htm
which says
The exception to this rule is for tablespaces PSAPPROTD or PSAPPROTI , which contain SAP logs from background processes and spool requests. Delete old log files and requests instead of extending these tablespaces.
Answer:
Database error 1562 at UPD access to table ZPSIDS
ORA-01562: failed to extend rollback segment number
8#ORA-01650: unable to extend rollback segment PRS_6 by 128
in tablespace PSAPROLL#
01650, 00000, "unable to extend rollback segment %s by %s in tablespace %s"
// *Cause: Failed to allocate an extent for rollback segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.
Tablespace Size (kb) Free (kb) Used (%) Tab/ind Extents AutoExt (kb) Used (%) Status
PSAPROLL 14.840.264 5.848 99 30 9.506 Off 99 ONLINE
Owner Object Type Tablespace KBytes Blocks Extents MaxExtents Next (K)
SYS PRS_6 ROLLBACK PSAPROLL 185.360 23.170 180 500 1.024
1) 8#ORA-01650: unable to extend rollback segment PRS_6 by 128
in tablespace PSAPROLL#. where does the 128 comes from?
2) in prs_6, the maxextents is 500. currently, only 180 extents, so left 320 extents which could be added.
but i am not sure its the size of the tablespace or the size of the object (which in this case is the
segment) that matters?
Answer:
My guess isthat your PSAPROLL is too full. You need to extend this. If doing big stuff like client imports, create a PSAPBIGROLL tablespace. There are notes on that at OSS.
Snowy
_________________
SapFans Moderator
NetWeaver ‘04–SAP Web AS for ORACLE certified
Search: /forums/search.php
SAP Notes: http://service.sap.com/notes
SAP Help: http://help.sap.com
Basic Rules: /forums/viewtopic.php?t=222759
Answer:
right, snowy,
1) i have added 2 data files to the rollback tablespace. but my understanding is that rollback tablespace is able to shrink and grow dynamically, upon the end of transactions?
2) in dev, i have left the rollback tablespace at its full. and there are no problem with the instance running. i guess the problem will be that i wont be able to do a restore from an online backup since the rollback has no space for the latest transactions?
please advise
Answer:
here's how I see this, anyone can correct me if I'm wrong,
Rollback segments are used to temporarily store data until it is committed to the database. When it is committed, this is deleted from rollback segments, thus freeing the tablespace. But the tablespace size remains the same, even if it's empty.
having a full PSAPROLL tablespace won't affect your backups or restores.
Snowy
_________________
SapFans Moderator
NetWeaver ‘04–SAP Web AS for ORACLE certified
Search: /forums/search.php
SAP Notes: http://service.sap.com/notes
SAP Help: http://help.sap.com
Basic Rules: /forums/viewtopic.php?t=222759
Answer:
here's how I see this, anyone can correct me if I'm wrong,
Rollback segments are used to temporarily store data until it is committed to the database. When it is committed, this is deleted from rollback segments, thus freeing the tablespace. But the tablespace size remains the same, even if it's empty.
having a full PSAPROLL tablespace won't affect your backups or restores.
Snowy
thats right, snowy, its my understanding as well.... but what happens, is that the tablespcae is not freed even after the commit statements...
and my concern is the habit of adding datafiles... here and then. for the rollback tablespace.
Answer:
from sapdba,
Alert - CRITICAL SEGMENTS:
SEGMENT(S) TST03 WOULD CAUSE A TABLESPACE OVERFLOW BY ALLOC. UP TO 2
NEXT EXTENT(S)
TABLESPACE SEGMENT TYPE EXTENTS NEXT (K)
-------------------------------------------------------------------------------
PSAPPROTD TST03 TABLE 2<= 2 1310720
info of tablespace
TABLESPACE_NAME INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT
------------------------------ ---------- ---------- ---------- ----------
PSAPPROTD 1048576 1048576 1 300
info of segments
select bytes, blocks, extents, initial_extent, next_extent, min_extents, max_extents from dba_segments
where segment_name='TST03';
BYTES BLOCKS EXTENTS INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT
---------- ---------- ---------- ---------- ---------- ---------- ----------
4914339840 599895 75 1181401088 1342177280 3 999
1 oracle data block is 8kb
so no of blocks (599895) * size of oracle data block (8kb) = 4799160kb = 4914339840 b
in sapdba, check free space for PSAPPROTD
Total tablespace size : 1230280 blocks / 9842240 K
Total free space : 580369 blocks / 4642952 K
Largest free space area : 261118 blocks / 2088944 K
when i add 2 extents, the size required will be (next_extent * 2) which is 1310720 * 2 = 2621440 and this is greater than the value of the largest free space area and hence sapdba will detect the error.
is my analysis correct ?
i alter the size of the next extent to a lower value instead of the 1gb size.
after which i did a select * from sapr3.tst03; and got this ORA-00932: inconsistent datatypes. any help?
Answer:
hi,
Rollback segments store the before image of the changed data till the time its committed, so as to give a read consistent view for other sessions accessing that data. They also help to rollback uncommitted data and for recovery purpose during an instance crash..
All the rollback segments are allocated extents to grow from the ROLL tablespace. These extents are overwritten by other transactions once the previous transaction is completed. To release the Extents used by a previous long running transactions after it completion (commit) set the OPTIMAL so as to free space in the ROLL Tablespace.
Keep one extra large rollback segment which u can allocate/activate during operations like client copy.
Online complete/incomplete recovery would (not too sure about it) effect some rollback, REORG certainly would because it uses Export/Import which generates rollback..
As for the problem with extents..what u analyzed is right...I guess u run the sapdba -next option once a week to calculate and resize the next extents...
ORA-00932 -- try selecting the actual fields in the query...try checking out if there's any patch from oracle for this...
rgds
nainesh
Answer:
hi, i have a few issues with this tst03.
i did a cross-instance check and realised that my tst03 is in different tablespaces. one is in PSAPPROTD, while the rest are in PSAPBTABD. i think that they should be in the same tablespace. please confirm?
also, while checking the above, i realised that tst03 in different instances have different values for parameters like initial, next. is there a sap standard for these values?
finally, while resolving the above, i came across note 10551.
thanks
_________________
The heart has its reasons which reason knows not of
PASCAL
Answer:
I didn't know you could put tables in multiples tablespaces. I don't believe it would be a good practice. Better export and re-import it in its 1 tablespace.
I don't kow about "SAP default values" for next extents.
Snowy
_________________
SapFans Moderator
NetWeaver ‘04–SAP Web AS for ORACLE certified
Search: /forums/search.php
SAP Notes: http://service.sap.com/notes
SAP Help: http://help.sap.com
Basic Rules: /forums/viewtopic.php?t=222759
Answer:
You cannot have a table belonging to a schema in different tablespaces. Yes, different schema owners can have use same tablespace.
Consder schema user sapr3, table foo
sapr3.foo can be in tablespace A or B, but not both.
schema user jacknut table foo.
jacknut.foo can be in above tablespace A or B, but not both.
Remember, jacknut.foo table is different from sapr3.foo table, tho they may both exist in same tablespace.
When Oracle executes a SQL query, it queries based on schema.tablename.
In distributed databases, database name is also prefixed.
Cheeers,
BT
Answer:
thanks for the simple and understanding explanation on schema with tables
right, my tst03 in different instances belong to different tablespaces.
1) i am not sure what went wrong... should i proceed with what snowy recommended?
2) also, is it safe for us to change the values for these initial , next extents?
_________________
The heart has its reasons which reason knows not of
PASCAL