Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about what the large Oracle Bigfile Tablespace document table space is like. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

The Oracle tablespace Tablespace is the highest level of the logical structure, and the others correspond to segment objects segment, extent extent and block block, respectively. The physical Oracle database is composed of a series of files, in which the data file datafile is the object corresponding to Tablespace.

The relationship between Tablespace and Datafile is one-to-many. A tablespace Tablespace can correspond to more than one Datafile,Datafile, but it can only belong to one Tablespace. In the traditional concept of Oracle management, it is advocated to create multiple data files in one tablespace, especially on multiple storage disks. However, Oracle10g's BigFile Tablespace large file tablespace changes this concept a little bit.

1 、 Bigfile tablespace

In Oracle 10g, the concept of Bigfile tablespace is introduced. Since Oracle 10g, the tablespace Tablespace has been divided into two types, smallfile tablespace and bigfile tablespace. In the past, one tablespace corresponds to multiple data files. We became Smallfile Tablespace.

The most significant difference in the so-called Bigfile Tablespace is that a tablespace can only correspond to one data file. Although Bigfile Tablespace corresponds to only one data file, the maximum volume of the data file is greatly increased. Traditional small datafile includes a maximum of 4m data blocks per file, and the maximum file size is 32G according to the size of a data block of 8K. Each Small Tablespace can theoretically include 1024 data files, so the theoretical maximum is the 32TB size. On the other hand, Bigfile Datafile has a stronger block block capacity and can include up to 4G blocks. Also calculated according to block 8K, the Bigfile Datafile size is 32KG=32TB. Theoretically, the total capacity of small tablespace and big tablespace is the same.

Let's observe Bigfile Tablespace through a series of experiments.

SQL > select * from v$version

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

SQL > create bigfile tablespace bttest datafile'd:\ ORADATA\ ORCL\ BTTEST.dbf' size 10m autoextend off

2 extent management local uniform. Size 1m

3 segment space management auto

Tablespace created

Here, we set up the Bigfile Tablespace through the create bigfile tablespace statement. Note two problems, one is that Bigfile Tablespace must be managed by local native extent, and DMT (Dictionary Managed Tablespace) is not allowed. On the other hand, the segment of segment space uses auto automatic mode, not manual.

However, these two conditions allow exceptions in Undo or temporary Bigfile tablespaces.

Small Tablespace and Bigfile Tablespace can coexist.

SQL > select tablespace_name, bigfile from dba_tablespaces

TABLESPACE_NAME BIGFILE

11 rows selected

When the bigfile tablespace was created, the data file was specified. If we try to add a new data file at this time, the Oracle system will report an error.

SQL > alter tablespace bttest add datafile'd:\ ORADATA\ ORCL\ BTTEST02.dbf' size 10m autoextend off

Alter tablespace bttest add datafile'D:\ ORADATA\ ORCL\ BTTEST02.dbf' size 10m autoextend off

ORA-32771: cannot add a file in a large file tablespace

Let's analyze the features of bigfile tablespace.

2. Relative file number of Bigfile

Here we look at some of the characteristics of the Bigfile Tablespace system.

SQL > select file_name, file_id, relative_fno from dba_data_files

FILE_NAME FILE_ID RELATIVE_FNO

D:\ ORADATA\ ORCL\ USERS01.DBF 4 4

D:\ ORADATA\ ORCL\ SYSAUX01.DBF 3 3

D:\ ORADATA\ ORCL\ UNDOTBS01.DBF 2 2

D:\ ORADATA\ ORCL\ SYSTEM01.DBF 1 1

D:\ ORADATA\ ORCL\ EXAMPLE01.DBF 5 5

D:\ ORADATA\ ORCL\ SPTABLESPACE 6 6

D:\ ORADATA\ ORCL\ QCDATA.DBF 7 7

D:\ ORADATA\ ORCL\ BTTEST.DBF 8 1024

8 rows selected

Each file contains two numbers, one is the absolute file number file_id, and the other is the relative file number relative_fno. File_id and relative_fno are the same in cases where the number of files is small, or when creating a database with fewer deleted files. Relative_fno is a cycle, with 1024 as a cycle. When file_id increments to 1024 integer multiples in turn, file_id continues to increase, and relative_fno forms an internal loop.

