What is Bigfile Tablespace in Oracle?

What is Bigfile Tablespace in Oracle?

A bigfile tablespace consists of a single data or temporary file which can be up to 128 TB. The use of big file tablespaces can significantly decrease the number of data files for your database. Oracle Database assists parallel RMAN backup and restore on single data files.

Benefits of Bigfile Tablespace in Oracle:

•A big file tablespace in Oracle with 8k blocks can contain a 32-terabyte data file. A big file tablespace with 32k blocks can contain a 128-terabyte data file. The maximum number of data files in an Oracle database is limited (usually to 64k files). Therefore, big file tablespaces in Oracle can significantly improve the storage capacity of an Oracle database.

•This tablespace in Oracle can decrease the number of data files required for a database. An additional benefit is that the db_files initialization parameter and max data files parameter of the create a database and create control file statements can be adjusted to decrease the amount of sga space needed for datafile information and the size of the control file.

•Bigfile tablespaces simplify database management by offering datafile transparency. SQL syntax for the alter tablespace statement lets you perform operations on tablespaces, rather than the underlying individual data files.

Bigfile tablespaces are assisted only for locally managed tablespaces with automatic segment space management, with three exceptions: These are locally managed to undo tablespaces, temporary tablespaces, and the system tablespace.

Key Points:

•Bigfile tablespace are intended to be used with automatic storage management (asm) or other logical volume managers that assists striping or raid, and dynamically extensible logical volumes.

•Avoid creating big file tablespaces in Oracle on a system that does not assist striping because of negative implications for parallel query results and rman backup parallelization.

•Using big file tablespace in Oracle on platforms that do not assist huge file sizes is not recommended and can limit tablespace capacity. It refers to your operating system-specific documentation for data about maximum assisted file sizes.

How to create Bigfile Tablespaces:

To create a big file tablespace, specify the bigfile keyword of the create tablespace statement (create bigfile tablespace …). Oracle Database automatically creates a locally managed tablespace with automatic segment space management.

You can but are required not, specify the extent of management local and management in this statement.

However, the database returns an error if you specify the extent management dictionary or segment space management manual.

The remaining syntax of the statement is the same as for the create tablespace statement, but you can only specify one data file. For instance:

Sql>create bigfile tablespace big tbs

Datafile ‘/u02/oracle/data/bigtbs01.dbf’ size 50g

You can specify size in kilobytes (k), megabytes (m), gigabytes (g), or terabytes (t).

If the default tablespace type was set to big file at database creation, you are required not to specify the keyword big file in the create tablespace statement. A big file tablespace is created by default.

If the default tablespace type was set to big file at database creation, but you want to create a traditional (small file) tablespace, then specify a create small file tablespace statement to override the default tablespace type for the tablespace that you are creating.

Altering a Big File Tablespace:

There are two clauses of the alter tablespace statement that assist datafile transparency when you are using big file tablespaces:

•Resize: The resize clause lets you resize the single data file in a big file tablespace to absolute size, without referring to the datafile. For instance:

•Alter tablespace big tbs resize 80g;

•Here Auto – extend(used outside of the add datafile clause):

With a bigfile tablespace, you can use the auto-extend clause outside of the add datafile clause. For instance:

Sql>alter tablespace bigtbs autoextend on next 20g;

An error is raised if you specify an add datafile clause for a big file tablespace.

How can Identifying a big file tablespace:

The following views contain a big file column that identifies a tablespace as a big file tablespace:

•Dba_tablespaces

•User_tablespaces

•V$tablespace

You can also identify a big file tablespace by the relative file number of its single datafile. That number is 1024 on most platforms, but 4096 on os/390.

Conclusion:

GoLogica Offers Oracle DBA Online Training Course expects to give you inside and out comprehension of the Oracle Database Administration. You will find out about the Oracle DBA Architecture, different ideas, database structures, memory and process engineering, security, composition objects, information reinforcement, and recuperation.

The Oracle DBA is related to the social database administration framework. The part of the Database Administrator is critical to dealing with the colossal volumes of data in vast ventures. Henceforth Oracle DBA 11g/12c experts are in immense request.

Related Courses