Maximum minimum ____oracle of Oracle 11g tablespace

The size of the file supported by Oracle and the number of his db_block_size and Db_block are determined. Prior to Oracle 9i, Oracle had a maximum of 2 db_block, while the db_block_size of the database instance we built through the Oracle default template was 8K. So the default size of a single file that Oracle 9i previously supported is 32GB. Although there is no such limitation in Oracle 11g, it defaults to the same amount of power. So I said in the front if you encounter this problem, you are not the average person, you are creating a single file more than 32GB of tablespace.


So how to solve this problem, I provide three of reference programs, you use the choice of which is appropriate for you, hehe:


i) do not create a single file that exceeds 32GB of tablespace

If you create more than 32GB of table space, store this tablespace as multiple data files, and each file is less than 32GB. In this way, you can create a table space successfully.

II) expansion of Db_block_size


According to Oracle's algorithm, we can easily think of this workaround. Number is fixed, but db_block_size can be changed (the maximum size of db_block_size is 32KB). If you expand the Db_block_size to 32KB (32-bit system looks like 16KB, I failed to create 32KB block_size in the Red Hat Enterprise version of the operating system. , then our system can support a single data file Max 128GB.


The plan sounds fascinating, but it's not really the case. Because it's not easy to modify db_block_size. Because this db_block_size should be specified when creating an instance. And you cannot specify db_block_size by simply modifying the parameters. Create a larger Db_block_size database instance I'll talk about it later.


III Create bigfile table spaces


Bigfile table space was introduced in oracle11g, and he took full advantage of the 64-bit CPU addressing capability, which enabled Oracle to manage the total number of data files up to 8EB. The size of a single data file reaches 128TB, even if the default 8K db_block_size is 32TB.

The SQL statement used to create the Bigfile table space is also simple.


Create Bigfile tablespace ...


The following statement is exactly the same as the normal statement.


It should be noted that using Bigfile tablespace, he can only support one data file. This means that the maximum size of the file is the maximum size of the table space, and you cannot enlarge the size of the table space by adding data files. So Oracle said, if you do not have the rest of the file space, you still do not use bigfile table space, this table space you do not have the scope of expansion, hehe.


Oracle has also told you that it is recommended that you use LVM or other software to manage your disks so that your space can grow dynamically.