Within the SAP Netweaver 7.0 system is a complete e-mail tool that allows users to send/receive electronic documents.
These documents are visible in the SAP Business Workplace (TX: SBWP) inbox/outbox and also in the SAP Connect transmission requests (TX: SOST).
As time goes by and users accumulate documents in their inbox and internal/external mails get sent through SAPConnect, space in the database will be used.
SAP Note: 966854 recommends running RSBCS_REORG to clear down these documents and free the space.
Unfortunately it doesn’t tell you how much space is taken up and how much you could get back.
Using the details in SAP Note: 706478 it is possible to check some of the tables at the database level.
Once again, the note doesn’t list all tables involved.
Instead, running the RSBCS_REORG report in “Test” mode reveals the full extent of the tables where records will be deleted:
So using this information, the following constructed SQL query will return the current size of the database segments (Oracle) for the selected tables (FOR ECC 6.0 based systems):
SELECT *
FROM (
select sum(s.bytes)/1024/1024 size_mb ,
s.segment_name,
s.segment_type,
s.segment_name table_name,
t.ddtext descr
from dba_segments s,
sapsr3.dd02t t
where s.segment_name in ('BCST_BOR',
'BCST_CAM',
'BCST_RE',
'BCST_SR',
'SOC3',
'SOCS',
'SOCX',
'SOER',
'SOES',
'SOFM',
'SOOD',
'SOOS',
'SOST')
and s.segment_name = t.tabname
and ddlanguage='E'
group by s.segment_name, s.segment_type, s.segment_name, t.ddtext
union all
select sum(s.bytes)/1024/1024 size_mb ,
s.segment_name,
s.segment_type,
i.table_name,
t.ddtext descr
from dba_segments s,
sapsr3.dd02t t,
dba_indexes i
where i.table_name in (
'BCST_BOR',
'BCST_CAM',
'BCST_RE',
'BCST_SR',
'SOC3',
'SOCS',
'SOCX',
'SOER',
'SOES',
'SOFM',
'SOOD',
'SOOS',
'SOST')
and i.table_name = t.tabname
and i.index_name = s.segment_name
and ddlanguage='E'
group by s.segment_name, s.segment_type, i.table_name, t.ddtext
) t1
ORDER BY t1.table_name ASC, t1.segment_type DESC;
For R/3 4.7 you can use the query below (removes the table and index descriptions):
SELECT *
FROM (
select sum(s.bytes)/1024/1024 size_mb ,
s.segment_name,
s.segment_type,
s.segment_name table_name
from dba_segments s
where s.segment_name in ('BCST_BOR',
'BCST_CAM',
'BCST_RE',
'BCST_SR',
'SOC3',
'SOCS',
'SOCX',
'SOER',
'SOES',
'SOFM',
'SOOD',
'SOOS',
'SOST')
group by s.segment_name, s.segment_type, s.segment_name
union all
select sum(s.bytes)/1024/1024 size_mb ,
s.segment_name,
s.segment_type,
i.table_name
from dba_segments s,
dba_indexes i
where i.table_name in (
'BCST_BOR',
'BCST_CAM',
'BCST_RE',
'BCST_SR',
'SOC3',
'SOCS',
'SOCX',
'SOER',
'SOES',
'SOFM',
'SOOD',
'SOOS',
'SOST')
and i.index_name = s.segment_name
group by s.segment_name, s.segment_type, i.table_name
) t1
ORDER BY t1.table_name ASC, t1.segment_type DESC;
You may just be able to see that the SOC3 table is using ~7GB of space with an index of 144MB and a lob object of 64KB.
Now if you run the RSBCS_REORG report using “Test” mode, with the settings to remove the documents that are >60 days old, then you can estimate what percentage of records will be removed from table SOC3 and therefore estimate the space saving.
As a rule of thumb, it may be wise to remove the documents from users who have left the company.
Generally the SAP account will be locked, so you can pull the account names using the following SQL query then add them into the RSBCS_REORG report “User” field:
SELECT DISTINCT bname FROM sapsr3.usr02 WHERE uflag =128;
NOTE: When running RSBCS_REORG, it will not remove assigned workflows from the “Workflow” folder (or sub-folders).
You may also consider running the report for the user who runs the SAPConnect background job step. As this user will have the majority of occupied space.
Once you’ve deleted the records that are not required, the space in the database tables will be freed.
However, this will not release the space to the rest of the database, only the tables from where the records were deleted.
Stay tuned for my up-coming post on how to free the table segment space after you’ve removed thousands of records from the Oracle database.