This mechanism reminds us of Oracle Small Tablespace's data file limit, which is also 1023 files. We imagine an extreme situation where the number of data files in a tablespace exceeds 1024, that is, the same relative_fno exists in the datafiles in a tablespace. This situation is not allowed in the first place.

Setting a relative_fno of 1024 is required for Oracle data addressing. Here we will pay attention to the rowid information of the Oracle positioning data row. Rowid is the only address within the Oracle that marks a row of records. Rowid consists of four parts: object number + file number + block number + slot line number. The length of Rowid is fixed, so the length of all four parts is fixed, where the file number fno is relative to the file number. The search order here is that the object number gets the number of the table space in which it is located, and the relative file number is used inside the table space to locate the file. If the same relative_fno file appears in a tablespace, location is not possible. Therefore, the internal file limit for small tablepsace must be 1023.

However, there are some differences in Bigfile Tablespace. If a tablespace has only one data file and only one data file, then relative_fno loses its meaning of existence. So on the relative_fno column in dba_data_files, the datafile for bigfile tablespace is direct 1024.

SQL > create table t_big tablespace bttest as select * from dba_tables

Table created

SQL > select dbms_rowid.rowid_object (t.rowid) obj_id, dbms_rowid.rowid_relative_fno (t.rowidjigle) relative_fno

2 dbms_rowid.rowid_block_number (t.rowid) block_num, dbms_rowid.rowid_row_number (t.rowid) rownums

3 dbms_rowid.rowid_to_absolute_fno (t.rowidtheconcept SYSIG') abso_fno

4 from t_BIG t where rownum select substr (rowid,1,6) "object"

Substr (rowid,7,3) "file"

Substr (rowid,10,6) "block"

Substr (rowid,16,3) "row"

From test

Object fil block row

AAABPW AAF AAAAv1 AAA

In small datafile, there is a 24-bit binary representation of 6 × 4 bits for data blocks. Oracle uses the middle 22 bits for block address storage. In this way, the range of block numbers that can be represented by 22-bit binaries is (2 ^ 22-1) = 4m blocks. If calculated according to the general size of 8K per block, the upper limit of each small datafile size is 32G. If the upper limit of 32K per block is calculated, then the upper limit of each small datafile size is 128G.

In bigfile datafile, things are different. Since there is no relative_fno problem, there is no need to save up to 1024 of the value of relative_fno in rowid. In this way, 10-bit binary bits are saved to locate data blocks, and rowid of the same length can address data blocks with 32-bit binary length. Each bigfile datafile can include up to (2 ^ 32-1) = 4G blocks. In an environment with a block size of 8K, the maximum is 32TB data. If the block size is 32K, the file size can be up to 128TB size.

4. Conclusion and discussion

In the first part of this paper, we discuss the theoretical analysis of Bigfile Tablespace and Small Tablespace, both of which theoretically have the same maximum capacity. It's just that Bigfile has a large limit on the capacity of a single file, so what do we think of this new type?

First of all, in the actual production environment, Bigfile Tablespace has a slight advantage in dealing with large amounts of data storage. Because Small Tablespace consumes a lot of multi-file management overhead when implementing the same storage size. At the same time control files in order to maintain files in multiple locations, cooperate with checkpoints and file headers such as SCN, it takes a lot of energy.

Second, the storage capacity of Small Tablespace is only theoretical, and few systems actually build thousands of files in a tablespace. From this point of view, Bigfile is not a small attraction.

Finally, Oracle's support for Bigfile Tablespace is comprehensive. At the storage level, ASM, Logical Volumn Manager/RAID, Dynamically Extensible Logical Volumes and OMF all provide comprehensive support.

But Bigfile Tablespace is not a panacea. Bigfile is the Bigfile at the Oracle level and does not involve support at the OS level. When using Bigfile, choose the appropriate underlying storage system to provide support. In addition, the concept of Bigfile runs counter to the Imax O dispersion theory that we have claimed in the past. Therefore, it is best to use Bigfile with underlying strong storage support (such as ASM) to achieve the optimal performance balance.

The above is what the Oracle Bigfile Tablespace large file table space shared by Xiaobian is like. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.