| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Space Manager vs. Live ReOrg vs TS ReOrg
We use LiveReorg here and have had a few problems. When it works, it works very well and when it breaks it can hose your database. It does invalidate some objects and you will need to manually recompile them if you don't tell it to do it for you. Since some objects are invalidated because of dependencies you may have to recompile some anyway. Obviously any that depend on a private database link cannot be compile by LiveReorg.
Now, on to the problems. Even though the advertised special is that you can run it in the middle of the day with thousands of users logged on with no interruption it is not completely true. We have given up and only use it after-hours, even in LiveReorg mode. The problem is that it can sometimes fail when it can't exclusively lock everything that is associated with one table. So if you have foreign keys referencing the table and the table has foreigns keys into other tables you will have problems if somebody has a table lock. Sometimes I think DDL locks cause problems too.
When the script blows up it will leave triggers out there that let everyone know that a LiveReorg is "in progress" so you will start getting phone calls right away. Believe me, the first time it happened to me I think I actually lost five pounds from sweating the entire two hours it to me to straighten the problems out. Eventually I was able to drop the triggers and create the missing grants from the LiveReorg script I had saved.
My advice is to be very careful when evaluating the effects of LiveReorg. I moved 300+ tables from one tablespace to another on Q/A and everything went fine because there were very few users logged in. We have 25 or so more tables to go in prodution and I wrote all of the DDL myself just because I have lost faith in LiveReorg. We have another DBA that has used it in Oracle Applications with very much the same problems.
Good luck.
--Michael
-----Original Message-----
Sent: Friday, June 15, 2001 1:33 PM
To: Multiple recipients of list ORACLE-L
Space Manager is an "offline" tool -- It creates a script that will create a new table, populate it, and replace the old table with it, and during this time the table is not accessible.
Live Reorg only has a "very short" window where the table is locked -- Quest says a few seconds -- while the table is reorg'd. And, while I haven't used it, I'm guessing that Live Reorg will not invalidate objects (like triggers) that have a dependency on the table being reorg'd, unlike Space Manager's brute force method.
Space Manager 3 is OK. v4 should be much nicer once it's out of beta (read: more stable). There are enough major bugs in v4 (easy to reorg a 500K index to a 500M index) that I'm going to wait for an "official" version. :)
Bottom Line:
Space Manager: Cheap, but needs downtime.
Live Reorg: Expensive, but no real downtime.
TS Reorg: Now owned by CA, so I probably won't even look at it. :)
Good luck!
Rich Jesse System/Database Administrator
Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
-----Original Message-----
Sent: Friday, June 15, 2001 12:11
To: Multiple recipients of list ORACLE-L
Sun Sparc Solaris 2.6/8
Oracle DB 8.0.6, 8.0.5
Oracle Apps 11.0.3
My client and I are currently planning on re-orging some of our database - say about 90G out of a 120G database (2 schemas). We've identified 3 tools and are seeking feedback on the pros/cons of each option.
....
anyone?
Also, can anyone explain the difference between Space Manager and Live ReOrg? Both are marketed by Quest, making me wonder what, if anything, is the difference...
TIA
J
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (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). Received on Fri Jun 15 2001 - 12:07:59 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |