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 bigfile 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 bigfile tablespace in oracle with 8k blocks can contain a 32 terabyte datafile. A big file tablespace with 32k blocks can contain a 128 terabyte datafile. The maximum number of datafiles 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 datafiles required for a database. An additional benefit is that the db_files initialization parameter and maxdatafiles 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 bigfile tablespaces in oracle on a system that does not assist striping because of negative implications for parallel query results and rman backup parallelization.
•Using bigfile 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 required not, specify extent management local and segmentspacemanagementauto in this statement.
However, the database returns an error if you specify 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 datafile. For instance:
Sql>create bigfile tablespace bigtbs
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 required not 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 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.
