Changes to Tablespaces in Oracle 23ai
Oracle Database 23ai changes the database's default tablespace type to BIGFILE, and new 23ai databases are created with the SYSTEM, SYSAUX, and USER tablespaces using the BIGFILE type by default.
Before exploring the reasoning and ramifications behind this change, let's take a look at the differences between BIGFILE and SMALLFILE tablespaces.
SMALLFILE tablespaces have one or more datafiles or tempfiles, and for many years this was the norm. Before introducing the BIGFILE type in Oracle 10g, there wasn't even a special name—they were just "tablespaces." A BIGFILE tablespace can have only a single datafile or tempfile.
Yet, while a SMALLFILE tablespace can contain many more files (up to 1,022) than a BIGFILE tablespace, BIGFILE tablespaces allow larger block sizes, making them capable of supporting more data. The maximum block size for files in a SMALLFILE tablespace is 4M, while blocks in a BIGFILE tablespace can be as large as 4G.
This impacts the total capacity of an Oracle database, too. The number of datafiles (of any type) allowed in a single database is (typically) 64k; a database using traditional SMALLFILE tablespaces, with a limited number of smaller individual datafiles, can't touch the potential of a database using BIGFILE tablespaces.
Total database size is one advantage of using BIGFILE tablespaces, but databases that aren't testing the boundaries of capacity will realize performance benefits. Reducing the number of datafiles means database administrators can set a lower value for DB_FILES and MAXDATAFILES, with a corresponding reduction in the memory structures in the SGA and the control file section containing datafile records.
BIGFILEs offer simplified management as well. Operations against SMALLFILE tablespaces take place at the datafile level, while BIGFILE tablespaces, associating a single datafile to a tablespace, remove that complexity, with operations performed directly on the tablespace. A BIGFILE tablespace set to AUTOEXTEND will never need datafiles added, either—one less thing for database administrators to
worry about! Queries for calculating space usage are easier, too, and understanding disk use and capacity planning efforts are greatly simplified.
Many people don't realize that BIGFILE is better for databases running Data Guard. When database instances in Data Guard environments experience state changes, Data Guard needs to physically "touch" every datafile before completing its switch- or fail-over operation. There's a measurable (and non-trivial) difference in the time required to switchover a moderately sized database using BIGFILE tablespaces
compared to an otherwise identical database using SMALLFILE tablespaces, spread over a few hundred datafiles.
Limits of BIGFILE Tablespaces
BIGFILE tablespaces are best suited to databases using ASM, or LVM-based storage that supports striping or RAID. Absent these, parallel operations and backups will not run as efficiently or effectively. Some older operating systems and hardware may not be optimized for BIGFILE operations or limit their overall capacity. Fortunately, most hardware used for modern databases—especially those being
considered for 23ai—meet these criteria.
I've heard database administrators offer objections to adopting BIGFILE tablespaces, but the majority of these arguments are the product of the "we've always done it this way" and easily overcome. For example, "RMAN backups of large BIGFILE datafiles take longer." The solution is using SECTION SIZE to split the backup of a single, large datafile across multiple channels.
In older database versions, space released when deleting segments from a large BIGFILE tablespace wasn't easily recoverable. 23ai adds the ability to shrink a BIGFILE tablespace for better storage management.
Practical Use of BIGFILE in 23ai
Databases upgraded from older versions of Oracle to version 23ai will retain their existing tablespace types. Newly created 23ai databases have BIGFILE as the default tablespace type, and users will see the SYSTEM, SYSAUX, and USERS tablespaces created as BIGFILE.
To see what tablespace type is set as the default in a database, run:
col name for a20
col value$ for a10
col comment$ for a25
select *
from props$
where name = 'DEFAULT_TBS_TYPE';
The result in a newly created Oracle 23ai database is:
NAME VALUE$ COMMENT$
-------------------- ---------- -------------------------
DEFAULT_TBS_TYPE BIGFILE Default tablespace type
In this example, new tablespaces will be created as BIGFILE. Creating a SMALLFILE tablespace is still possible, but requires use of the SMALLFILE keyword:
create smallfile tablespace small_ts ...
Conclusion
The shift to BIGFILE tablespaces as the default in Oracle 23ai represents Oracle's commitment to simplifying database management while enhancing performance and scalability. By reducing the complexity of managing datafiles and streamlining operations for modern storage configurations, BIGFILE tablespaces are better suited to meet the demands of today’s data-driven environments.
For DBAs, embracing this change means less time spent on tedious tasks like adding and managing datafiles and more time focusing on higher-value activities like performance optimization and strategic planning. Whether you're working with cutting-edge storage systems or legacy infrastructure, Oracle 23ai provides the flexibility to tailor your tablespace strategy to fit your needs.
As always, it’s exciting to see Oracle delivering features that empower us to work smarter, not harder. BIGFILE tablespaces may seem like a small change on the surface, but the cumulative impact of simplified management, improved performance, and reduced operational overhead will undoubtedly be felt by organizations of all sizes.
Happy Holidays! 🎄
SUBMIT YOUR COMMENT