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

A Database consists of one or more logical storage units called table spaces (tablespace). Logical storage units in the table space are segments (segment), which can be further divided into data extensions (extent). Data extension consists of a continuous set of data blocks (datablock).

Large file tablespace

In Oracle, users can create large file table spaces (bigfile tablespace). In this way, the tablespace (tablespace) used by the Oracle database can consist of a single large file instead of several small data files. This allows Oracle to take advantage of the 64-bit system's ability to create and manage very large files. In a 64-bit system, the storage capacity of the Oracle database has been extended to 8 EB (1EB = 1024PBJ 1PB = 1024TB database 1024GB).

When the database file is managed by Oracle (Oracle-managed files) and uses a large file tablespace (bigfile tablespace), the data file is completely transparent to the user. In other words, the user only needs to perform administrative operations on the tablespace (tablespace) and does not care about the underlying data file (datafile). Use large file tablespaces to make tablespaces the main object of disk space management, backup, and recovery operations. The use of large file tablespaces, combined with database file management (Oracle-managed files) technology by Oracle and automatic storage management (Automatic Storage Management) technology, eliminates the need for administrators to manually create new data files (datafile) and maintain a large number of database files, thus simplifying database file management.

By default, the data base creates a small file table space (smallfile tablespace), which is the traditional table space (tablespace) type in Oracle. SYSTEM and SYSAUX table spaces in the database are always created using traditional types only locally managed (locally managed), and segment space automatically managed (automatic segmentspace management) tablespaces (tablespace) can use large file tablespaces (bigfile)

Tablespace). But there are two exceptions: locally managed undo tablespaces (undo tablespace) and temporary tablespaces (temporary tablespace), even if their segments (segment) are manually managed (manually managed), large file tablespaces can be used. An Oracle database can contain both large and small file tablespaces (bigfile/smallfile tablespace). The type of tablespace (tablespace) is not considered when the SQL statement is executed, unless the data file (datafile) name is explicitly referenced in the statement.

The administrator can create a set of temporary table spaces (temporary tablespace), and users can take advantage of the temporary space provided by each table space (tablespace) in the group when needed. Administrators can also specify tablespace groups (tablespace group) as the default temporary tablespaces for the database. When users need a lot of temporary space for sorting operations, they can take advantage of large file tablespaces and tablespace groups.

Advantages of using large file tablespaces

● uses large file tablespaces (bigfile tablespace) to significantly enhance the storage capacity of Oracle databases. A small file table space (smallfile tablespace) can contain up to 1024 data files (datafile), while a large file table space contains only one file, which has a maximum capacity of 1024 times that of small data files. From this point of view, the maximum capacity of large file tablespaces and small file tablespaces is the same. However, because a maximum of 64K data files are used in each database, the limit number of tablespaces in the database when using large file tablespaces is 1024 times that when using small file tablespaces. The total database capacity of using large file tablespaces is three orders of magnitude higher than that of using small file tablespaces. In other words, when an Oracle database uses a large file tablespace and uses the maximum block capacity (32K), its total capacity can reach 8EB.

● reduces the number of data files by using large file tablespaces in very large databases, thus simplifying the management of data files. Due to the reduction of data files, the information about data files in SGA and the capacity of control files (control file) are also reduced.

● simplifies database management because data files are transparent to users.

Factors to consider when using large file tablespaces

● large file tablespaces (bigfile tablespace) should be used in conjunction with automatic Storage Management (Automatic Storage Management) or other logical volume management tools (logical volume manager), which should support dynamic extension of logical volumes, as well as striping (distribution of data across disks) or RAID.

● should avoid using large file tablespaces on systems that do not support striping, as this will not facilitate parallel execution (parallel execution) and parallel backup of RMAN (backup)

Parallelization).

● avoid using the disk group (disk group) that the table space is using when there may not be enough space and the only way to expand the table space is to add data files to another disk group

Large file tablespace.

