tablespace reorganization
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: tablespace reorganization

  1. #1
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317

    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.

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    * 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"

  3. #3
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    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.

  5. #5
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    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.

  6. #6
    Join Date
    Jan 2001
    Posts
    3,131
    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.

  7. #7
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Thanks Mr.Hanky!
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095

    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    You can do it in 8.1.6 - presume it came in at 8i

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width