Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

2 Answers
2

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'!)

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.