● does not recommend using large file tablespaces on platforms that do not support large files, which limits the capacity of tablespaces (tablespace). Refer to the relevant operating system documentation for the maximum text supported by it.

The capacity of a piece.

● will improve the performance of large file tablespaces instead of traditional tablespaces, database open (open), checkpoints, and DBWR processes. But enlarge the data file

(datafile) capacity may increase backup and recovery time.

Tip:

The user does not specify a data extension when creating a tablespace (tablespace)

(extent), local management (locally managed) is used by default.

10g newly added table space type: large file (Bigfile) table space.

In a way, large file tablespaces improve the management ability of Oracle on VLDB. Only LMT (Locally Managed Tablespaces) with automatic segment space management supports BIGFILE tablespaces. Large file tablespaces can contain only one file, but files can reach 4G block size. (BFT is used to refer to BIGFILE Tablespace.

BFT can be used in conjunction with the following storage technologies:

Automatic Storage Management (ASM)

LVM

OMF

The theoretical BFT can reach the values listed below:

Block size (in K) BFT maximum (in T)

2k 8T

4k 16T

8k 32T

16k 64T

32k 128T

In the real world, this is also limited by the file system of the operating system.

Basic operation of BFT

When a 10g database is created, the default tablespace type is specified. If not specified, the default is a tablespace of type SMALLFILE.

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 do not specify a type when we create a tablespace, we will create a tablespace of type SMALLFILE by default. You can modify the default tablespace type of the database through 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

To create a tablespace of type BIGFILE, you only need to specify an additional parameter, BIGFILE. The syntax for creating a tablespace is similar to that of the original:

CREATE BIGFILE TABLESPACE bftbs

DATAFILE'/ u01 SIZE

The DBA_TABLESPACES (USER_TABLESPACES) and V$TABLESPACE views can view information about the BIGFILE tablespace. Let's take a look at how DBA_TABLESPACES has changed in 10g:

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

8 rows selected.

BFT attribute

BFT has some unique properties.

1. Each tablespace can contain only one data file. If you try to add a new file, an ORA-32771 error is reported:

SQL > ALTER TABLESPACE bftbs

2 ADD DATAFILE'/ u01 SIZE

ALTER TABLESPACE bftbs

*

ERROR at line 1:

ORA-32771: cannot add file to bigfile tablespace

two。 Only LMT (locally managed tablespaces) with automatic segment space management supports BFT

SQL > CREATE BIGFILE TABLESPACE bftbs02

2 DATAFILE'/ u01 SIZE

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 SIZE

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. Relative file number (RELATIVE_FNO) is 1024 (4096 on OS/390)

Because BFT has only one data file, its relative file number is also 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 >

Http://www.51CTO reminds you not to spam ads! / xueyuanzhuanqu/jishuwenzhang/201110/jishuwenzhang-1107.html

=

Bigfile tablespace in oracle10g

Http://www.ixpub.net/blog-25038273-410563.html

Bigfile tablespace is a new feature of Oracle10g. Bigfile tablespaces contains 1 datafile with a maximum of 4G blocks. Therefore, the maximum bigfile tablespace using 8KBdata blocks can reach 32TB.

Bigfile tablespaces is often used for very large DB. If a large DB has thousands of read/write datafiles, operations such as checkpoints can be very time-consuming. If you use a large datafile, reduce the number of datafiles, will improve efficiency.

Use the BIGFILE keyword when creating:

CREATE BIGFILE TABLESPACE BIG_DEMO

DATAFILE'/ ORADATA/PROD/BIG_DEMO.DBF' SIZE 25G

Smallfile tablespace is the new name of the original Oracle tablespace datafile option. In smallfile tablespace, you can include multiple datafiles. Each datafile has a maximum of 4m data blocks. Therefore, the datafiles maximum 32GB in the smallfile tablespace that uses 8KB data blocks. Smallfile tablespace has a maximum of 1023 datafiles. SYSTEM and SYSAUX tablespaces are always smallfile tablespaces.

1. Question: what is the difference between a large file tablespace and a small file tablespace with only one data file (that is, what we commonly use)?

Here is the explanation of BIGFILE netizens:

Bigfile tablespace is a new feature of Oracle10g. Bigfile tablespaces contains 1 datafile with a maximum of 4G blocks. Therefore, the maximum bigfile tablespace using 8KBdata blocks can reach 32TB.

Bigfile tablespaces is often used for very large DB. If a large DB has thousands of read/write datafiles, operations such as checkpoints can be very time-consuming. If you use a large datafile, reduce the number of datafiles, will improve efficiency.

Use the BIGFILE keyword when creating:

CREATE BIGFILE TABLESPACE BIG_DEMO

DATAFILE'/ ORADATA/PROD/BIG_DEMO.DBF' SIZE 25G

Smallfile tablespace is the new name of the original Oracle tablespace datafile option. In smallfile tablespace, you can include multiple datafiles. Each datafile has a maximum of 4m data blocks. Therefore, the datafiles maximum 32GB in the smallfile tablespace that uses 8KB data blocks. Smallfile tablespace has a maximum of 1023 datafiles. SYSTEM and SYSAUX tablespaces are always smallfile tablespaces.

According to ROWID's understanding of the scarlet letter above

ROWID rfile# block# row# is 10 bit, 22 bit, 16 bit, respectively.

So a single data file contains a maximum of 2 ^ 22-1 (4194303) blocks, and a block of 8K is 8 * the maximum is 32 gigabytes.

Bigfile tablespaces contains 1 datafile with a maximum of 4G blocks

two。 Question: how many tablespaces can ORACLE see at most? How many data files can be created per tablespace?

It is also analyzed according to ROWID format:

Number of tablespaces before 8i = = maximum number of data files 1024

After 8i, the number of table spaces 2 ^ 32-1, the number of data files, the number of table spaces * 1023

Rowid is the only id that records the physical location. Before the oracle 8 version, rowid was composed of file# block# row#, occupying 6 bytes space, 10 bit file#, 22bit block#, 16 bit row#.

Starting from oracle 8, rowid has become extend rowid, which is made up of data_object_id# rfile# block# row# and occupies 10 bytes space, 32bit's data_object_id#,10 bit's rfile#,22bit 's block#,16 bit's row#.. As the composition of rowid has changed from file# to rfile#, the limit of the number of data files has changed from no more than 1023 in the entire library to no more than 1023 data files per tablespace.

After talking about the composition of rowid, let's take a look at how many bytes rowid occupies in the index. The number of bytes occupied by rowid stored in the previous index of oracle8 is also 6bytes. After oracle8, although oracle uses extend rowid, the rowid of bytes is still stored in the ordinary index, only the extend rowid of 10bytes is stored in global index, and extend rowid is also a necessary condition for global index. We will explain why.

Why does golbal index need to include data_object_id# in index rowid entry? What would happen if it wasn't included? First of all, we need to know that the rowid entry of index exists in order to find the specific physical location of this record of the table according to it, we need to know which data file it is in, which block, on which line, the ordinary index oracle can be known according to rfile#,block#,row#, but partition table can be distributed in multiple table spaces, that is, it can be distributed in multiple data files, when we set up local index. Index rowid entry does not contain data_object_id#, because oracle can know which table partition the index corresponds to, and can get the ts# (tablespace number) of the table partition, so oracle can find the specific data file according to ts# and rfile#. But if it is golbal index, if it does not include data_object_id#, then we can not know which table partition the index corresponds to, nor can we know the conversion relationship between its rfile# and file#, so it will not find the corresponding record. After including data_object_id#, oracle can convert rfile# to file# based on data_object_id# and then find the corresponding physical location of the record. It is important to note that to understand the above concepts, we still need to understand the difference between file# and rfile#.

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.