The size of table TST03 and thus the corresponding tablespace increases continually. Unfavorable block length for table TST03, especially for Oracle as block length
a) Explanation of the Problem
So far, any problems with this tablespace originated from table TST03 which is the database table of component TemSe.
Table TST03 stores spool data. This data is stored in LONG RAW database fields. The size of the LONG RAW field was:
- Up to Release 3.0A: 9998 Bytes plus key fields
- As of Release 3.0B: 8092 Bytes plus key fields
- As of Release 3.1I: as of kernel patch level 218, a length of 7952 bytes is used (see Note 140547). In addition, Release 4.0B is dealt with as of kernel patch level 309 (see Note 140547)
The kernel patches and the information in 140547 correct the problem in accordance with Oracle and chained row processing since the new block length does not require any more chained rows if PCTFREE = 1%.
The spool inserts many database lines in the TST03 and then deletes them again (after printing). This puts a large demand on the storage administration of the database.
Size of TABLESPACE
Size of TABLESPACE ---------------------------.
Size of table TST03 -------------------------. !
---------------------. ! !
data ! ! !
---------------------+ ! !
waste ! ! !
---------------------+ ! !
free in TST03 ! !
-----------------------' !
-----------------------. !
other tables ! !
-----------------------' !
free in tablespace !
------------------------'
When you insert data into table TST03, the space is taken from "free in TST03". If not enough space is available there, ORACLE automatically assigns further extents and takes space from the "free in tablespace" area. If not enough space is available there, the system administrator must use SAPDBA or SQLPLUS old tablespace add datafile '
When you delete data from table TST03, the storage space used for that purpose returns to "free in TST03". Unfortunately, this storage space is sometimes lost in an ORACLE environment. As shown in the above diagram, it is then added to the "waste".
b) The Reason for not Performing the Reuse of TST03 Free Space
The reason for not performing the reuse of TST03 free space may be an incorrect initrans parameter setting of TST03.
Check this using:
```sql select ini_trans,max_trans from dba_tables where table_name = 'TST03'; ```
If ini_trans > 1, set ini_trans = 1 in case of problems.
```sql alter table TST03 initrans 1; ```
This action affects newly allocated blocks in TST03 only, i.e. you may need to carry out reorganization ONCE.
c) Hasty and Inelegant Step
To keep operations running if the tablespace suddenly fills up, you can try to delete the data again with SP01 or RSPO0041. Otherwise, you must create more disk space.
d) Simple Reorganization
Reorganization of tablespace. Here, you combine the initial extent of each table and also all other extents to form a new enlarged initial extent. This does not reduce the overall disk space requirement, but it does break up the "waste" and return it to the "free in TST03". Then, you can work with table TST03 again.
e) Reorganization with Demand for Return of Disk Space
To find out how much space has been used in table TST03, call Transaction SP12 (TemSe maintenance) which calculates the (net) amount of used data. If you add about 10% to this, you can estimate how much data table TST03 actually contains. However, to allow for all unforeseen situations such as unfavorable relationships between between record size and block size, it is preferable to add 90% to the amount obtained with SP12.
By performing a tablespace reorg, you can use the "change storage parameters manually" function to change the size of the initial extents for table TST03 to this size. This means that the released space is not returned to the "free in TST03" area but to the "free in tablespace" area. The system always displays the actual sizes, the target sizes, and the future sizes of the storage specification for a table.
With this tablespace reorg, you can additionally use the "inclusive data files" function to return some of this space to UNIX. However, proceed with caution: If the new (small) size of the tablespace is not sufficient to re-import the data of all the tables involved, the reorg will fail. Then, you have to perform a manual cleanup before restarting the reorg import run. Remember that the whole tablespace is at stake here and not just the spool data.
SQL commands for determining the amount of space assigned to each table:
```sql column SEGMENT_NAME format a20 select SEGMENT_NAME,count(EXTENT_ID),sum(BYTES) from sys.dba_extents where TABLESPACE_NAME = 'PSAPPROTD' group by SEGMENT_NAME ; ```
To determine the amount for TST03 in the R/3 System, proceed as follows:
-> Tools -> Aministration -> Monitoring -> Performace -> Database -> Tables/Indexes (DB02)
- In the 'Tables & Indexes' screen, PRESS 'Detailed analysis'
- Enter table name TST03 and press (Ok)
- Place cursor on TST03, select 'Choose for analysis'
- Select 'Detailed analysis' and press (Ok)
- Space: Allocated space....bytes
Caution: Unfortunately, the number of "free" and "used" bytes specified here is often incorrect.
f) Avoidance Measures
It is generally possible to re-configure the spool so that the data is stored in files in the file system, not in the database in table TST03. The profile value for "rspo/store_location" must be switched from "db" to "G". The following considerations are relevant:
"db" advantages:
- Table TST03 is subject to normal database security.
"db" disadvantages:
- Table space sizes: See this note.
- The database is slower than the file system.
"G" advantages:
- Quicker
"G" disadvantages:
Data is not saved in normal back-ups. Even when they have been separately backed up, it is difficult to re-establish consistency between the database and the files. On the other hand, it is often cheaper to create new spool requests than to restore them from a backup.
In the case of "G," a correspondingly large amount of memory is required for the SAP global directory.