Subject:            Understanding and Resolving ora-1547
Author:            Reem Adranly 
Last Revision Date: 11 September 1994



Oracle uses the logical 'tablespace' unit, however, the physical aspect of
the tablespace unit is the datafile.  The datafile, which is created
physically on disk, is where all objects within that tablespace reside.  In
order to add space to the tablespace, you must add a datafile.

When the error ora-1547 arises, the problem is lack of space in a particular
tablespace. The error gives two parameters: 'SIZE' which tells the user how
many Oracle blocks the system wasn't able to find; and 'TABLESPACE' which tells
the user where the space is needed.

Oracle will ALWAYS try to allocate CONTIGUOUS space.  Although the tablespace
may have enough free space, if it is not contiguous, the error will occur.

SEEING IF YOU HAVE ENOUGH SPACE:

To see if you have enough contiguous space in the tablespace, perform the
following query:

'select max(blocks) from sys.dba_free_space where tablespace_name='<NAME>';

This will return one record which shows the biggest chunk of space free in the
tablespace in question.  This number will be lower than the one returned by
the error.

If you wish to compare the contiguous space with total space, perform the
following query:

'select sum(blocks) from sys.dba_free_space where tablespace_name='<NAME>';

This also returns one record.  This value can be compared to the above to
see how much of the total space is contiguous.

WHY DOES IT NEED SPACE IN THIS PARTICULAR TABLESPACE:

Space is needed for the following objects to extend:

1.  Tables and Indexes: this is caused by the said objects needing additional
space to satisfy an insert or update.

2.  Rollback Segements: if the culprit is a rollback segments, the error
ora-1562 "failed to extend rollback segment (id = %s)" will always preceed
the ora-1547.  The ora-1562 is telling us that it couldn't extend the
rollback segment, and the reason is the ora-1547, not enough space.

3.  Temporary Tables:  these are tables created by the Oracle kernel to do a
sort on behalf of the user.  A user can tell that he is running out of space
for a temporary table, based on the operation he/she is performing (such as
creating an index, doing a query with an order by, or a lengthy join
statement.).  The temporary tablespace the user will use, can be seen by
performing the following query:

'select temporary_tablespace from sys.dba_users where username='<USERNAME>';

If the space being used seems excessive, you may want to investigate the
default storage for the temp tablespace, it is possible that the defaults
are too large.  To see the default storage, perform the following query:

'select inital_extent, next_extent, min_extents, pct_increase from
  sys.dba_tablespaces where tablespace_name='<NAME>';

Adjustments can be made to the default storage of the tablespace by issuing the
following command:

'alter tablespace <NAME> storage (inital xxx next xxx....)

Rather than add space to the temporary tablespace, you may opt to alter the
user so that he uses a tablespace you know has more free space.  If you
wish to change the temporary tablespace for the user, issue the following
command:

'alter user <USERNAME> temporary tablespace <NEW_TABLESPACE_NAME>';

HOW TO ADD SPACE:

Space can be added to a tablespace using the 'ALTER TABLESPACE' command (full
syntax below).  This statement will create a database file on disk, and will
enlarge the existing tablespace.  The statement can be performed on all
tablespaces (including system) without shuting down the database or taking the
tablespace offline.  Immediately following the completion of the statment, the
space is available.

alter tablespace <TABLESPACE_NAME> add datafile '<PATH/FILENAME>'
      size <size_of_file>;

To get an idea of the naming conventions, or locations for existing files,
perform the following query:

select file_name from sys.dba_data_files where tablespace_name='<NAME>';

NOTE:  All commands above can be found in the sql*language reference manual.