on 07-25-2013 8:39 AM
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
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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:
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Marius Burger,
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
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
80 | |
24 | |
11 | |
9 | |
7 | |
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.