-
tablespace reorganization
Hi,
I've been reading some stuff on ts reorganization on this site.
I want to reorganize my ts, because it's DDM I want to migrate it to LMT. But, because it's a production-db I'm a bit afraid of the implications.
What I want to do is:
- create new LMT-ts (small, medium, large and huge);
- 'alter table x move tablespace y online' all the tables to the correct ts;
- 'alter index rebuild' all the indexes;
- drop the now empty old ts.
Question : when moving tables and other objects, the objects get locked I suppose. Does this mean I have to do all this at night? (It's a 24x7 db). Is this possible with scripts? (if so, I would really like to see an example because I don't want to risk my life... I mean job on this)
PS: it's an Oracle 8.1.7-db
Thanks in advance!
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
-
* During a table re-org, DML will not be supported online.
* Be care-ful while moving HUGE tabs.
Scripts you have to do by urself
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Thanks, but I wasn't asking for a complete "download and run" script. I was only asking if you guys do the reorg-action at night and if you automate this via scripts. Because, when a script fails at some point, you can have a big problem. And you'll only find out when you arrive at work. Reorganization will take a long time and availability is important.
The only issue here is that I'm a bit worried about altering the most important ts at this company. (The extent size is totally wrong, so I want to improve that) But what I don't need is a call from the client that the users are complaining about a crashing application because of my reorg.-action.
I hope the clarifies my thread...
Erik
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
-
If its a large re-org then you need to plan it carefully - you will need an outage. Again if its a large re-org the you'll need to script it up (maybe login and check every so often if running overnight) - but test, test and test it to death if you can, to iron out any niggles (they're always a few gotchas). You'll need buy in from management, users etc - treat it as a major project. We've done a number of these with critical DB's and they've gone pretty smoothly, but only after careful planning and testing.
-
That's more like it... Thank you fraze !
Any other suggestions / tips?
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
-
HAVE A GOOD TESTED BACKUP!!
I know this is a no-brainer but it can not be overemphasized.
You also have the benefit of time; you do not have to do all of them in one shot. Take your time, do the small ones first, this way if there are any “gotchas” and you need to back out (recover) the recovery will be quicker. You will also have gained some experience before you move on to the larger tablespaces.
MH
I remember when this place was cool.
-
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
-
Re: tablespace reorganization
Originally posted by efrijters
- 'alter table x move tablespace y online' all the tables to the correct ts;
I don't think online table moves are available in 8i. Maybe I'm wrong.
In addition, why are you re-organizing? If it is to save space, you need to futz with your storage clause else your new segment will be the same size as the old segment.
Jeff Hunter
-
You can do it in 8.1.6 - presume it came in at 8i
-
Really? Maybe I got something configured wrong. Could you explain the steps to enable this option?
Code:
SQL> alter table xyz move online;
alter table xyz move online
*
ERROR at line 1:
ORA-25148: ONLINE option not permitted
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|