Improved defaults in 12c

Just learnt a new thing in 12c and this relates to improved defaults in 12c database and the below quote is from docs

<quote>
If you specify the DEFAULT clause for a column, then the default value is stored as metadata but the column itself is not populated with data.
</quote>

Oracle 11g has the improved way of adding DEFAULT value to NOT NULLABLE column, but 12c has improved way for NULLABLE columns too.

 rajesh@PDB1> exec show_space('BIG_TABLE');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               8
Full Blocks        .....................           1,713
Total Blocks............................           1,768
Total Bytes.............................      14,483,456
Total MBytes............................              13
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              11
Last Used Ext BlockId...................           3,328
Last Used Block.........................              40

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.19
rajesh@PDB1>
rajesh@PDB1>
rajesh@PDB1> alter table big_table add x char(2000) default 'x' ;

Table altered.

Elapsed: 00:00:00.04
rajesh@PDB1> exec show_space('BIG_TABLE');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               8
Full Blocks        .....................           1,713
Total Blocks............................           1,768
Total Bytes.............................      14,483,456
Total MBytes............................              13
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              11
Last Used Ext BlockId...................           3,328
Last Used Block.........................              40

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16
rajesh@PDB1>

so the table size remains 13MB even after adding  a new column of 2000 bytes, but in 11g size increases from 13MB to 264MB.

rajesh@ORA11GR2>
rajesh@ORA11GR2> exec show_space('BIG_TABLE');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................           1,525
Total Blocks............................           1,664
Total Bytes.............................      13,631,488
Total MBytes............................              13
Unused Blocks...........................             105
Unused Bytes............................         860,160
Last Used Ext FileId....................               5
Last Used Ext BlockId...................          54,912
Last Used Block.........................              23

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table big_table add x char(2000) default 'x' ;

Table altered.

Elapsed: 00:01:41.17
rajesh@ORA11GR2> exec show_space('BIG_TABLE');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................          31,808
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................             223
Full Blocks        .....................           1,525
Total Blocks............................          33,792
Total Bytes.............................     276,824,064
Total MBytes............................             264
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               5
Last Used Ext BlockId...................          86,656
Last Used Block.........................           1,024

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.38
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table big_table add y char(2000) default 'y' not null ;

Table altered.

Elapsed: 00:00:00.25
rajesh@ORA11GR2> exec show_space('BIG_TABLE');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................          31,808
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................             223
Full Blocks        .....................           1,525
Total Blocks............................          33,792
Total Bytes.............................     276,824,064
Total MBytes............................             264
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               5
Last Used Ext BlockId...................          86,656
Last Used Block.........................           1,024

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.38
rajesh@ORA11GR2>