Restructure Tablespaces; merge datafiles
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Restructure Tablespaces; merge datafiles

  1. #1
    Join Date
    Apr 2007
    Posts
    20

    Restructure Tablespaces; merge datafiles

    I'm very new to this whole DBA thing, so I apologize if this is a redundant question. I am working on 9i databases.
    We have a couple of databases which need some restructuring. The structure of the tablespaces are fine, but the issue I have is with the unnecessary amount of datafiles within each tablespace (for example we have one tablespace with 8 datafiles totalling 16GB although only 5GB is used in this tablespace...and each datafile is at most 30% utilized). I'd like to combine the datafiles but I am not completely clear on the best process in which to do this.
    From the research I have done, it seems as though I should do a tablespace export, drop the tablespace, recreate the tablespace with the amount of datafiles and the size I am seeking and do an import. This sounds too easy to be honest. I primarily use OEM, and have exported a db through OEM, but I am not sure if doing an export/import using OEM is the right way, or if there are extensive scripts I need to create for this.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    are you sure the datafiles arent spread across devices balancing out i/o

  3. #3
    Join Date
    Apr 2007
    Posts
    20
    No, sorry I should have been more specific. Both these db's are on one Liniux partition. What has happened is a massive cleanup effort and too many datafiles were created from the get-go (I did not create these db's). We'd really prefer to have these down to one or two per tablespace. Not to mention some of these tablespaces will not grow as one of these db's is more like a staging database for our application configuration.

  4. #4
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Another way would be to create a new tablespace with the appropriate datafiles. Then do
    Code:
     Alter table table_name move tablespace new_ts_name
    You'll also need to rebuild your indexes at that point.

    Personally, I like this better since you never delete your data.

    Good Luck!
    Jodie

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    yep, concur with that - safe and easy (and quicker)

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Support to Jodie and Davey, althoug the imp will do as well.
    Just using this method will end up with your objects mooved to a tablespace with a name, different from the initial one, since you will have to create a new tablespace and moove the objects to it. The new tablespace however will have to be with different name, and you do not have a rename tablespace in 9i. I personally do not see any problems with that, but still to mention that difference

  7. #7
    Join Date
    Apr 2007
    Posts
    20
    Thank you everybody.
    I might try out the "alter table" and move it to a temp table space, drop the current, recreate with the correct amount of datafiles and do the "alter table" once again. I need to keep the tablespace(s) with the correct name since we have a great deal of scripts/nightly jobs reliant on this. We would also like to keep the naming consistent with all our databases.
    Does this sound like a sound plan? (Can you tell I'm new at this and have no confidence what-so-ever!!)

  8. #8
    Join Date
    Apr 2007
    Posts
    20
    Another quick question...we have a separate table space dedicated to our indexes, so if I were to do this on both the tablespaces would I still have to rebuild all indexes? Wouldn't the objects essentially be as if they were not touched?

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    alter table move will invalidate your indexes, you have to rebuild them
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Apr 2007
    Posts
    20
    OK, I tried. )
    I will rebuild them.
    Again, thank you everyone for your help!

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