In the physical design of an Oracle Database, the choice of tablespace management method directly impacts future scalability. Correctly understanding the differences between Oracle Smallfile and Bigfile is the first step toward efficient storage operations. This article compares the technical specifications of Oracle Bigfile tablespaces with the advantages of traditionally used Smallfile tablespaces and provides a thorough explanation of the optimal use cases in practice.
Conclusion and Quick Steps
The choice depends on the scale of the data being handled and the storage management policy (such as the presence of ASM).
- Bigfile tablespace: Managed with a single massive file. Ideal for Very Large Databases (VLDB) or when you want to simplify file management.
- Smallfile tablespace: Managed with multiple files. Ideal for standard-sized databases or when you need to physically distribute files across different disks.
Background and Fundamentals: Two Management Methods
Since Oracle Database 10g, two types of tablespace management have been provided based on how datafiles are handled.
- Smallfile tablespace: The traditional Oracle format. A single tablespace can contain up to 1,022 datafiles.
- Bigfile tablespace: A tablespace contains exactly one datafile. In exchange, it supports massive sizes at the TB scale for that single file.
Quick Note for Beginners
Imagine traditional Smallfile as “filling a bucket with many small stones (files),” while Bigfile is like “the bucket itself being one giant rock (file).”
Differences Between Smallfile and Bigfile: A Thorough Comparison
Maximum sizes and expansion practices vary significantly depending on the tablespace type. The following is a comparison of specifications for an 8KB block size.
| Item | Smallfile Tablespace | Bigfile Tablespace |
|---|---|---|
| Number of Files | Max 1,022 | Exactly 1 |
| Max Size per File | Approx. 32 GB ($2^{22}$ blocks) | Approx. 32 TB ($2^{32}$ blocks) |
| Max Tablespace Size | Approx. 32 TB | Approx. 32 TB |
| Capacity Expansion | ADD DATAFILE or RESIZE | RESIZE only |
| Management Complexity | Increases as file count grows | Extremely simple |
Note: Maximum size depends on the block size. With a 32KB block size, Bigfile can expand up to 128TB.
Implementation: SQL Examples for Creation and Modification
Here are basic SQL command examples that work in an actual environment.
1. Creating a Bigfile Tablespace
Used to automate file management and secure a single massive area.
-- Privileges: SYSDBA or CREATE TABLESPACE
-- Default in 26ai and later, but explicit specification is required in 19c and earlier
CREATE BIGFILE TABLESPACE TS_BIG_DATA
DATAFILE '/u01/app/oracle/oradata/ORCL/ts_big_data01.dbf' SIZE 10G
AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
[Explanation] Use the BIGFILE keyword explicitly. By specifying MAXSIZE UNLIMITED, the single file can grow to the limits of the disk capacity.
2. Creating a Smallfile Tablespace (Traditional Format)
Used when distributed placement across multiple disks is required or when managing as an Oracle Smallfile tablespace.
-- Create by explicitly specifying SMALLFILE
CREATE SMALLFILE TABLESPACE TS_SMALL_DATA
DATAFILE '/u01/app/oracle/oradata/ORCL/ts_small01.dbf' SIZE 1G,
'/u02/app/oracle/oradata/ORCL/ts_small02.dbf' SIZE 1G
AUTOEXTEND ON;
[Explanation] Multiple files can be defined simultaneously using comma separation, allowing storage destinations to be separated at the OS level across different disks.
3. Handling Capacity Shortages (RESIZE)
Since you cannot add datafiles to a Bigfile tablespace, you must resize the existing file.
-- Directly expand the data size of a Bigfile tablespace
ALTER TABLESPACE TS_BIG_DATA RESIZE 20G;
Operational and Security Notes
Pros
- Bigfile: As the number of datafiles decreases, information within the control file is reduced, leading to memory savings in the SGA and potentially faster checkpoint processing.
- Smallfile: Offers the flexibility to expand a tablespace by adding files to different disk areas even if one mount point becomes full.
Cons and Pitfalls
- Bigfile: Once a single file reaches several TB, handling it with standard OS commands like
cportarbecomes difficult. Furthermore, the file system must support large-scale files. - How to revert: You cannot convert types (Small ⇔ Big) after a tablespace has been created. To change the type, you must create a new tablespace and migrate data using tools like Data Pump.
FAQ
Q: Is there a difference in performance? A: There is almost no difference in the speed of data access itself. However, in environments with many files, using Bigfile may reduce management process overhead.
Q: Which one should I use if I’m unsure? A: If you are using ASM (Automatic Storage Management) where striping is handled automatically, Bigfile is highly recommended for easier management.
Q: What if I need more than 32TB of data in Bigfile? A: You must either increase the block size (to 16KB or 32KB) or create another Bigfile tablespace to distribute the data.
Summary
- Oracle Smallfile tablespaces are the flexible, traditional type composed of multiple files.
- Oracle Bigfile tablespaces are the modern type that simplifies management with a single massive file.
- Since Bigfile is the default in the latest 26ai, Bigfile operations will become the mainstream moving forward.
- The type cannot be changed once created. Determine future data volumes during the design phase.
This article focuses on Oracle Database 19c (default values may differ in the latest versions such as 26ai).
コメント