cancel
Showing results for 
Search instead for 
Did you mean: 

SAP BW table re-org

Former Member
0 Kudos

Dear All,

When I re-org the table RSBERRORLOG is 1.3 TB size.

It showing the below error.

BR0280I BRSPACE time stamp: 2013-07-21 12.36.21

BR0301E SQL error -12008 at location tab_onl_reorg-39, SQL statement:

'BEGIN DBMS_REDEFINITION.START_REDEF_TABLE (UNAME => '"SAPSR3"', ORIG_TABLE => '"RSBERRORLOG"', INT_TABLE => '"RSBERRORLOG#$"', , OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK); END;'

ORA-12008: error in materialized view refresh path

ORA-01653: unable to extend table SAPSR3.RSBERRORLOG#$ by 8192 in tablespace PSAPSR3

ORA-06512: at "SYS.DBMS_REDEFINITION", line 56

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1490

ORA-06512: at line 1

BR0280I BRSPACE time stamp: 2013-07-21 12.36.24

  BR1106E Reorganization of table SAPSR3.RSBERRORLOG failed

Thanks

Manas

Accepted Solutions (1)

Accepted Solutions (1)

former_member204746
Active Contributor
0 Kudos

Hi Manas,

this table should have never been as big.

instead of reorg, I would ask my BW team if they really need active data in this table. If only contains error logs, no important data is in this table. If So, simply truncate this table in SQLPLUS. This is gonna take a few seconds to a few minutes and will resolve your issues. Do this at a quiet time when BW is not used that much to avoid issues.

Next, search SAP notes about this well-known table and run proper programs to delete old stuff from it. it should have never been to this size!

Former Member
0 Kudos

Hi Eric,

Thanks for your reply.

I know it contains error logs but i can not delete all the logs.

I already  deleted logs except 3 months logs .

Thanks,

Manas

Answers (4)

Answers (4)

Reagan
Advisor
Advisor
0 Kudos

Hello

If you don't want to delete all the data (truncate) in the table then you may use the report RSBM_ERRORLOG_DELETE to delete the error logs from this table for a specific period of time.

Read these SAP Notes:

Note 706478 - Preventing Basis tables from increasing considerably

Note 1095924 - Correction: Deletion/analyis report for error handling logs

Note 1144400 - RSBM_ERRORLOG_DELETE no frequent commits

Regards

RB

Former Member
0 Kudos

Hi RB,

I execute the report for delete logs for a specific period of time but

not reduce the size of the RSBERRORLOG table.

How I reduce the size?

Thanks,

Manas

former_member204746
Active Contributor
0 Kudos

as stated earlier, 1.3TB for this logging table is way too much.

truncate this table and go buy a beer!

Reagan
Advisor
Advisor
0 Kudos

Hello

Deleting the contents of a table wont reclaim the space used by the table.

The table will still have the original size due to the high water mark and deleting the contents wont change it.

Either you can perform an export and import of the table if you want to have the data what is present in the table or like Eric mentioned just truncate the table.

Regards

RB

Former Member
0 Kudos

Hi all,

After truncate table , I update database statistics but db02 show the table size.

I oracle sql level view it shows 0 size.

Please help how db02 get updated data.

Thanks,

Manas

Former Member
0 Kudos

run db check. or db02old > refresh

close (/n) screen and check again with db02.

Reagan
Advisor
Advisor
0 Kudos

Hello

When I re-org the table RSBERRORLOG is 1.3 TB size.

For sure this is a wrong strategy. Instead you should perform a table export and import as the size of the table is 1.3 TB

When you do an online table reorganization the tablespace will need the space to accommodate the second copy of the table.

Read this:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70cf010d-0b01-2d10-86a2-a0636c39a...

Here is what the article says:

Temporarily twice the space is needed because both source and target table and indexes exist in parallel.

There is no point in extending the file tablespace with additional datafiles.

Regards

RB

Former Member
0 Kudos

Hi RB,

Thanks for your reply.

If I use export & import method what is the command please share.

Thanks,

Manas

Reagan
Advisor
Advisor
0 Kudos

