8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 23 » Here

Bigfile Tablespace Defaults in Oracle Database 23ai

Bigfile tablespaces were introduced in Oracle 10g. In Oracle database 23ai bigfile tablespaces are the default.

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...

Back to the Top.