relocating tables and indexes and S.A.F.E. Script anyone?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: relocating tables and indexes and S.A.F.E. Script anyone?

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

    relocating tables and indexes and S.A.F.E. Script/ideas anyone?

    Hello

    I'm planning to relocate all tables and indexes to new tablespaces. I am doin' so because of the S.A.F.E. rules.

    The only problems is: there are many tables and indexes and relocating them by hand is a lot of work.

    My question:
    Does anyone have a script/tool that intelligently chooses the right tablespace for an object? If the result is a 'alter table/index rebuild online tablespace "x"; ' script, then it would be perfect.

    If there isn't a script, can someone explain me how I can determine the right TS?

    I know that the newer versions of TOAD can generate such a script, but this is a creation script, not a relocate script.

    Thanks in advance,
    Erik
    Last edited by efrijters; 09-02-2003 at 06:55 AM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  2. #2
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    I think a tool called spacetuner will do what you are after, you can use it on a 30 day trail.

    btw - whats S.A.F.E?

  3. #3
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    S.A.F.E. means: "Simple Algorithm for Fragmentation Elimination".

    It's fully explained in the Oracle doc. "How to stop defragmenting and start living: the definitve word on fragmentation" and it's available on metalink. If you want the document, I can email you it.

    Thanks for the tip for "Spacetuner". I will look into it.

    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
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91
    Can't you just create one localy managed tablespace with uniform extent size and move all your objects in it?

  5. #5
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Yes offcourse, but the SAFE-document states that it's better to create a small,medium,large and huge ts, so you can categorize your objects in the correct ts. I thought this also counts for indexes.

    In this way, you can keep the number of extents low.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Segments would be categorized by their size, then, like ...
    Code:
    Select
       segment_name,
       Case When Bytes/1024/1024 < 1 Then 'TS_SML'
            When Bytes/1024/1024 < 64 Then 'TS_MED'
       Else 'TS_BIG'
       End TS
    From user_segments
    /
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Thanks slimdave!

    Hmmm... if thought it would be harder to determine this. (shame on me...!)

    A question for slimdave: how many years of experience do you have in Oracle? I'm just starting and I was just wondering how long it would take (roughly) to become a senior DBA...
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Ah, a chance to proselytize.

    I've been using Oracle for about 10 years now. However, the important thing with having 10 years of experience is that it must not be "one year of exerience ten times", if you know what i mean. I've met DBA's with 15 years of experience who have no clue about any features of Oracle introduced after 7.1.4, and who are barely able to create a table (seriously).

    So read every scrap of information you can get your hands on, get practiced at testing every assumption with little test cases, and be prepared to admit when you're wrong (technical people seem to come with a built-in professional ego that makes it difficult to accept that someone else is right, and the ability to sulk for hours when they don't get their way).

    End of proselytationalizationing.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Originally posted by slimdave

    Ah, a chance to proselytize.

    .....

    End of proselytationalizationing.
    And you have 10 years of experience in 'scrabble', don't you?
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by efrijters
    And you have 10 years of experience in 'scrabble', don't you?
    never been beaten in the absence of a dictionary.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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