Hello

First you need to read the below SAP Notes I have pointed out.

There is a report you can run to delete the data older than NN days.

Once you have done that then you can do a table export and import.

To do export and import of tables you need to switch to orasid and call brtools and select Segment management. There you will have the export and import of the tables option

You can specify the table name and select the expdp tool as the export utility and also specify a separate location for the export dump (Export dump directory)

Regards

RB

former_member206552
Active Contributor
0 Kudos

Hi Manas,

As suggested by Deepak, you will need to add space to the tablespace inorder to complete the request,

you can use the following query to determine how much space you require

SELECT * FROM

(SELECT

    SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,

       ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *

      (1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,

    ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE / 100) -

      (AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"

  FROM DBA_TABLES

  WHERE TABLE_NAME='RSBERRORLOG')

WHERE ROWNUM <=1;

Best Regards

Marius

Former Member
0 Kudos

Hi   ,

Thanks for your reply.

After execute the above command I can added how many space in the table space PSAPSR3

"WASTED_MB" space or which one.

you can tell me how long time required to re-org the table.

Thanks,

Manas

former_member206552
Active Contributor
0 Kudos

Hi Manas,

The WASTED_MB is the space you will roughly get back. the GROSS_MB is the space you roughly need for the reorg of the table.

If you use brtools and use 4 processes then it roughly take 30 gig table 30 min including indexes.

But if the table is 1.3T then it will be a better option to do an offline re-org as suggested by Orkun.

Best Regards

Marius

Former Member
0 Kudos

Hi Marius,

Thanks for your reply.

Just I confirm one thing GROSS_MB is required free space in PSAPSR3 ?

WASTED_MB which purpose is used.

I use brtools 7.20 how i start 4 processes in re-org.

It is not possible for re-org an offline we not get downtime.

Thanks,

Manas

former_member206552
Active Contributor
0 Kudos

Hi Manas,

Yes. The WASTED_MB is the space you will be able to relaim , the GROSS_MB is the current used space in that table.

But as stated earlier i would rather do an export import of the table as it is very big. Eric also had good advice that if you can truncate the table that would save you a lot of time and effort ?

Here is the command execute as ORASID

brspace -c force -p initSID.sap -s 20 -l E -f tbreorg -o sapsr3 -e 4 -t "RSBERRORLOG"

Best Regards

Marius

Former Member
0 Kudos

Hi Marius,

I already deleted all the data except 2 month.

If i export the data and then truncate the table, it is better.

please share the command for export & truncate table.

thanks

Manas

former_member206552
Active Contributor
0 Kudos

Hi Manas,

for the basic export command you can use the following

exp sapsr3/password file=RSBERRORLOG.dmp log=RSBERRORLOG_exp.log tables=RSBERRORLOG consistent=y

after you have exported the tables you can either drop the table or you can truncate it.

for impport you can run the following if you drop the table

imp sapsr3/password file=RSBERRORLOG.dmp log=RSBERRORLOG_imp.log full=y

for impport you can run the following if you truncated the table

imp sapsr3/password file=RSBERRORLOG.dmp log=RSBERRORLOG_imp.log full=y ignore=y

reason for the IGNORE=Y flag is to bypass the error that state that the table allready excists and exits

If you would like to use export datapump utility please use the following thread

http://scn.sap.com/thread/3359085

Best Regards

Marius

former_member188883
Active Contributor
0 Kudos

Hi Manas,

As per error message

ORA-12008: error in materialized view refresh path

ORA-01653: unable to extend table SAPSR3.RSBERRORLOG#$ by 8192 in tablespace PSAPSR3

Tablespace PSAPSR3 looks to be full. Please add atleast 10GB in PSAPSR3 and repeat the process.

Hope this helps.

Regards,

Deepak Kori

Former Member
0 Kudos

Or perform offline reorganization...

Best regards,

Orkun Gedik

Former Member
0 Kudos

Hi Deepak or Orkun,

Thanks for your reply.

You tell that add 10 gb space but table size is 1.3 GB and tablespace free size is 240 GB.

It is possiable for re-org

Thanks,

Manas