Learning is not a spectator sport

In my previous post I talked about BIGFILE tablespaces and described how you could (using DBCA or manual scripts) build a CREATE DATABASE command that would result in the SYSTEM and SYSAUX tablespaces being BIGFILE by making it a database-wide default.

Then I concluded with the following teaser:

“if you are using any of our smallest databases (Express Edition, Developer Free) then using a default of BIGFILE might be the best decision you ever make.”

This post is to explain that last statement. I’ll use the term “XE” from now on, which covers both Express Edition and Developer Free, because the same rationale applies to each.

If you’ve ever run close to the size limit on XE, you’ll know that if any file grows to over 12GB, then (by definition) we know you have hit the size limits of Express Edition. You can fill that datafile with whatever data you want, but you cannot increase the size of that file or add any new files to the database.

But as I mentioned in another post, to provide you with as close to a full Enterprise Edition experience as possible, Express Edition comes with just about all of the “bells and whistles” available in our flagship product. This means that when you configure XE, we’ll be putting a lot of components into the SYSAUX tablespace, eg

• Some database options (XMLDB, Spatial, Workspace Manager, Text, OLAP etc)
• Job scheduler/task maintenance
• Audit trails
• SQL performance managements facilities
• Workload Repository
• Optimizer history
• APEX default installation

and so on. Also in that post, I took the combination of XE file size limits and SYSAUX component growth to its logical conclusion, namely, if SYSAUX gets to 12G you might end up “dead in the water”. This is because a number of tables in the SYSAUX tablespace contain columns of data type LONG, which means the following show-stopping issue if you try to re-shuffle elements in the tablespace to free up space

SQL> conn /@xepdb1 as sysdba
Connected.

SQL> alter table sys.SQLOBJ$PLAN move;
alter table sys.SQLOBJ$PLAN move
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

I then described how to best prevent this issue with a number of steps you should undertake at database creation time (separate tablespaces for audit data, APEX, etc) but it is all well and good for me to be saying “prevention is better than cure” when there is NO cure if you get into scenario where your XE space is exhausted. 😁

Enter a new feature in 23ai – Shrink a Tablespace! Killian Lynch described the functionality nicely here so I won’t repeat all of the details, but here is the most critical part of the feature as it pertains to XE

SQL> conn sys/admin@db23 as sysdba
Connected.
SQL> set serveroutput on;
SQL> alter session set events '10613 trace name context forever, level 1';

Session altered.

SQL> begin
  2    dbms_space.tablespace_shrink('SYSAUX',
  3       shrink_mode => dbms_space.ts_mode_analyze);
  4  end;
  5  /
Procedure exited because it encountered error:
ORA-20000: Tablespace name is not valid or it isn't bigfile tablespace
ORA-06512:
at "SYS.DBMS_SPACE", line 5821

begin
*
ERROR at line 1:
ORA-20000: Tablespace shrink exited with error
ORA-06512: at "SYS.DBMS_SPACE", line 9039
ORA-06512: at "SYS.DBMS_SPACE", line 9055
ORA-06512: at line 2

For tablespace shrink to be possible, the tablespace to be shrunk must be a BIGFILE tablespace.

So has the penny dropped yet?

Now hopefully you can see why every tablespace in your XE installation should be a BIGFILE tablespace. Because once they are, every time any of your tablespace datafiles gets close to 12GB in size and there is free space to be reclaimed, you can use the new tablespace shrink feature to reclaim that space and reduce the size of the datafile. Every single valuable byte of your 12GB can be put to its best use!

And yes, it even works for tablespaces that have tables with LONG columns, which means you also can shrink SYSAUX!

SQL> set serveroutput on 
SQL> alter session set events '10613 trace name context forever, level 1';

Session altered.

SQL> begin
  2    dbms_space.tablespace_shrink('SYSAUX',
  3       shrink_mode => dbms_space.ts_mode_analyze);
  4  end;
  5  /
[get_ts_number] tablespace number: 1
[get_ts_number] tablespace number: 1
[get_ts_file_size] tablespace file size: 246415360
[get_additional_blocks] rows number of AUD$UNIFIED SYS_P561: 1522
[additional_blocks] addtional blocks for AUD$UNIFIED SYS_P561: 14
[get_largest_obj_blocks] blocks of the largest object: 800
[get_ts_used_blocks] current usage of the tablespace: 23544
[get_ts_number] tablespace number: 1
[get_ts_total_blocks] total blocks of the tablespace: 30080
[get_ts_used_blocks] current usage of the tablespace: 23544
[get_free_blocks] target block id: -1
[get_free_blocks] free blocks: 5760
[get_ts_meta_blocks] meta of the specified tablespace: 776
[get_ts_number] tablespace number: 1
[get_ts_total_blocks] total blocks of the tablespace: 30080
[max_shrink_target_block] extra blocks: 800
[max_shrink_target_block] target blocks: 25120
[tablespace_shrink] target block id: 25120

[get_free_blocks] target block id: 25120
[get_free_blocks] free blocks: 800
[get_additional_blocks] rows number of AUD$UNIFIED SYS_P561: 1522
[additional_blocks] addtional blocks for AUD$UNIFIED SYS_P561: 14
[get_largest_obj_blocks] blocks of the largest object: 800
[get_ts_used_blocks] current usage of the tablespace: 23544
[get_ts_number] tablespace number: 1
[get_ts_total_blocks] total blocks of the tablespace: 30080
[get_ts_used_blocks] current usage of the tablespace: 23544
[get_free_blocks] target block id: -1
[get_free_blocks] free blocks: 5760
[get_ts_meta_blocks] meta of the specified tablespace: 776
[get_ts_number] tablespace number: 1
[get_ts_total_blocks] total blocks of the tablespace: 30080
[max_shrink_target_block] extra blocks: 800
[max_shrink_target_block] target blocks: 25120
Suggested Target Block: 25120
-------------------ANALYZE RESULT-------------------
Total Movable Objects: 0
Total Movable Blocks: 0
Total Movable Size(GB): 0
Orginal Datafile Size(GB): .23
Suggested Target Size(GB): .19
Process Time: +00 00:00:01.094918

PL/SQL procedure successfully completed.

So the smaller your database has to be, the bigger the need for BIGFILE !

Addenda Feb 2025: Woo hoo! In 23.7, the restriction on BIGFILE for shrink tablespace has been lifted!