Learning is not a spectator sport

Space management used to be hard

Way back in the good old days…In fact, before I continue, rest assured that whenever a database person says “good old days” what they really mean is a time totally warped by their imagination where they have managed to block out all of the horror stories and problems they faced, to only remember the tiny nugget of positivity about some antiquated version of the database, so that they come to you and say … “Way back in the good old days” . But I digress…

Way back in the good old days, things were actually not anywhere near as good as things are today when it came to tablespaces and datafiles. In the early versions of Oracle 7, you picked a size for your datafiles and that was pretty much it. If your tablespaces got close to full, you could manually resize the datafiles or add new ones, but that was 100% your job to keep an eye on things. As a consequence, every DBA of that generation had a script called something along the lines of “freespace.sql” which rang the alarm bell whenever a tablespace got above 80% full, because you didn’t want to be that person that forgot to allocate space in your database before going home for the evening.

Autoextend and automatic space management

Eventually we got autoextend datafiles which meant that as long as our datafiles were not close to their absolute limit, we had a much greater chance of getting an uninterrupted nights sleep! But even autoextend had its issues if it was the crutch that you were leaning on in terms of ensuring consistent application performance.

It obviously takes time to add tens or hundreds of a megabytes to a file, and thus, if your single row transaction was the unfortunate soul that took the datafile over its current size threshold, then you would sit there for precious seconds whilst your datafile grew to its new size.

At the time, this was often the cause of bluster and bravado from DBAs in the industry proudly beating their chest with: “REAL admins do not need to autoextend. We just manage our databases properly”. Of course, this was complete tosh because not using the features available to you is less proper management and more revealing yourself to be a fool  

Many Oracle practitioners still worry about this pause in throughput as a file needs to autoextend, but only because there was never not a lot of publicity around a feature that arrived in Oracle 9, namely the SMC0 process (Space Management Coordinator). It did precisely what DBAs were doing manually with their own scripts, namely,

• keep an eye on the rate of growth of segments in the database,
• based on that rate, initiate an increase the size of datafiles before they truly needed to autoextend.

If you’re unfamiliar with the SMC0 process, MOS Doc ID 743773.1 is good reference, or check the docs.

Empty tables are not always empty

But enough background…Even with autoextend, manual checks, and SMC0, if you have bursty or sporadic growth, there may come a moment when a datafile cannot grow any further and you hit the familiar “ORA-01653: unable to extend table .. in tablespace…” error when you were not expecting it. If you are in the situation where you have bursts of growth, it is important to realise that once you get one occurrence of ORA-01653, you might continue to get ORA-01653 errors, even if the transaction iniated the growth fails and rolls back.

Lets look at an example of this. I’ll create a 1GB tablespace and for the sake of covering the autoextending case as well, you can assume it could just as easily have started as a 500MB file and has since autoextended out to its maximum size of 1GB.

SQL> create tablespace demo datafile 'X:\ORACLE\ORADATA\DB19\PDB1\DEMO.DBF' size 1000m;

Tablespace created.


SQL> create table t ( pk int, c varchar2(4000) ) tablespace demo  ;

Table created.

Now I am going to load up this table T with far too much data than the tablespace can accommodate.

SQL> insert /*+ APPEND*/ into t
  2  select rownum, rpad(rownum,4000)
  3  from dual
  4  connect by level <= 1000000;
insert /*+ APPEND*/ into t
*
ERROR at line 1:
ORA-01653: unable to extend table MISTER.T by 1024 in tablespace DEMO

So my table T failed to load any data, and thus is still empty. But let us now look at the state of the tablespace.

SQL> @free DEMO

TABLESPACE_NAME          TOT_SIZE                                 TOT_FREE               PCT_USED
------------------------ ---------------------------------------- -------------------- ----------
DEMO                          1000m                                       7m                   99

1 row selected.

It’s space is still completely exhausted. By looking at DBA_EXTENTS you can see why this is the case.

SQL> select count(*)
  2  from   dba_extents
  3  where  segment_name = 'T'
  4  and    owner = user;

  COUNT(*)
----------
       195

The table T, which failed to load any rows, still allocated extents as the load progressed. Those allocations are performed by recursive dictionary transactions, which you can think of as being synonymous with autonomous transactions in PL/SQL. They run “separately” from the main loading transaction and thus are not rolled back when the transaction fails. My table T is empty, but it is still 1GB in size. We can safely assume that any activity that requires space in this tablespace is now going to fail as well.

Since we were loading table T from an empty state, there is a simple fix here. I simply truncate the table to release all of its extents back to the free space for the tablespace.

SQL> truncate table t;

Table truncated.

SQL> @free DEMO

TABLESPACE_NAME          TOT_SIZE                                 TOT_FREE               PCT_USED
------------------------ ---------------------------------------- -------------------- ----------
DEMO                          1000m                                     999m                    0

1 row selected.

When truncate cannot be used

But what if the table was not empty before starting the failed load transaction? For example, what if I had already successfully loaded 100 rows into the table as below:

SQL> insert /*+ APPEND*/ into t
  2  select rownum, rpad(rownum,4000)
  3  from dual
  4  connect by level <= 100;

100 rows created.

SQL> commit;

Commit complete.

and then I’d attemped a much bigger load and hit the error.

SQL> insert /*+ APPEND*/ into t
  2  select rownum, rpad(rownum,4000)
  3  from dual
  4  connect by level <= 1000000;
insert /*+ APPEND*/ into t
*
ERROR at line 1:
ORA-01653: unable to extend table MISTER.T by 1024 in tablespace DEMO

Obviously a truncate command is off the table (… see what I did there? ). Do I need to perform a complete reorganisation of the table in order to reclaim the space? Fortunately not. A command that has long been available in the Oracle Database but often forgotten by DBAs is that we can prune extents from a table if those extents contain no data. The DEALLOCATE UNUSED will free up those extents that do not contain any data whilst not touching any extents that are in “genuine” use.

SQL> alter table t deallocate unused;

Table altered.

My table data is preserved, and I’ve now freed those extents back to the tablespace.

SQL> select count(*) from t;

  COUNT(*)
----------
       100

1 row selected.

SQL> @free DEMO

TABLESPACE_NAME          TOT_SIZE                                 TOT_FREE               PCT_USED
------------------------ ---------------------------------------- -------------------- ----------
DEMO                          1000m                                     998m                    0

1 row selected.

TL;DR: If you are doing data loads in bursts and hitting the ORA-01653 error, you might be able to reclaim space without expensive reorganisations by using the ALTER TABLE DEALLOCATE UNUSED command.

Here’s some related information from a video session I did on AUTOEXTEND