How Oracle uses Space, sort of.

Space. The Final Frontier.
My long-suffering Mrs does enjoy a bit of sci-fi especially if some hunky all-action type is wandering around with his shirt off.
“That man has such a nice personality”, she may well sigh, staring dreamily at the screen.

As with any software Oracle error messages can look as if they’ve been put together in some alien language.
This is especially true if your fairly new to Oracle.
When you get space errors in Oracle, the answer is not necessarily to simply add more space.

What we’re going to look at here is :

  • what a tablespace is and the various things they are used for
  • how redo logs work ( and how they are archived)
  • some of the space related errors you may encounter and what the underlying causes may be

Of necessity, I’ve made some generalisations here. The purpose of this post is not to provide an in-depth technical guide to the inner workings of Oracle. Rather it is to provide enough information for you to work out whether you should be looking up the phone number for your hard-pressed DBA, or looking at that bit of code you’ve just run.

Also, like the author, this post is a bit short of cache. For the sake of simplicity (and that weak pun), I’m going to pretend that Oracle uses memory in one amorphous lump.
Additionally, I’ve not taken into consideration Direct Path Inserts.


The “it’ll do for now” definition of a Tablespace is that it’s a collection of one or more datafiles.
Datafiles are the physical operating system files that the database uses to store persistent data…among other things.

There are, in fact, three types of tablespace. You can see this (if you have the appropriate permissions in the database) by running the following query :

SELECT tablespace_name, contents
FROM dba_tablespaces

Run this and you’ll get something like :

------------------------------ ---------

Permanent Tablespaces

This is the type that you’d characterise as normal. These tablespaces are used to store the segments (tables and indexes mainly) that hold your data.

The other thing to bear in mind here is that data is stored in blocks. These are (by default) 8k in size.

SELECT last_name, shirt_colour
FROM crew_members
WHERE starship_name = 'ENTERPRISE'

This query will retrieve a block of data from the CREW_MEMBERS table, that’s stored in a Permanent Tablespace.
So far, so simple.

Database users that have privileges to create tables may be allocated a quota of space in a tablespace to hold the segments they create.

Temporary Tablespaces

You can think of a Temporary Tablespace as being a bit like Virtual Memory – i.e. an area of disk used for what would normally be in-memory operations.

These tablespaces are used mainly for :

  • sorting operations that are too big to be done in physical memory
  • holding data stored in Global Temporary Tables

This brings us to the vexed question of what is a temporary table ?
Consider the following :

CREATE TABLE extras_tmp(
    last_name VARCHAR2(50),
    shirt_colour VARCHAR2(30));
INSERT INTO extras_tmp(last_name, shirt_colour)

INSERT INTO extras_tmp(last_name, shirt_colour)


-- Doing some processing followed by....
-- surprise Klingon attack !
DROP TABLE extras_tmp;

In the context of this session, the EXTRAS_TMP table is temporary. It’s created, populated and then destroyed all within the same session. However, to Oracle, this is a permanent segment. The table and it’s data will be held in a Permanent tablespace.
Any data changes will be written to the Redo Logs (more of which shortly).
In contrast a Global Temporary table is created once and it’s structure persists across sessions. However, the data within that table is held in the Temporary tablespace and is private to the session in which it was created.

If you are going to use temporary tables in Oracle, then a Global Temporary table is worthy of serious consideration.

The syntax is :

    last_name VARCHAR2(50),
    shirt_colour VARCHAR2(30))

This one time operation creates the table. As soon as the transaction is completed, the data is removed from the table.
Alternatively, if we want the data in this table to persist for the life of the session then we can define it as follows :

    last_name VARCHAR2(50),
    shirt_colour VARCHAR2(30))

This means that our Surprise Klingon Attack program would be more like this :

-- No need to create the table as it already exists - just start using it
INSERT INTO extras_tmp(last_name, shirt_colour)

INSERT INTO extras_tmp(last_name, shirt_colour)

-- Assuming we've specified PRESERVE ROWS in the table creation
-- this will keep the records we've inserted into the table.

-- Doing some processing followed by....
-- those Klingons really are sneaky
TRUNCATE TABLE extras_tmp;

There’s a good explanation of Global Temporary Tables here.

Undo Tablespaces and Database Transactions

This is where it gets a bit more involved.
Undo tablespaces are used to store data blocks that hold records that have been changed by an ongoing transaction.
These blocks are used to ensure read consistency across the database.
The blocks are held in what, in the olden days, used to be called Rollback Segments. In modern parlance, they are also referred to as Undo Segments.

Reading that last bit, you may well be thinking something along the lines of “The engines canna take it !”
Perhaps I should take a step back and explain.

In SQL terms, a transaction is a series of statements that may change some data ( INSERT, UPDATE, DELETE).
In some RDBMSs, such statements are automatically commited as soon as they are completed. Oracle is different in this regard.
The default behaviour is to wait for an explicit termination of the transaction by use of either the COMMIT or ROLLBACK keywords, or by the execution of a DDL statement (e.g. CREATE/DROP/ALTER/TRUNCATE).

Essentially, you need to tell the database either that you are sure that you want to save your changes (COMMIT), or no, you’ve made a horrible mistake and would like to take it back (ROLLBACK).
Whilst you’re making your mind up or – more likely – making a series of related changes, Oracle needs to ensure that these changes are visible only to you. All other database sessions will see the data before you started changing it.

