Author: Fenng
Date: 27-NOV-2004
Source:
http://www.dbanotes.net
Version: 0.1
basic introduction
Oracle 10g's storage capacity has been significantly enhanced. This table is now in many ways, hereby introducing 10G new tablespace types: big file (BIGFILE) table space.
The large file table space improves the management capabilities of Oracle on VLDB from a certain angle. Only the LMT (LOCALLY Managed TableSpaces) of the automatic segment space is supported by the Bigfile tablespace. The big file table space can only contain one file, but the file can reach 4G data block size. (The following uses BFT to refer to Bigfile TableSpace.)
BFT can be used in conjunction with the following storage techniques:
Automatic Storage Management (ASM) LVM OMF
Theoretical BFT can reach the value listed below:
Data block size (unit: k) BFT maximum (unit: t) 2K8T4K16T8K32T16K64T32K128T
In the actual environment, this is also limited by the file system of the operating system.
BFT basic operation
10G Database When you are created, you will specify the default tablespace type. If you don't specify, the table space of the smallfile type is default.
SQL> SELECT *
2 from Database_Properties
3 where property_name = 'default_tbs_type';
Property_Name Property_Value Description
--------------------------------------- -------------------------
Default_TBS_TYPE SMALLFILE DEFAULT TABLESPACE TYPE
In this case, if we don't specify a type when we create a table space, the default created is a table space of the SmallFile type. You can modify the tablespace type of the database by the alter Database command:
SQL> ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;
Database altered.
SQL> SELECT *
2 from Database_Properties
3 where property_name = 'default_tbs_type';
Property_Name Property_Value Description
--------------------------------------- -------------------------
Default_TBS_TYPE BIGFILE DEFAULT TABLESPACE TYPE
SQL>
SQL> ALTER DATABASE SET DEFAULT SmallFile TableSpace;
Create a table space of the Bigfile type, just specify additional parameter Bigfile, others similar to the original creative tablespace syntax:
Create Bigfile TableSpace Bftbs
DataFile '/u01/app/oracle/oradata/demo/bftbs01.dbf' size 5m;
DBA_TABLESPACES (User_Tablespaces) and V $ TABLESPACE These two views can view information about the Bigfile tablespace. Let's see what changes in DBA_TABLESPACES in 10G: SQL> Desc DBA_TABLESPACES
Name NULL? TYPE
------------------------------------- -----------------------
TABLESPACE_NAME NOT NULL VARCHAR2 (30)
Block_size not null Number
Initial_extent Number
Next_extent Number
Min_extents not null number
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
Min_extlen Number
STATUS VARCHAR2 (9)
CONTENTS VARCHAR2 (9)
Logging varchar2 (9)
Force_Logging Varchar2 (3)
EXTENT_MANAGEMENT VARCHAR2 (10)
Allocation_type varcha2 (9)
Plugged_in varchar2 (3)
Segment_space_management varchar2 (6)
DEF_TAB_COMPRESSION VARCHAR2 (8)
Retention varchar2 (11)
BIGFILE VARCHAR2 (3)
SQL>
Compared with 9i, the DBA_TABLESPACES view is more than two columns: Ret1 and Bigfile. Where a BIGFILE column describes whether the table space is BFT:
SQL> SELECT TABLESPACE_NAME, BIGFILE
2 from DBA_TABLESPACES;
TABLESPACE_NAME BIG
------------------------------ ---
SYSTEM NO
Undotbs no
Sysaux no
Temp NO
Users no
Example No
Test No
Bftbs Yes
Rows SELECTED.
V $ TABLESPACE view also adds new columns to 9i:
SQL> Desc v $ TABLESPACE
Name NULL? TYPE
------------------------------------- -----------------------
TS # Number
Name varchar2 (30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2 (3)
BIGFILE VARCHAR2 (3)
Flashback_on varchar2 (3)
Where Flashback_on and Bigfile are new.
BFT attribute
BFT has some unique properties.
1. Each table space can only contain a data file. If you try to add a new file, an ORA-32771 error is reported:
SQL> ALTER TABLESPACE BFTBS
2 add datafile '/u01/app/oracle/oradata/demo/bftbs02.dbf' size 5m;
Alter TableSpace Bftbs
*
Error At Line 1:
ORA-32771: Cannot Add File to Bigfile TableSpace
2. Only the LMT (LOCALLY Managed TableSpaces) of automatic segment space management supports BFT
SQL> Create Bigfile TableSpace Bftbs02
2 DataFile '/u01/app/oracle/oradata/demo/bftbs02.dbf' size 5m
3 Extent Management DICTIONARY;
Create Bigfile TableSpace Bftbs02
*
Error At Line 1:
ORA-12913: Cannot Create Dictionary Managed TableSpace
SQL> Create Bigfile TableSpace Bftbs02
2 DataFile '/u01/app/oracle/oradata/demo/bftbs02.dbf' size 5m
3 segment space management manual;
Create Bigfile TableSpace Bftbs02
*
Error At Line 1:
ORA-32772: Bigfile Is Invalid Option for this Type of TableSpace
3. The relative file number (Relative_FNO) is 1024 (4096 on OS / 390) because the BFT has only one data file, so its relative file number is fixed: 1024
SQL> SELECT TABLESPACE_NAME, FILE_ID, RELATIVE_FNO
2 from DBA_DATA_FILES;
TABLESPACE_NAME FILE_ID RELATIVE_FNO
------------------------------------------------------------------------------------------------------------------------------------------ -
Users 4 4
Sysaux 3 3
Undotbs 2 2
SYSTEM 1 1
EXAMPLE 5 5
TEST 6 6
BFTBS 7 1024
7 rows selected.
SQL>
4.RowID changes
The RowID structure on the table stored on the BFT is somewhat different from the RowID structure on the SmallFile tables. To get ROWID information correctly, the DBMS_ROWID package adds a new parameter TS_TYPE_IN to solve this problem. Refer to this example:
SQL> SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER (RowID, 'Bigfile')
2 from foo;
DBMS_ROWID.ROWID_BLOCK_NUMBER (RowID, 'Bigfile')
----------------------------------------------
twenty four
SQL>
Can you create how big tablespaces?
We mentioned earlier, BFT is also limited by the operating system's file system. Let's take a Linux operating system as an example:
SQL> Show Parameters DB_BLOCK_SIZE
Name Type Value
----------------------------------- --- ---------------------------
DB_BLOCK_SIZE INTEGER 8192
SQL>
That is to say, in theory we can create a table space of up to 32T (4g * 8K). Can we do it?
SQL> Create Bigfile TableSpace Bftbs02
2 DataFile '/u01/app/oracle/oradata/demo/bftbs02.dbf' size 20t reuse;
Create Bigfile TableSpace Bftbs02
*
Error At Line 1:
ORA-01119: Error In Creating Database File '/u01/app/oracle/oradata/demo/bftbs02.dbf'
ORA-27059: COULD NOT Reduce File Size
Linux Error: 27: File Too LargeadDitional Information: 2
SQL>
Note our operating system information (black part): File Too Large. This illustrates the allowable value of the operating system. The environment I used is Fedora Core Linux, the core version is 2.6.9, and the file system is EXT3. 2.4 The kernel subsequent version is supported by LFS (Large File Support).
File system (block) file size limit file system size limit EXT2 / 3 (2K) 256G8TEXT2 / 3 (4K) 2T16TEXT2 / 3 (8K) 64T32TREISERFS 3.6 1E16T
We check the size of the OS file system block:
[root @ fc3 ~] # tune2fs -l / dev / hda7 | grep block
Block count: 2621440
Block Size: 4096
Blocks per group: 32768
[root @ fc3 ~] #
That is, we can create a file that is not more than 2T on the operating system. Although we don't have that big storage space, you may also test it:
SQL> Create Bigfile TableSpace Bftbs02
2 DataFile '/u01/app/oracle/oradata/demo/bftbs02.dbf' size 1800g;
In another terminal, observe the change of the directory:
[root @ fc3 demo] # ls -ltr
Total 1159048
-rw ------- 1 Oracle Oracle 5251072 NOV 28 20:05 bftbs01.dbf
-rw ------- 1 Oracle Oracle 1932735291392 NOV 28 20:49 bftbs02.dbf
[root @ fc3 demo] #
Wow, we really can observe Oracle in creating a "super big" file, close to 1.8T file :-) After a while, Oracle will report an error (after all 1800g big file):
Create Bigfile TableSpace Bftbs02
*
Error At Line 1:
ORA-19502: Write Error on file "/u01/app/oracle/oradata/demo/bftbs02.dbf",
Blockno 898048 (blocksize = 8192)
ORA-27072: File I / O Error
Additional Information: 898047
It can be seen that in this case, due to the limitations of the operating system file system, we can only create a BFT below 2T.
Do you need BFT?
If BFT is applied, the advantages have existent. According to Oracle official documentation, the value of two parameters of DB_FILES and MAXDATAFILES is reduced to SGA (why is the original pressure?). The maximum number of data files in the database is limited (64k files), and the emergence of BFT has indeed a certain positive significance for massive databases. To a certain extent, BFT simplifies management, but it may be a disaster when recovery.
At the point of view, at least we are now in most cases no need to use BFT. "Do you want to put the eggs in a basket?"
Reference Information
Oracle Database Administrator's Guide 10g Release 1 (10.1) Part Number B10739-01 (Note 62294.1) Large File Support in Linux - http://www.suse.de/~aj/linux_lfs.htmlMetalink [NOTE: 262472.1] 10g: BIGFILE Type TableSpaces Versus SmallFile Type
Fenng, a US-raising company DBA, spare time, mixed with each database, is not tired. Currently payment of how to use the Oracle database effectively build enterprise applications. A little study on Oracle Tuning, Troubleshooting.
Personal technology site:
http://www.dbanotes.net/. Can via email
DBANOTES@gmail.com Contact him.
Origin
http://www.dbanotes.net/oracle/10g_bigfile_tablespaces.htm
All articles (by
Fenng) Are Licensed Under A
Creative Commons license.
I Would Welcome Any Feedback. Please send Questions, Comments or Corrections to
Dbanotes@gmail.com
