Ansicht
Dokumentation

ORA1547 Table space overflow
Tip-No.: 1051 ( INFO1051 )

Short text

ORA1547 Table space overflow
Tip-No.: 1051

Symptom

ORA1547 failed to allocate extent of size ... in tablespace ...

Cause

Normal growth of the data base. System tried to create a new extent
for a table. System determined that space in the table space
containing the table is not sufficient.

Solution

Expand (allocate an additional BS file) the table space through
the DBA. (From ca. October 1992, tool-supported).

SAP conventions
---------------

Rule 1: The data files from the data base always have their own sub-
directory. The name of the sub-directory and the name of the da
to file are uniquely derived from the table space name.
Example:
Table space PSAPSTABD has two data files:
/usr/ora/C11/sapdata1/stabd_1/stabd.data1
/usr/ora/C11/sapdata1/stabd_2/stabd.data2
Directories "sapdata" are mount points. They represent
the existing data base file system.

When attaching a new data file to table space PSAPSTABD, the new
file should be in the same file system as the already existing files,
when possible. A new subdirectory must be created for the new file:
cd sapdata1
mkdir stabd_3
Define the new data file "stabd.data3" (see below)

If this is not possible due to space restrictions, you should first
try to mount a new file system on dapdata(n+1) (blocksize 8k|, see
below) and create the new data files/sub-directories here.

mkdir sapdata
mount the new file system, check block size
cd sapdata
mkdir stabd_3
create new data file "stabd.data3"

If new new file system available, you can also use the remaining space
from other existing file systems (via softlinks - symbolic links).

Rule 2: When possible, avoid symbolic links| Always try to place
the data base files in their own file system, mounted
on sapdata.

Creating the softlink:

The symbolic link is saved under the same name as the corresponding sub-
directory from the above conventions:

Example: you want to create a symbolic "stabd_3" under #/sapdata1
on file system "/usr/ora/newspace1/C11":

cd /usr/ora/newspace1/C11
mkdir stabd_3
cd /usr/ora/C11/sapdata1
ln -s /usr/ora/newspace1/C11/stabd_3 stabd_3
create the new data file:

/usr/ora/C11/sapdata1/stabd_3
|
v
/usr/ora/C11/newspace1/stabd_3/stabd.data3


Rule 3: Never link the data files - link the corresponding sub-
directory instead.
Rule 4: Do not just link iny any partially free file systems| The
linked data files should be placed together in a common file
system, when possible (e.g. /usr/ora/newspace1).


Expanding the table space
-------------------------

1. Determining the overflowed table space
-----------------------------------------------

- from the syslog
- from file /usr/ora/C11/sapcheck/extents.today
(log of all objects that cause a table space to overflow due to
one/two extents).
- The fill level of the table spaces are logged daily in
/usr/ora/C11/sapcheck/check.today

2. Displaying the table space data files that have already been created
-----------------------------------------------------------------

sqlplus sapr3/sap
SQL> select file_name, bytes from dba_data_files where
tablespace_name = 'PSAP...';


3. Defining the size of the new data file
---------------------------------------------

Attempt to estimate the future data flow for this table space (e.g.
also before importing mass data).
Make sure that ORACLE only manages 62 data files (including the 4 redo-
log files) under UNIX. Therefore, do not create your data files too
small. (For more than 62 data files, a comprehensive reorganization
is required|)


4. Defining the file name for the new data file
-------------------------------------------------

See conventions above


5. Creating the sub-directory / the symbolic link
-------------------------------------------------

Check whether enough space for the new data file exists in the current
fole system:
sapdata
df .
or bdf . (for HP)

Create a corresponding sub-directory or symbolic link (see example
above).

If you are creating a symbolic link to a new file system, you must
check whether the block size of the file system agrees with the block
size of the data base (8k). To do this, log on as superuser
and start script "/usr/ora/C11/sapscripts/admin/blocksize.csh".


6. Attaching the data file
--------------------------

sqlplus system/manager

SQL> Alter Tablespace add datafile
'' size ... M;

Example:
ALTER TABLESPACE PSAPSTABD ADD DATAFILE
'/usr/ora/C11/sapdata1/stabd_3/stabd.data3' SIZE 200 M;

(For this command, it does not matter whether "stabd_3" is a
sub-directory or a symbolic link|)


7. Saving the table space online
--------------------------------

In order to perform recoveries, you must now perform an online backup
of the affected table space:

brbackup -d disk -t online -m -u system/

The table space data files are saved on disk in compressed form. The
backup is in the directory defined in the brbackup profile
?/dbs/initC11.sap with parameter "backup_root_dir".