Skip to Main Content

Breadcrumb

Warning

Before you submit your comment...

Remember - this is about feedback to the original question. AskTOM is about building a strong Oracle community, not stealing time away from others needing assistance by sneaking in a brand new question under the guise of "Comment".

If your comment requires a response that might include a query, please supply very simple CREATE TABLE and INSERT statements. Nice simple scripts so we can replicate your issue simply.

Remember to take a look at Legal Notices and Terms of Use before supplying a comment...

Don't forget, your comments are public. If you want to send a comment to just the AskTOM team, please use the feedback option

Comment

Highlight any SQL, PL/SQL, or fixed-width text and click the <code> button
 (will never be published or used to spam you)

Question and Answer

Connor McDonald

Thanks for the question, alee.

Asked: January 15, 2018 - 9:05 am UTC

Last updated: July 13, 2023 - 5:40 am UTC

Version: 12

Viewed 10K+ times! This question is

You Asked

I have a big file tablespace，which has 100G size，I wonder ,big file tablepspace vs normal small file tablespace，which is better? someone said, it is very difficult to recover if bad block occurs in a big file, and what's more, performance is worse than normal small file tablespace.

which is the best chooice if I have many tablespaces,each tablespace has about 100G data, and each will increase 50G every year?

and Connor said...

There is no performance difference between a bigfile tablespace and a smallfile tablespace. As you probably know, the ROWID is used to locate a particular row for a particular table in a tablespace (ie, one of its datafiles).

The difference between bigfile and smallfile is simply we dedicate more bits in the rowid to the block address in the file, and less bits to the file location. So the file can be bigger (we can address more blocks), but you can only one file (because we dont have as many bits any more to reference it).

So you won't see any differences in performance, usage etc.

What *is* worth considering is your "unit of work". If you have to recover an entire datafile, then whether it is a smallfile or a bigfile, it is worth knowing what the recovery time will be, and whether it fits into your acceptable business availability requirements. If you had a 100TB bigfile that you had to restore, it might take a while.

That said, its rare to need to recover a particular datafile - the most common scenarios are recover a database, or recover/fix some blocks. In either case, there are mechanisms available to avoid needing to do massive file operations, in which case, the choice between bigfile and smallfile makes no difference.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment