I was doing some 23ai test to write an article in my blog and found this strange behavior.
According to documentation, BIGFILE is default in Oracle 23ai:
Starting with Oracle Database 23ai, the SYSTEM, SYSAUX, and USER tablespaces are created as bigfile tablespaces by default.
If I check the tablespace type on an Oracle 23ai autonomous database, all tablespaces were created as BIGFILE, as expected since it is the default in Oracle 23ai:
I then created a DB System with Oracle 23ai, and when I checked the tablespaces types they were not BIGFILE:
I connected to the PDB, created a tablespace without specifying BIGFILE and checked the results:
Same as on the CDB, everything was like in previous versions. I created a tablespace using the BIGFILE keyword (theoretically not required anymore in 23ai) and it was created OK:
I checked the DEFAULT_TBS_TYPE database property and it is not set to BIGFILE, that I understand is the default in 23ai:
When I check the 23ai CREATE TABLE documentation it says the default is SMALLFILE…
SET DEFAULT TABLESPACE Clause
Use this clause to determine the default type of subsequently created tablespaces and of the SYSTEM and SYSAUX tablespaces. Specify either BIGFILE or SMALLFILE to set the default type of subsequently created tablespaces as a bigfile or smallfile tablespace, respectively.
A bigfile tablespace contains only one data file or temp file, which can contain up to approximately 4 billion (232) blocks. The maximum size of the single data file or temp file is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.
A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 data files or temp files, each of which can contain up to approximately 4 million (222) blocks.
If you omit this clause, then Oracle Database creates smallfile tablespaces by default.
So anyone can confirm which one is the default?
ADDED:
Found this article from Tim Hall mentioning that in 23ai the DEFAULT for ALL TABLESPACES is BIGFILE, but It does not mention exact documentation, and the tests seems to be performed on 23ai Free Edition. Nevertheless, is kind of strange that Free Edition has different behaviour than Standard/Enterprise related to bigfile, more considering it only allows 12 Gb of data….