8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Bigfile Tablespace Defaults in Oracle Database 23ai
Bigfile tablespaces were introduced in Oracle 10g. In Oracle database 23ai bigfile tablespaces are the default.
- BIGFILE Default for SYSAUX, SYSTEM, and USER Tablespaces
- BIGFILE Default for User Defined Tablespaces
- Considerations
Related articles.
BIGFILE Default for SYSAUX, SYSTEM, and USER Tablespaces
In Oracle database 23ai most of the Oracle provided tablespaces are bigfile tablespaces by default.
We connect to the root container and check out the tablespaces for this container. All are bigfile tablespaces excluding the TEMP tablespace.
conn / as sysdba select tablespace_name, bigfile from dba_tablespaces order by 1; TABLESPACE_NAME BIG ------------------------------ --- SYSAUX YES SYSTEM YES TEMP NO UNDOTBS1 YES USERS YES SQL>
We switch to a pluggable database and display the tablespaces for this container. This is similar, but the USERS
tablespace in the PDB is a smallfile tablespace. This is the default behaviour in this release of 23ai Free.
alter session set container=freepdb1; select tablespace_name, bigfile from dba_tablespaces order by 1; TABLESPACE_NAME BIG ------------------------------ --- SYSAUX YES SYSTEM YES TEMP NO UNDOTBS1 YES USERS NO SQL>
BIGFILE Default for User Defined Tablespaces
Bigfile is the default file size when creating a new tablespace. In the following example we create a new tablespace without explicitly setting the bigfile/smallfile file size. We are using Oracle Managed Files (OMF), so we don't need to manually name the associated datafile.
create tablespace new_ts datafile size 2g;
Notice the new tablespace has been created as a bigfile tablespace.
select tablespace_name, bigfile from dba_tablespaces order by 1; TABLESPACE_NAME BIG ------------------------------ --- NEW_TS YES SYSAUX YES SYSTEM YES TEMP NO UNDOTBS1 YES USERS NO SQL>
Considerations
These new defaults were introduced in 23.4. if I run the same tests in 23.3 I get the following.
conn / as sysdba select tablespace_name, bigfile from dba_tablespaces order by 1; TABLESPACE_NAME BIG ------------------------------ --- SYSAUX NO SYSTEM NO TEMP NO UNDOTBS1 NO USERS NO SQL> alter session set container=pdb1; select tablespace_name, bigfile from dba_tablespaces order by 1; TABLESPACE_NAME BIG ------------------------------ --- SYSAUX NO SYSTEM NO TEMP NO UNDOTBS1 NO USERS NO 6 rows selected. SQL> create tablespace new_ts datafile size 2g; select tablespace_name, bigfile from dba_tablespaces order by 1; TABLESPACE_NAME BIG ------------------------------ --- NEW_TS NO SYSAUX NO SYSTEM NO TEMP NO UNDOTBS1 NO USERS NO SQL>
I've been told some of the 23ai cloud services have different defaults in this behaviour. I suspect this is because of different versions of the software at the point of the image creation they are based on. I would expect future release updates to result in all tablespaces, including TEMP, to be bigfile when a clean installation is performed.
For more information see:
Hope this helps. Regards Tim...