watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=


Q           The title is as follows :

stay Oracle in , What is a large file table space (Bigfile Tablespace)?


     
A           The answer is as follows :          


Oracle Table space is the highest level of logical structure , And the physical level Oracle A database is made up of a series of files . The relationship between table space and data file is one to many , A table space can correspond to multiple data files , A data file can only belong to one table space . from Oracle 10g The concept of large file table space was introduced , That is, the table space from Oracle 10g Later, it will be divided into two types , Small file table space (Smallfile Tablespace) And large file table spaces (Bigfile Tablespace). The most prominent feature of the so-called large file table space is that a table space can only correspond to one data file . Although large file table space only corresponds to one data file , But the maximum size of the data file is greatly increased , You can achieve 32TB. In the traditional small file table space, the maximum file size of each data file is 32G, But each small file table space can theoretically include 1024 Data files , therefore , The maximum value of small file table space is 32TB size . therefore , Theoretically, the total capacity of small file table space is the same as that of large file table space .

Large file table spaces are designed for very large databases . If a very large database has thousands of data files , It takes a long time to update the header information of the data file . If a large file table space is used , You can use big data files to reduce the number of data files , Make the operation of updating the header information of data file faster .

To create a large file table space , Can be in CREATE Use in statement BIGFILE keyword . for example :“CREATE BIGFILE TABLESPACE mybigtbs01 DATAFILE 'f:\bigtbs01.dbf' SIZE 20G;”. here , adopt “CREATE BIGFILE TABLESPACE” Statement to create a large file table space , We need to pay attention to two aspects , One is that large file table space must use local area (LOCAL EXTENT) Management style , Not allowed DMT(Dictionary Managed Tablespace). On the other hand, it's a period of SEGMENT Space use AUTO Automatic mode , Do not use MANUAL.

SYSTEM and SYSAUX A table space is always created as a small file table space . Want to create a small file table space , Can be in CREATE Use in statement SMALLFILE Keywords or not using such keywords . for example :“CREATE SMALLFILE TEMPORARY TABLESPACE lhrtbs01 TEMPFILE 'e:\tmptbs01_1.dbf' SIZE 4M UNIFORM SIZE 64K;”.

If you want to know what the system created by default is SMALLFILE still BIGFILE Table space , You can perform the following query :

SQL> SELECT PROPERTY_NAME,PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE '%TBS%';

    PROPERTY_NAME                  PROPERTY_VALUE

    ------------------------------ ---------------------------------------

    DEFAULT_TBS_TYPE               SMALLFILE

By command “ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;” To modify the default table space type of the system . It is recommended that you bring... When creating a table space BIGFILE or SMALLFILE Parameters , So as not to create unreasonable table space due to parameter setting .