This content has been marked as final. Show 7 replies
IMHO a only big file is an error
how can you backup it ?
if you have many files, you could put one by one in backup mode, but how to do it in only one file ?
Are you using ASM?
You can use one tablespace, but is not a good idea because you complicate your administration process. Divide the Data in organized tablespaces like: HR_DATA, HR_INDEX,FIN_DATA, FIN_INDEX...etc, will help you with the admininstration of the Data.
What is better (thinking in all your personal properties in your house):
Have all your stuff in a big container? or have all organized in small ones, that when you need to find something you know where to find it, and because is small it's easy to find it?
Francisco Munoz Alvarez
How is the database being backed up? If you're using RMAN that means only 1 channel can backup the datafile at a time as opposed to the datafile being split up in which case RMAN could backup the datafiles using multiple channels (if you're using Enterprise Edition) meaning backup time will be less (in theory).
Whosoever designed this database, deserves pat on the back ! :)
Could someone analyze the performance impact in this case?
with one single tablespace, does it need more time to insert/query than using multple tablespaces?
It's almost imposible to give the information you request with the information you post. They are so many variables: Number of datafiles, Storage, ASM?, Hot objects, Database parameters,etc. I recommend you to read some material regarding concepts, performance tuning,etc on oracle tahiti.
Francisco Munoz Alvarez
It's really depends on how your storage been setup. Besides the lack of parallel backup ability like other user pointed out. There shouldn't be any other major performance impact solely because of big tablespace. Or bigfile tablespace alone doesn't cause performance problem. It's only a problem if you setup is wrong. For example setup this file on system that doesn't support striping.
Since you have 3.3 TB size tablespace with one single datafile, that means you must have a big file tablespace which only support one datafile.
Performance of database opens, checkpoints, and DBWRThat is in the event of media crash, that could be only affect one or two small files in traditional setup, but in your case, you need to restore whole big file.
processes should improve if data is stored in bigfile
tablespaces instead of traditional tablespaces.
However, increasing the datafile size might increase
time to restore a corrupted file or create a new
Some more information about big file tablespace here,
Considerations with Bigfile Tablespaces