SQL error -42008 during reorg with BRTOOLS

[Former Member]

Dears,

we just upgraded successfully to EHP6 a test system, and at the end we noticed the tsp *702 are not empty, some table it's still inside.

For that reason we cannot drop them as usual.

We read note 1715052 ad as suggested we try to perform a reorganization to move them to the new tablespaces.

But despite we are using the latest DBATOOLS 720 (34) we recive the error:

BR0301E SQL error -42008 at location tab_onl_reorg-39, SQL statement:
'BEGIN DBMS_REDEFINITION.START_REDEF_TABLE (UNAME => '"SAPR3"', ORIG_TABLE => '"TTREET"', INT_TABLE => '"TTREET#$"', OPTIONS_FLAG => DBMS_REDEFINITION.CONS_US                E_PK); END;'
ORA-42008: error occurred while instantiating the redefinition
ORA-01405: fetched column value is NULL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 56
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1490
ORA-06512: at line 1

We found  note 1290097, but does not work. The error still occurr.

Any advise ?

Best Regards

[Reagan]

brspace -c force -o process,time -u / -f tbreorg -a reorg -tablespace PSAPSR3702 -t "*" -parallel 3 -degree 3 -newts PSAPSR3731

Good Luck

RB

[Former Member]

Maybe your problem are tables which haven't been handled by ICNV, see https://service.sap.com/sap/support/notes/674070 for further details. The note has a report which identifies tables which are in wrong tablespace.

[Former Member]

Hello,

we have had the same problem  with upgrade from EHP4 to EHP6.

select segment_name , segment_type from dba_segments where tablespace_name='PSAPSID701';

for the list of table that are in the wrong tablespace.

we have moved the table with the command

brspace -p
initSID.sap -s 20 -l E -f tbreorg -a reorg -mode online -s PSAPSID701 -o SAPSID
-t TTREE -n PSAPSID731

for each table, therefore, indexes, follow the tables.

And after that i was able to delete the tablespace with

brspace -f tsdrop -t PSAPSID701

I hope to be helpful

regards

Antonietta

[stefan_koehler]

Hi Roberto,

is this error caused by underlying PL/SQL procedure dbms_snapshot_utl.get_log_name? If yes then you are hitting Oracle bug #13767921.

However you can crosscheck the root cause of this error by setting the following event and executing DBMS_REDEFINITION manually.

SQL> ALTER SESSION SET events '1405 trace name errorstack forever, level 4';

Regards

Stefan

[Former Member]

Hi,

can you please use the latest Brtool for 720.

As we have see some issue related to this .

Note 1844380 - Minor functional enhancements in BR*Tools (5)

Thanks

Rishi abrol

[Former Member]

Hi,

It looks that you are on the latest version of brtools....

so note will note help.

Thanks

Rishi Abrol

[Former Member]

Hi,

Why don't you use the below command.

firest used the beloos commnad to move the tables.

1. alter table <initial table space>.TTREE move tablespace <target table space name>;

Then rebuild the index.

1. alter index <initial table space>."TTREE^0" rebuild tablespace <target table space name>;

2. alter index <initial table space>."TTREE^I01" rebuild tablespace <target table space name>;

3. alter index <initial table space>."TTREE^TYP" rebuild tablespace <target table space name>;

Just an example.

Thanks

Rishi Abrol