A bigfile tablespace in oracle is a tablespace with a single, but very large (up to 4g blocks) datafile. Traditional small file tablespaces, in contrast, can contain multiple datafiles, but the files cannot be as large. The benefits of big file tablespaces are the following:
Want To Get DBA Training From Experts? Enroll Now For Free Demo On Oracle DBA Online Certification Training.
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment space management, with three exceptions: locally managed to undo tablespaces, temporary tablespaces, and the system tablespace.
To create a big file tablespace, specify the bigfile keyword of the create tablespace statement (createbigfiletablespace …). Oracle Database automatically creates a locally managed tablespace with automatic segment space management.
You can, but need not, specify extent management local and segmentspacemanagementauto in this statement.
However, the database returns an error if you specify extentmanagementdictionary or segmentspacemanagementmanual.
The remaining syntax of the statement is the same as for the create tablespace statement, but you can only specify one datafile. For example:
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 need 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 createsmallfiletablespace statement to override the default tablespace type for the tablespace that you are creating.
[Related Article: Oracle DBA Interview Questions]
Two clauses of the alter tablespace statement support datafile transparency when you are using big file tablespaces:
With a bigfile tablespace, you can use the auto-extend clause outside of the add datafile clause. For example:
Sql>alter tablespace bigtbs autoextend on next 20g;
An error is raised if you specify an add datafile clause for a big file tablespace.
Identifying a big file tablespace:
[Related Article: Oracle DBA Tutorial]
The following views contain a big file column that identifies a tablespace as a big file 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.
Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!
Ravindra Savaram is a Content Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.