oracle Database reorganization issue fact tablespace
we are currently in process of Re-oraganization of our SAP system(APO) database, we are currently running in oracle 10.2G with latest patch and Brtools 7.0 patch34,
we have taken into account all SAP Note 646681( re-organization with brspace),771191 ( for BI tablesapces) and table- class values during New tablespace creation
our Reorginization is allmost 99% succesfull for all tablespaces expect some indexes (5 partation indexes) still in old tablesapace and tables are in New tablespace, i am not able to understand why ony few indexes still left in old tablespace
partation index are :- 1 /BIC/FZSP_C050
Note :- we are also moving from old database layout to new database layout earlier we had new tablesapce PSAPFACD, and PSAPFACTI now all tables move to New tablespace PSAPAPOFACT, all tables moved to PSAPAPOFACD New and most of the indexes as well expect few as given above not gone to new tablespace....
Eric Brunelle replied
again, BW keeps on droping and creating tables and indexes. so, ehwn it does that, it uses the data dictionnary to accomplish its task.
because you have not changed data dictionnary class, all new objects will be created in the tablespace defined in data dictionary. This is completely normal.
Are you planning on keeping these new tablespace? if you plan on renaming them, check the following:
for all new objects to be created in new tablespace:
update sapr3.taora set TABSPACE='PSAPFACT2D' where TABART='DFACT';
update sapr3.iaora set TABSPACE='PSAPFACT2I' where TABART='DFACT';
create a new type (TABART) in TAORA and IAORA and adapt table DD09L, field TABART for moved tables.
Also consider adapting table RSDCUBE fields CUBEDATCLS, DIMEDATCLS, AGGRDATCLS and ADMIDATCLS.
Also check ODS objects table RSDODSO, fields ODSADATCLS and ODSMDATCLS
also check PSA table RSTS, field PSADATCLS
change partition info for moved table:
alter table sapr3."/BIC/Fzzzazzz" modify default attributes tablespace PSAPFACT2D;
alter table sapr3."/BIC/Eyyyyyyy" modify default attributes tablespace PSAPFACT2D;
select table_name from dba_tables where tablespace_name='PSAPFACTD';
check if tablespace is empty:
select * from dba_segments where tablespace_name= like 'PSAPFACT%';
drop tablespace PSAPFACTD
drop tablesapce PSAPFACTI
brspace -c force -f tsalter -a rename -t PSAPFACT2D -n PSAPFACTD
brspace -c force -f tsalter -a rename -t PSAPFACT2I -n PSAPFACTI
delete from sapr3.iaora where tabspace='PSAPFACT2D';
delete from sapr3.iaora where tabspace='PSAPFACT2I';
delete from sapr3.taora where tabspace='PSAPFACT2I';
delete from sapr3.taora where tabspace='PSAPFACT2D';