1

The following 2 sentences are excerpt from Oracle documentation. You can easily fine them by google them directly. My question is, 4G and 4M are the unite of digital information storage. What's the meaning in the context of the following sentences?

Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks.
A smallfile tablespace can contain up to 1022 files with up to 4M blocks each.

Thanks.

2 Answers 2

4

In this case 4G and 4M aren't quite referring to what I think you mean by the unit of digital storage; it isn't referring to 4 gigabytes and 4 megabytes. It's just a number, not a unit.

Oracle has to keep track of the blocks in the tablespace, and appears to be limited to a 32-bit 'address' space (2^32 = 4,294,967,296 = 4G). So it can internally reference up to 4G blocks of data. The distinction between bigfile and smallfile is that bigfile can reference one huge file with all 4G blocks in it, which smallfile can reference up to 1022 files with up to 4M blocks in each. (There seems to be some overhead somewhere, since it's 1022 instead of 1024). The total number of blocks ends up being (almost) the same, they're just organised differently.

So far this has little to do with how much actual data that is. This is where the database block size comes in. If you have a 4KB (4096 byte) block size, a bigfile can be up to 4G x 4096 bytes, or 16TB; while with smallfiles each file could be up to 4M x 4096 bytes, or 16GB. As you can have 1022 files, the total available storage is (almost) the same. If your database blocksize is twice as big, 8KB, the bigfile can also be twice as big, up to 32TB; and each smallfile can similarly be up to 32GB.

The bigfile documentation may help explain it better than I can. (Though confusingly that uses '32K' to mean 32 kilobytes, which is normally clear but when also talking about 4G blocks it might have been better to use '32KB'... I'm not opining on which is 'correct'!)

5
  • Yes, I think you answer the correct one. I misinterpreted the question. Sep 13, 2011 at 17:47
  • The default block size in Oracle is 8k. I do not think smaller block sizes are supported these days.
    – BobC
    Feb 28, 2017 at 9:54
  • @BobC - this Q&A predates 12c, but even in 12c, "Typically, DB_BLOCK_SIZE is set to either 4K or 8K.".
    – Alex Poole
    Feb 28, 2017 at 10:02
  • @AlexPoole 8k has been the default in 10g at least; and maybe even earlier. I've not seen 4K used in a long time :)
    – BobC
    Feb 28, 2017 at 10:10
  • @BocC - I didn't say it wasn't the default, but smaller block sizes are still supported. I'm not sure the default is relevant to this question really.
    – Alex Poole
    Feb 28, 2017 at 10:14
0

The smallest unit that stores information in database is called Data Block in Oracle. If you know SQL Server, it calls Data Pages.

Imagine this, when you want to store something is a basket or box, you want something that isn't too big or too small. Say, you want to keep your books in your basement. you do not want 5' X 5' X 5' box. It is too big. On the other hand, if you want to keep you desktop computer with the monitor, you don't want a small box.

4G and 4M means 4 Gigabytes and 4 Megabyte. When you setup a new database, one of the most important thing is to design what size of your data block is. For a big row, you want 4G size data block. For storing a small row, you want something small like 4M data block.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.