DBA Hub

📋Steps in this guide1/3

Bigfile Tablespace Defaults in Oracle Database 23ai/26ai

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

oracle 23configurationintermediate
by OracleDba
22 views
1

BIGFILE Default for SYSAUX, SYSTEM, and USER Tablespaces

In Oracle database 23ai/26ai 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. We switch to a pluggable database and display the tablespaces for this container. This is similar, but the tablespace in the PDB is a smallfile tablespace. This is the default behaviour in this release of 23ai/26ai Free.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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>

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

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. Notice the new tablespace has been created as a bigfile tablespace.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create tablespace new_ts datafile size 2g;

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

Considerations

These new defaults were introduced in 23.4. if I run the same tests in 23.3 I get the following. I've been told some of the 23ai/26ai 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: - Tablespace File Size - Bigfile Tablespaces Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!