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.
are you sure the datafiles arent spread across devices balancing out i/o
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.
Another way would be to create a new tablespace with the appropriate datafiles. Then do
You'll also need to rebuild your indexes at that point.
Alter table table_name move tablespace new_ts_name
Personally, I like this better since you never delete your data.
yep, concur with that - safe and easy (and quicker)
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
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!!)
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?
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.
OK, I tried. )
I will rebuild them.
Again, thank you everyone for your help!
Click Here to Expand Forum to Full Width