Q: looking for a best practices on a reorg issue

[vince_laurent]

I am trying to figure out how to reorg our BW PSAPODSD and PSAPODSI tablespaces. They both have partitions which seems to complicate the issue a tad. What I am trying to do is move the data to new tablespaces (PSAPTESTD and PSAPTESTI, drop the old ODS tablespaces and datafiles, recreate them as LTMS, then move from TEST back to ODS.

I've looked at using the oracle exp/import tools. I've looked into brtools/brspace tools. I am not sure which way to go that (a) will get all the data, (b) get/recreate all the bazillion partitions, and (c) recreate/rebuild all the indexes and the partitoned indexes.

I've already done an exp then an imp (to create the SQL) but do I need to do more than just go into the SQL and change the target tablespaces?

Does anyone have any method/scripts they can share?

Thanks in advance

Vince

[Former Member · Accepted solution]

Vincent,

Check out this forum thread:

http://www.sapfans.com/forums/viewtopic.php?t=255674&highlight=reorg

[vince_laurent · Accepted solution]

Thanks for the pointer but it seems to be asking the same questions I am. The only PDF that I could get was the one from quest. Yours from LuLu I kept getting DB errors on the LuLu site.

Also, 3rd party options are currently out of the question. I have done reorgs on R3 systems but since they don't have partitions and sub partitions and such there didn't seem to be as much to worry about.

I don't even want to mess with any BW R3 internal tables and that is why I would like to go from ODSD->Temp->ODSD. I can get the downtime to do it too.

Thanks for your help though the issue still sin't solved. And I'd like to see your book sometime.

Vince

[Former Member]

What I have done in the past is to first move all the large ODS objects into their own individual tablespaces. Then using sapdba I would reorganize the PSAPODSD and PSAPODSI tablespaces using the recreate tablespaces and datafiles option. SAPDBA had no problem with partitions. Ideally, your PSA should follow good data administration practices. Make sure that you don't have old master data PSA entries and change logs. I run jobs every Sunday to delete older entries. I check in DB02 every couple of months for large objects and make sure that they have a corresponding PSA deletion jobs. If you keep the ODS trim, reorganization should go smoothly. When I first started with my current company, the ODS was almost 500 Gb (with less than 20% free space) and growing rapidly. It's now less than 150 Gb (40 free space) and the growth is negligible.

[Former Member]

Hello Vincent,

Why don't you follow the note 646681?

It seems to provide the instructions for what you want to do.

I'm not aware of problems with partitions

[vince_laurent]

That was the first note I looked at. 11+ steps to reorg seemed a bit excessive but if that is what it takes. I also have tried going through brtools interface but can't seem to figure out the sequence of commands to get what I need.

Thanks for the reply though...

Vince

[vince_laurent]

After reading and re-reading 646681 I still have a few questions.

How do you deal with LOBs and LOBSEGMENTs? How do you know if a table has a long when you want to export and extire TABLESPACE short of looking through the THOUSANDS of tables SAP puts in one tablespace? How can I do an OFFLINE reorg (note only covers ONLINE)?

Thanks in advance for any help...

vince