This is achieved by processing requests from any other sessions for data that you are changing to the Undo tablespace.
By contrast, your session will be looking at this data in an area of memory ( remember, nothing has been written to disk at this point).

So, Deb has a session running and issues the following statement :

UPDATE crew_members
SET shirt_colour = NULL
WHERE starship_name = 'ENTERPRISE'
AND last_name = 'KIRK'

In another session, I issue the query :

SELECT shirt_colour
FROM crew_members
WHERE starship_name = 'ENTERPRISE'
AND last_name = 'KIRK'

My query returns the result YELLOW rather than NULL. This is because my query is retrieving the data from the Undo tablespace.
I will only be able to see the results of Deb’s change once she’s issued a COMMIT in her session.
If you want to understand more about transactions in Oracle, have a look here.

Redo Logs and Archiving

There’s nothing more frustrating than doing loads of work and then losing it because of a crash.
Oracle attempts to protect you from this by backing up your work. The mechanism used for this are the Redo Log files.
An Oracle database will have at least two redo log files. Usually it will have more, and they’ll be in groups, but I’m trying to keep it simple here so, let’s pretend.
As you make changes to the data in the database ( or, commit your transactions if you want to be a bit more precise), they will be copied to one of the redo logs.
Once the redo log file is full, the database will start writing to the second redo log file.
This process continues going around in a circular fashion. When the second redo log file is full, Oracle will start overwriting the contents of the first redo log with the latest transaction data.
This presents something of a problem. The transactions in the redo log only persist until Oracle gets around to writing to it again. The data is only held for a short time which means that your options in terms of recovering from a crash are a bit limited.
To solve this problem, Oracle can run in archivelog mode. This is pretty much standard for Production systems.
What happens here is that, as the database finishes writing to a redo log and switches to the next one, the now full redo log file is copied to another area (usually) on disk.
This mechanism is fundemental to the Oracle Database’s ability to recover to any point in time since the last full backup was taken.

The Dilithium matrix is destabilising…and other Space Errors

In practical terms, we can apply this knowledge to help diagnose what’s going on when we get some of the more common Oracle space related errors.
Many of these errors have quite similar wording, so it’s worth reading them carefully and taking note of the error number reported.

An important point to note here is that, whilst the solution to many of these problems may be to add more space, you should first review the code that you are running at the time the error occurs.
Is it using an unusually large amount of space ? Are you generating large volumes of data changes between commits ?
Should this be happening ?

NOTE – in the following section “%s” is used in the error messages as a placeholder for the string that may appear when the error actually occurs.

ORA-01536 – Space quota exceeded for tablespace %s

This error occurs when a user is trying to add some data to a segment owned by them in a tablespace where they’ve already used all of the space allocated to them in that tablespace.

The solution here would involve either clearing down data in their segments in that tablespace, or asking the DBA to increase their space quota on that tablespace.
You can see what quotas you’ve been assigned (if any) by running the following query :

SELECT tablespace_name, 
    bytes as bytes_used, 
    blocks as blocks_used,
    max_bytes as quota_in_bytes,
    max_blocks as quota_in_blocks
FROM user_ts_quotas;

ORA-01653 – unable to extend table %s.%s by %s in tablespace %s

There is no more space available in this tablespace. Once again, you either need to clear down some data that you no longer need or get the DBA to add some space.

ORA-01652 – unable to extend temp segment by %s in tablespace %s

At first sight, this looks almost identical to the previous error. However, the cause is quite different.
Essentially, there’s not enough space available in the temporary tablespace (Virtual Memory Area, remember) to process the operation you are executing.
As the Temp tablespace is shared across all sessions on the database, it may not even be the code that’s running that is causing the problem – there could be another process that is running and hogging all of the temporary tablespace.
Of course, if your storing a lot of data in a Global Temporary Table, you may want to take at look at that as well.

ORA-30036 – unable to extend segment by %s in undo tablespace %s

This relates to the amount of data being changed in a single transaction. Once again, the Undo tablespace is shared across the database so it may not be your statement that’s causing the problem.

Related to this is…

ORA-01555 : Snapshot too old, rollback segment %s %s too small

This is the error any other session will get when it attempts to query data that is being changed by the current transaction and the current transaction has run out of undo space.

ORA-00257 archiver error. Connect internal only until freed

You will get this error if you are trying to connect to a database where the archiver is stuck.
This happens when the destination area specified for the archived redo logs (remember them?) is full and the database cannot write any further logs to it.
The database will remain in this “stuck” state until space is freed up in the destination location.
In the majority of cases, this problem is symptomatic of an underlying issue.
If your program is generating an unusually large amount of Redo – by which I mean more data is being written to the Redo Logs than is being changed by the entire process – then it may well be a case that your temporary tables aren’t as temporary as you thought they were. The solution here may well involve the judicious use of Global Temporary Tables.
On the other hand, you may simply need some more space.
Often, when this error comes up, the temptation is to switch off archiving to enable you to get back up and running as soon as possible. For the reasons I’ve already stated ( i.e. being able to recover your database) – this is a step that should only be considered in the most dire of circumstances…mentioning no fictional tests that may or may not go by the name of Kobayashi Maru.
If you do take the decision to turn off archiving then it’s essential to have a plan for re-enabling it at the earliest opportunity and that that plan is followed. The plan should also include taking a full backup of the database as soon as possible to minimise the possibility of data loss.

All of this has left me feeling a little “spaced out” so I’ll just leave you with this thought : Klingon programs don’t have parameters, they have arguments…and they always win them (just like certain Welsh people I could mention !)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.