What are "Bigfile" tablespaces and/or can I have a datafile greater than 32GB?

Products

• Data Loss Prevention Enforce
• Data Loss Prevention Oracle Standard Edition 2

Issue/Introduction

Either you are interested in making data files larger than 32 gigabytes or you've encountered a database with a datafile bigger than 32 gigabytes and are wondering how is that possible.

Environment

Since 10g, Oracle introduced the ability to define tablespaces as having “smallfiles” or “bigfiles”.

Resolution

Use of Bigfile tablespaces is not certified by Symantec DLP.

Smallfiles are the default and are limited by block size.  Since we build a DB with 8k block size, the maxfilesize is 32GB. Each tablespace is limited to 1023 datafiles (OS limitation). This puts a limit of 32 TB on each tablespace.

Bigfile Tablespaces are bigger, shall we say, in that a single datafile may contain larger blocks and more of them (32kB blocks size maximum). 

• A Bigfile tablespace may have one and only one datafile
• That file must be managed using Automated Segment Space Management (ASSM) and needs to be striped or RAIDed across multiple disks
• The file must be locally managed

Different block sizes have different max file sizes, and, logically, the larger the block size, the larger the max database size, effectively limited only by hardware and budget.