How to create an Oracle large file table space

How to create an Oracle large file table space

Sql>create bigfile tablespace Table name
DataFile ' d:\ndo\ddo\ table name. DBF '
SIZE 500M autoextend on;


Sql>create bigfile tablespace BF_IMAGES_XP
DataFile ' e:\datacenter\bf\bf_images_xp.dbf ' size 500M autoextend on;
The description is as follows:
Create a large table space, the name is: BF_IMAGES_XP, the data file is E:\DATACENTER\BF\BF_IMAGES_XP.DBF
Initialization size is 500M and the file grows automatically


Sql>create bigfile tablespace BF_V_XP
DataFile ' e:\datacenter\bf\bf_v_xp.dbf ' size 500M autoextend on;


Where bigfile indicates that the table space created is a large file table space.
DATAFILE specifies large files that make up a large file table space (Shanghai hard disk Data recovery file),
Size represents the initial size of a large file:
Autoexted on indicates that large files are allowed to expand automatically.
User information: for reference.

Discovery table space file capacity is associated with db_block_size,
When the initial library is built, db_block_size to the actual needs,
Set to 4k,8k, 16K, 32K, 64K, and several sizes,
The maximum number of Oracle physical files is only 4,194,304 blocks (determined by the operating system).
The maximum value for a smallfile tablespace table space file is 4194304xdb_block_size/1024m.
That
4k Maximum table space is: 16384m=16g
8K Maximum table space is: 32768m=32g
16k Maximum table space is: 65536m=64g
32K Maximum table space is: 131072m=128g
64k Maximum table space is: 262144m=256g
//---------------------------------------------------------------------
Oracle bigfile tablespace Large File table space----------------------------
Oracle 10g new Table space type: Large file (Bigfile) tablespace.
Large file table spaces improve Oracle's VLDB management capabilities in some ways.
Only the LMT (locally Managed tablespaces) of automatic segment space management supports Bigfile tablespace.
A large file table space can contain only one file, but the file can reach a size of 4G blocks.
(hereinafter used BFT to refer to Bigfile tablespace.) BFT can be used in conjunction with the following storage technologies:
Automated storage Management (ASM) LVM OMF
The theoretical BFT can achieve the values listed below:
Data block Size (unit: K) BFT maximum (unit: T)
2k 8T
4k 16T
8k 32T
16k 64T
32k 128T
In the actual environment, this is also subject to the operating system's file system limitations.


BFT Basic Operations
The 10g database specifies the default table space type when it is created.
If not specified, the default is the table space of the Smallfile type.
Sql> SELECT * from database_properties WHERE property_name = ' default_tbs_type ';
In this case, if we do not specify a type when we create the tablespace, then the default is to create the table space of the Smallfile type.


Modify the database default table space type
You can modify the default table space type for a database by using the ALTER DB command:
sql> ALTER DATABASE SET DEFAULT bigfile tablespace;
Database altered.
Sql> SELECT * from database_properties WHERE property_name = ' default_tbs_type ';
Sql>
sql> ALTER DATABASE SET DEFAULT smallfile tablespace;
To create a tablespace of type bigfile, simply specify an additional parameter bigfile
The other is similar to the original CREATE TABLE space 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 table space.
Let's see what happens to the dba_tablespaces in 10g:
Sql> desc dba_tablespaces
　 　　
Sql>
Compared with 9i,
The Dba_tablespaces view has two more columns: RETENTION and Bigfile.
Where the Bigfile column indicates whether the tablespace is BFT:
Sql> SELECT tablespace_name, bigfile from Dba_tablespaces;
8 rows selected. The V$tablespace view also adds new columns relative to 9i:


Sql> desc V$tablespace Name
The flashback_on and Bigfile columns are all new.


BFT Property
BFT has some unique properties.
1. Each table space can contain only one data file. If you attempt to add a new file, a ORA-32771 error is reported:
sql> ALTER tablespace Bftbs
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 LMT (locally managed tablespaces) supported by automatic segment space management BFT
sql> CREATE bigfile tablespace bftbs02
DataFile '/u01/app/oracle/oradata/demo/bftbs02.dbf ' SIZE 5M
EXTENT MANAGEMENT DICTIONARY;
CREATE bigfile tablespace BFTBS02 * ERROR at line 1:
Ora-12913:cannot Create dictionary managed tablespace


sql> CREATE bigfile tablespace bftbs02
DataFile '/u01/app/oracle/oradata/demo/bftbs02.dbf ' SIZE 5M
SEGMENT space MANAGEMENT MANUAL;
CREATE bigfile tablespace BFTBS02 * ERROR at line 1:
Ora-32772:bigfile is invalid option to this type of tablespace


3. Relative file number (RELATIVE_FNO) is 1024 (4096 on os/390)
Because BFT has only one data file, its relative file number is fixed: 1024
Sql> SELECT tablespace_name, file_id, RELATIVE_FNO
From Dba_data_files;
Sql>


4.rowid of Change
The ROWID of the table stored on the BFT is somewhat different from the ROWID structure on the Smallfile table space.
To get the ROWID information correctly, the DBMS_ROWID package adds a new parameter ts_type_in to solve the problem.
Refer to this example:
Sql> SELECT dbms_rowid.rowid_block_number (ROWID, ' bigfile ')
from Foo;
24
Sql>
How much table space can you create?
As we mentioned earlier, BFT is also subject to the operating system's filesystem limitations.
Here we take the Linux operating system for example:
Sql> Show Parameters Db_block_size
　　
Db_block_size integer 8192
Sql>
In other words, theoretically we can create the maximum 32T