I am in need of some help, I inherited a rather messy oracle database. some of the tablespaces have many many small datafiles. Could someone outline a procedure to consolidate these many small files into one large file, without taking the tablspace or the db offline? is this even possible.
06-07-2007, 06:31 AM
Was there any reason why your predessessor created lots of small datafiles? You might want to find that out before you do anything.
Anyway, why don't you create new tablespace with larger datafile and move all the objects. Do you have any downtime?
06-07-2007, 04:27 PM
The simple answer, they did not know what they were doing, once they created a smal file and every time they ran out of space, they just created another one of the same size, go figure???
No, I do not have the luxury of any downtime, I have had a consultant reccomend to me that this is an easy thing to do while the DB is up so I thought I would ask.
I was planning on doing this consolidation as part of a migration to new hardware and oracle 10g, currently running 9i. My plan was to build a new db and the tablepaces, import the rows and tables, apply the edited indexfile then be up and running, the consultant I think spooked my already juttery boss and recommended copying the db over and running the convert script for 10g, he said it won't take but an hour or two, I have my doubts, but that would leave me the eventual task of cleaning up the messy little files.