Export/Import table

[Former Member]

Hi,

Oracle-10.2.0.4.0

SAP-R/3 Release 4.6C

SID-DEV

I have to export/import same table TST03 in same system due to block corruption issue.

Checked and found process for the same

1. Export the table

2. Truncate the table

3. Import the table

Please suggest me if there is any other better way to perform this task online with minimal impact.

[Former Member]

Thanks

[Former Member]

If you have not committed to a resolution yet, you should really determine if the table is corrupt or if it is just the index. If it is the table, then the export is going to fail as it reads the corrupt block. If it is an index corruption then drop and recreate.

This will force a full tablescan on the same record that causes you to get the Oracle error. If you DO NOT get an error from this query then you most likey have a CORRUPT INDEX (not a table).

select /*+full (a) parallel(a,5) */ DCONTENT from SAPR3.TST03 a where DNAME='SPOOL0000021915'

You can also confirm this with just a complete full tablescan:

select /*+full (a) parallel(a,5) / count() from SAPR3.TST03;

Your other query that DID get the error may have been doing an INDEX SKIP SCAN on the index TST03~0. Which may give more evidence for index corruption not table corruption.

select DCONTENT from SAPR3.TST03 a where DNAME='SPOOL0000021915'

Follow SAP Note 1559652 as well. If this is an index corruption, this saves you time from having to go through all the additional work of export and import. You also might have the BLOCK ID in the tracefiles under the saptrace/usertrace directory which you could dump to determine if it is a table or index block. If this IS a table corruption issue you have bigger problems.

Good Luck,

Mike Kennedy

[Former Member]

TST03 have long field

[mahesh_phegade1]

Hi,

In that case you have to do the offline reorg.

A test case of TST03 table offline reorganization is mentioned here.

General steps :

u2022 Take database backup

u2022 Shutdown SAP instance

u2022 Create ddl statements for tables,index and dependencies.

u2022 Export Table

u2022 Rename table and all indexes

u2022 Create table and indexes from ddl statements

u2022 Import table contents

u2022 Starts SAP instance and check

u2022 Drop old renamed table.

SAP Note 646681 - Reorganizing tables with BRSPACE.pdf

This can be helpful

Regards,

Mahesh Phegade

[Former Member]

Could we do this also

1. Export the table

2. Truncate the table

3. Import the table

[Former Member]

Could we do this also
> 
> 
> 1. Export the table
> 2. Truncate the table
> 3. Import the table

Yes, this is also possible. Nevertheless I wouldn't leave out as preparation to perform a backup and shutdown SAP.

If you are lucky then only an index is corrupt and you won't see the ORA-00600 message during the export.

[Former Member]

Also for worst case scenario ... ensure you have a backup of the system before the block corruption occurred.

The block corruption might cause problems with any of the steps mentioned above as resoution.

Regards,

Karan Shah

[markus_doehr2]

> I have to export/import same table TST03 in same system due to block corruption issue.

> Please suggest me if there is any other better way to perform this task online with minimal impact.

If a block is corrupt an export may fail due to that exact block corruption.

I would stops the SAP system when doing this and also make sure that you have a backup.

Markus

[Former Member]

I will have complete backup of system before performing this activity. I can take down time for server also.

Is there any other way to perform this task.

SQL> select DCONTENT from SAPR3.TST03 where DNAME='SPOOL0000021915';

ERROR:

ORA-00600: internal error code, arguments: [8003], [], [], [], [], [], [], []

no rows selected

SQL> select DCONTENT from SAPR3.TST03 where DNAME='SPOOL0000025088';

D

-

F

7

SQL>

Main problem is with this record only-SPOOL0000021915...All other records in table TST03 is fine

[mahesh_phegade1]

Hi,

If table TST03 doesn't have any long row field then try to reorg this table online through brtools & see the results for affected spool number.

Regards,

Mahesh Phegade