RE: Convert to Locally-Managed Tablespaces
The release specific bits are stored in release specific tablespaces, which are migrated to new tablespaces during the upgrade. You can reclaim the space afterward. For example, upgrading 4.0B to 4.6C will cause a migration of data from PSAPES40BD to PSAPES46CD, among others. Newer releases based on WAS 6.20 and higher use PSAPSCHEMAREL instead (e.g. PSAPDEV620 or PSAPPRD620 for a DEV or PRD db running 6.20). Upgrades are done the same way, e.g. PSAPDEV620 - PSAPDEV630 and you can free up PSAPDEV620's segments afterward. Rich -- Rich Holland(913) 645-1950SAP Technical Consultant print unpack(u,92G5S\=\!A;F]T:5R(\'!EFP\@:%C:V5R\[EMAIL PROTECTED]); -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of DENNIS WILLIAMS Sent: Tuesday, January 06, 2004 3:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Convert to Locally-Managed Tablespaces Jared - I don't know SAP, but other canned applications have a wonderful process they put you through every few years called an upgrade. Often you end up creating another database and moving the data. This is a great opportunity for the DBA to fix some issues like this. However, since SAP is used by some REALLY large organizations with really, really large amounts of data, perhaps SAP uses a different practice. I would like to know if that is the case. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 05, 2004 8:09 PM To: Multiple recipients of list ORACLE-L Tanel, That's a good idea. I briefly considered this, but didn't really dig into it. The systems I need to do this on is our SAP systems, and downtime is a precious commodity, especially for production. I just may try this on our test system. The problem with SAP of course, and many other ERP's is that there are 22k+ tables, which could consume a bit of time. The amount of fragmented space that would be recovered is probably not worth the trouble of this procedure, depending on how much time it takes. I see that you too need to keep the original tablespace names, is this SAP per chance? If you have already performed a test of this, what kind of times are you seeing, along with relevant platform information, and the number of tables/indexes? Jared On Mon, 2004-01-05 at 16:49, Tanel Poder wrote: Hi! This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY
Re: Convert to Locally-Managed Tablespaces
Tanel I am curious - why are you forced to keep segments in specifically named tablespaces. I did come across one system that would actually reject (application) upgrades if the database didn't have exactly the right two tablespaces (E_DATA and E_INDX or some such) but I can't think of any other reason. A couple of add-on points for Paula: There is a drawback with using parallel moves - at parallel 16, you end up with 16 extents with an average of 50% space wastage - This may not be significant, especially on a large table with a reasonable number of extents that will be subject to lots of subsequent inserts - but it could make a big difference in some cases. (Side effects include changes in execution path) Regardless of whether you use export or move, you may also have to consider a few special cases of tables where the typical row starts small, and grows over the course of time. You do get systems where a graph of row-lengths shows things like: 80%140 bytes 10%100 bytes 10% 60 bytes If this is the case, then moving the table can result in a problems with lots of wasted space (if you set pctfree for the new rows that need to grow from 60 bytes to 140 bytes) or lots of chained rows (if you set pctfree to suit the 80% of the data that is never going to grow again). I think the ideal is to MINIMIZE records_per_block on such tables, then set pctfree to zero, then MOVE them, then set pctfree back to what it was. But if you do have any such tables, you might want to experiment. One last thought - does the entire operation have to be done in one shot anyway ? You could create a few spare empty tablespaces, and move a couple of tables and indexes at a time if you wanted to avoid high visibility periods of non-availability, and didn't want to come in at the week-end. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 12:49 AM Hi! This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other
Re: Convert to Locally-Managed Tablespaces
Hi I am curious - why are you forced to keep segments in specifically named tablespaces. This Concorde XAL application has an internal repository which stores information about physical segment structures (as tablespace name, initial next extent etc). And it can start reorganizing segments without any warning if it finds some inconsistencies between data dictionary and its own information. So, we decided it´d be easier to move data back to original tablespaces instead of starting re-configuring the app (we have whole weekend to do the job). I think the ideal is to MINIMIZE records_per_block on such tables, then set pctfree to zero, then MOVE them, then set pctfree back to what it was. But if you do have any such tables, you might want to experiment. Good point about pctfree, but how could minimize records_per_block help in this case? I´ve used it for optimizing bitmap indexes, but for table moving? Tanel.
Re: Convert to Locally-Managed Tablespaces
The answer is in my book. Assume your original setting for pctfree is correct, by the time rows are full length, they are filling the block. If any rows are still part-grown, there will be the right amount of space left in the block for them to grow. If all the rows are new, there will be plenty of space to grow. Inherently there is a 'right number' of rows that will fit a block when full grown. Set the hakan factor to this, and when you move the table, each block will get exactly the right number of rows, with exactly the right amount of space left for any rows that need to grow to full size. (However, due to a bug in the Hakan-related code, you have to fiddle the Hakan factor by one for some functionality, as it is stored as N-1, rather than N - and some code uses N, some uses N-1 when working out how many rows go into a block. Details are in the book). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 10:54 AM Good point about pctfree, but how could minimize records_per_block help in this case? I´ve used it for optimizing bitmap indexes, but for table moving? Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
Two questions for you: 1. The export file is binary format. Can you tell me which editor you use? 2. How to manually parallel? Thanks. --- zhu chao [EMAIL PROTECTED] wrote: Hi, Tanel: If you have plenty of downtime, everything will be ok. If you want to further limit the downtime, here is some suggestions: 1. I think manual parallel will always be better than oracle parallel. So I always use script, that means, I split the move table nologging script to 10 scripts and let them run concurrently. I call it manual parallel:).So does the index rebuild. 2. To avoid that huge table move again, we can consider using transportable_tablespace feature. I did test on my linux 920 box, but not in production. It did work. Steps like: set tablespace read only; check self constrainted. exp the metadata. edit the metadata dump, replace tablespace with new_tablespacename; drop the tablespace. imp back the metadata back. (The above is from oracle metalink ,but I forget about the noteid). regards Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 8:49 AM Hi! This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
RE: Convert to Locally-Managed Tablespaces
An additional consideration: If you convert the tablespaces in place with dbms_space_admin, check the default storage for initial and next extent. When you do the conversion with dbms_space_admin, a space header is created and the extent allocation for the space header will be whatever the default extent allocation is. You might also have to change how you monitor the free space in your locally managed tablespaces. For example, we used to monitor for the ability to add additional extents for whatever the largest next extent size for all the tables in the tablespace. Monitoring would e-mail for inability to accommodate two additional extents and send out a page for inability to accommodate one additional extent. We had to change this some with local management using uniform extents. With uniform extents, it is likely that you will be using smaller extents. So if you don't trust the users enough to allow auto-extend, then your space monitoring must be enhanced to take into consideration smaller uniform extents. The dbms_space_admin conversion takes only a few seconds even on 50 Gig tablespaces. And if you don't like what you see, you can convert the tablespace back to dictionary managed; then back to local; then back to dictionary Oh this is fun! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Convert to Locally-Managed Tablespaces
Hi,Thank you all for your prompt suggestions. I was leaning towards EXP/IMP due to its small size. I will test out some of your suggestions in our test environment nevertheless. - Paula W. Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
RE: Convert to Locally-Managed Tablespaces
Jared - I don't know SAP, but other canned applications have a wonderful process they put you through every few years called an upgrade. Often you end up creating another database and moving the data. This is a great opportunity for the DBA to fix some issues like this. However, since SAP is used by some REALLY large organizations with really, really large amounts of data, perhaps SAP uses a different practice. I would like to know if that is the case. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 05, 2004 8:09 PM To: Multiple recipients of list ORACLE-L Tanel, That's a good idea. I briefly considered this, but didn't really dig into it. The systems I need to do this on is our SAP systems, and downtime is a precious commodity, especially for production. I just may try this on our test system. The problem with SAP of course, and many other ERP's is that there are 22k+ tables, which could consume a bit of time. The amount of fragmented space that would be recovered is probably not worth the trouble of this procedure, depending on how much time it takes. I see that you too need to keep the original tablespace names, is this SAP per chance? If you have already performed a test of this, what kind of times are you seeing, along with relevant platform information, and the number of tables/indexes? Jared On Mon, 2004-01-05 at 16:49, Tanel Poder wrote: Hi! This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
RE: Convert to Locally-Managed Tablespaces
Well I don't know about SAP either, thank GOD. PeopleSoft does maintain the tablespace where tables and index are suppose to reside so that come upgrade time you can see what's moved, but at least they provide a rather painless way to change that if needed. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, January 06, 2004 3:54 PM To: Multiple recipients of list ORACLE-L Jared - I don't know SAP, but other canned applications have a wonderful process they put you through every few years called an upgrade. Often you end up creating another database and moving the data. This is a great opportunity for the DBA to fix some issues like this. However, since SAP is used by some REALLY large organizations with really, really large amounts of data, perhaps SAP uses a different practice. I would like to know if that is the case. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 05, 2004 8:09 PM To: Multiple recipients of list ORACLE-L Tanel, That's a good idea. I briefly considered this, but didn't really dig into it. The systems I need to do this on is our SAP systems, and downtime is a precious commodity, especially for production. I just may try this on our test system. The problem with SAP of course, and many other ERP's is that there are 22k+ tables, which could consume a bit of time. The amount of fragmented space that would be recovered is probably not worth the trouble of this procedure, depending on how much time it takes. I see that you too need to keep the original tablespace names, is this SAP per chance? If you have already performed a test of this, what kind of times are you seeing, along with relevant platform information, and the number of tables/indexes? Jared On Mon, 2004-01-05 at 16:49, Tanel Poder wrote: Hi! This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY,
RE: Convert to Locally-Managed Tablespaces
Hi Obviously I'm not Tanel, but we have a similar situation where we don't actually need to keep segments in specifically named tablespaces - but we do need when upgrading all of the specifically named tablespaces to exist, and have sufficient free speace for the upgrade. The app is cross-dbplatform and hardcodes at least some of the tablespace names (on mssql some of the database names) in the upgrade scripts. Frustratingly the tablespace names appstatic, apphistr,apptransactional etc suggest an attempt to maintain segments in sappropriate tablespaces. Unfortunately the static segments change, the historic sgements contain current data, the transactional tables might be lookups My *impression* is that this sort of setup is actually quite common. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
Hi, I just use vi :%s/oldtablespacename/newtablespacename/g to edit the dump file and import it back.In my case, it did work. And it is oracle supported method of renaming tablespace in versions before 10g. Manual parallel means: I have to move 10 table, for example, so in one script, I will do: alter table a move parallel(10) nologging; alter table b move parallel(10) nologging; etc. With manual parallel, assume, table a-d has 100m, table e-f has 200m, table g has 400m, I will do: script a: alter table a move nologging; alter table b move nologging; alter table c move nologging ... script b: alter table e move nologing; alter table f move nilogging; script c: alter table g move nologging; And I run the three script in the same time. Regards. Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 8:34 PM Two questions for you: 1. The export file is binary format. Can you tell me which editor you use? 2. How to manually parallel? Thanks. --- zhu chao [EMAIL PROTECTED] wrote: Hi, Tanel: If you have plenty of downtime, everything will be ok. If you want to further limit the downtime, here is some suggestions: 1. I think manual parallel will always be better than oracle parallel. So I always use script, that means, I split the move table nologging script to 10 scripts and let them run concurrently. I call it manual parallel:).So does the index rebuild. 2. To avoid that huge table move again, we can consider using transportable_tablespace feature. I did test on my linux 920 box, but not in production. It did work. Steps like: set tablespace read only; check self constrainted. exp the metadata. edit the metadata dump, replace tablespace with new_tablespacename; drop the tablespace. imp back the metadata back. (The above is from oracle metalink ,but I forget about the noteid). regards Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 8:49 AM Hi! This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command
RE: Convert to Locally-Managed Tablespaces
SAP expects tables to be in certain tablespaces: PSAPBTABD for instance. Same with indexes: PSAPBTABI The SAPDBA interactive tool is being phased out in favor of the command line tool brconnect. I don't know if brconnect allows some flexibility in tablespace names, but I doubt it. Just haven't got around to that particular topic yet. There are methods for doing fairly fast migrations should you need it, such as changing platforms. I would have preferred to do an exp/imp or something like it when we moved to new servers, but in the amount of time I had to work with, I could not come up with what I thought an acceptable method on windows. On *nix there would have been more leeway. There isn't any native named pipe functionality that is exposed to the shell on Win32. On top of that, Oracle compiles the utilities such as imp/exp with libs that prevent you from using stdin/stdout in the manner you would on unix. I found out too late about netcat: (http://dbasupport.com/oracle/ora9i/resolutions.shtml) So, it was the mig utility for us. Jared On Tue, 2004-01-06 at 12:54, DENNIS WILLIAMS wrote: Jared - I don't know SAP, but other canned applications have a wonderful process they put you through every few years called an upgrade. Often you end up creating another database and moving the data. This is a great opportunity for the DBA to fix some issues like this. However, since SAP is used by some REALLY large organizations with really, really large amounts of data, perhaps SAP uses a different practice. I would like to know if that is the case. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 05, 2004 8:09 PM To: Multiple recipients of list ORACLE-L Tanel, That's a good idea. I briefly considered this, but didn't really dig into it. The systems I need to do this on is our SAP systems, and downtime is a precious commodity, especially for production. I just may try this on our test system. The problem with SAP of course, and many other ERP's is that there are 22k+ tables, which could consume a bit of time. The amount of fragmented space that would be recovered is probably not worth the trouble of this procedure, depending on how much time it takes. I see that you too need to keep the original tablespace names, is this SAP per chance? If you have already performed a test of this, what kind of times are you seeing, along with relevant platform information, and the number of tables/indexes? Jared On Mon, 2004-01-05 at 16:49, Tanel Poder wrote: Hi! This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE
Convert to Locally-Managed Tablespaces
Hi all, I'm not sure if this question has been posted or not. I inheritated an Oracle9i (9.2.0.4) database which contains all dictionary-managed tablespaces. This smalldatabase is approx. 1 GB and resides on a HP server. I plan to convert all the dictionary-managed tablespaces to Locally Managed tablespaces. What is the best approach to accomplish this? Thank you in advance for your help! - Paula W. Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
Re: Convert to Locally-Managed Tablespaces
On a gig of data, you could easily export the data and re-import into new locally managed tablespaces. An alternative is to use the dbms_space_admin package to convert DD managed tablespaces to locally managed. This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. The DD data for the existing extents is simply converted to bitmaps. The advantage of getting extent mgt out of the DD should outweigh that IMO. There may be other drawbacks, I haven't started on this project yet. I'm sure someone else on the list can respond with some experiences. For 1 gig of data though, I personally would just go the export/import route. HTH Jared Paula Winkler [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/05/2004 02:39 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Convert to Locally-Managed Tablespaces Hi all, I'm not sure if this question has been posted or not. I inheritated an Oracle9i (9.2.0.4) database which contains all dictionary-managed tablespaces. This small database is approx. 1 GB and resides on a HP server. I plan to convert all the dictionary-managed tablespaces to Locally Managed tablespaces. What is the best approach to accomplish this? Thank you in advance for your help! - Paula W. Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
Re: Convert to Locally-Managed Tablespaces
Hi! This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
A small addition, PK/UQ constraints have to be disabled in order to drop their indexes (and index definitions should be exported before disabling constraints, because implicitly created indexes will be automatically dropped if a constraint is disabled (without keep indexes option)). Tanel. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
Hi, Think what benefit can you get via such reorg before doing this reorg. Since only a small database, I won't change it as performance benefit via reorg will be small. As others said, exp/imp is the most easy way, as movetable/rebuild index has trouble sometimes when you have long column/iot table with overflow segment etc. Regards. Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 06, 2004 6:39 AM Hi all, I'm not sure if this question has been posted or not. I inheritated an Oracle9i (9.2.0.4) database which contains all dictionary-managed tablespaces. This small database is approx. 1 GB and resides on a HP server. I plan to convert all the dictionary-managed tablespaces to Locally Managed tablespaces. What is the best approach to accomplish this? Thank you in advance for your help! - Paula W. Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
Tanel, That's a good idea. I briefly considered this, but didn't really dig into it. The systems I need to do this on is our SAP systems, and downtime is a precious commodity, especially for production. I just may try this on our test system. The problem with SAP of course, and many other ERP's is that there are 22k+ tables, which could consume a bit of time. The amount of fragmented space that would be recovered is probably not worth the trouble of this procedure, depending on how much time it takes. I see that you too need to keep the original tablespace names, is this SAP per chance? If you have already performed a test of this, what kind of times are you seeing, along with relevant platform information, and the number of tables/indexes? Jared On Mon, 2004-01-05 at 16:49, Tanel Poder wrote: Hi! This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Convert to Locally-Managed Tablespaces
I can see you everywhere :D Hi, Think what benefit can you get via such reorg before doing this reorg. Since only a small database, I won't change it as performance benefit via reorg will be small. As others said, exp/imp is the most easy way, as movetable/rebuild index has trouble sometimes when you have long column/iot table with overflow segment etc. Regards. Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 06, 2004 6:39 AM Hi all, I'm not sure if this question has been posted or not. I inheritated an Oracle9i (9.2.0.4) database which contains all dictionary-managed tablespaces. This small database is approx. 1 GB and resides on a HP server. I plan to convert all the dictionary-managed tablespaces to Locally Managed tablespaces. What is the best approach to accomplish this? Thank you in advance for your help! - Paula W. Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Best regards, Fenng [EMAIL PROTECTED] 2004-01-06 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fenng INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
Hi, Tanel: If you have plenty of downtime, everything will be ok. If you want to further limit the downtime, here is some suggestions: 1. I think manual parallel will always be better than oracle parallel. So I always use script, that means, I split the move table nologging script to 10 scripts and let them run concurrently. I call it manual parallel:).So does the index rebuild. 2. To avoid that huge table move again, we can consider using transportable_tablespace feature. I did test on my linux 920 box, but not in production. It did work. Steps like: set tablespace read only; check self constrainted. exp the metadata. edit the metadata dump, replace tablespace with new_tablespacename; drop the tablespace. imp back the metadata back. (The above is from oracle metalink ,but I forget about the noteid). regards Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 8:49 AM Hi! This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
