Ansicht
Dokumentation

ORA1547 in temporary table space PSAPTEMP
Tip-No.: 2733 ( INFO2733 )

Short text

ORA1547 in temporary table space PSAPTEMP
Tip-No.: 2733

Symptom

Sort operations (esp. creation of indices) terminate with the
error ORA1547.

Cause

Tablespace overflow in temporary Tablespace PSAPTEMP.
The Tablespace PSAPTEMP is used in sort operations as a clipboard for
intermediate results, whenever the sort operation cannot be executed
in memory (see init.ora parameter: "sort_area_size").

Solution

As in SAP the temp. segments grow dynamically
(Storage parameter PCTINCREASE = 50), the appending of a data file
can cause poor exploitation of these data files. We recommend therefore
either to drop the Tablespace PSAPTEMP and create a new one or the
creation of a new tablespace for the duration of the sort operations
and to defined this as the sort tablespace.


Procedure:
----------

Option No.1: create temp. second sort tablespace

As large sort areas usually are only required when creating indices
(e.g. for reorg.), we recommend that new sort tablespace only be created
when required and then to drop it afterwards. This has the advantage
that the resulting fragmentation does not appear in the rest of the
database.


1. Check in your file system where there is sufficient space for the
new temporary tablespace.
(df or bdf)
Create the relevant directories according to the SAP conventions.
(e.g. /usr/ora/C11/sapdata7/reorg_1)


2. Create the new temp. Tablespace PSAPREORG.

sqlplus system/
SQL> CREATE TABLESPACE PSAPREORG
DATAFILE '/usr/ora/C11/sapdata7/reorg_1/reorg.data1'
SIZE 200 MB
DEFAULT STORAGE (INITIAL 64K NEXT 64K MAXEXTENTS 999
PCTINCREASE 50);

Select the size of the tablespace (SIZE ...) as required (in the
worst case, the tablespace must be twice the size of your largest
index, in order to be able store sorted and unsorted data).

Set INITIAL and NEXT to a min. of 64k. The optimum value is that
which corresponds to init.ora parameter "sort_area_size".
(this value is a multiple of 64k. As sort segments on the disk are
larger than the sort_area_size, you can thus avoid unnecessary
fragmentation).

PCTINCREASE must be 50%, in order to achieve almost exponential
growth for the temp. segments.


3. Define PSAPREORG as sort tablespace the users SAPR3 and SYSTEM.

sqldba
SQLDBA> connect internal
SQLDBA> alter user sapr3 temporary tablespace PSAPREORG;
SQLDBA> alter user system temporary tablespace PSAPREORG;


4. Start your actions (e.g. reorganisation, creation of indices) that
require sort segments.


5. Set the sort tablespace back to PSAPTEMP

sqldba
SQLDBA> connect internal
SQLDBA> alter user sapr3 temporary tablepace PSAPTEMP;
SQLDBA> alter user system temporary tablespace PSAPTEMP;

6. Drop the Tablespace PSAPREORG

SQLDBA> connect system/
SQLDBA> alter tablespace PSAPREORG offline;
SQLDBA> drop tablespace PSAPREORG;

7. Drop the data file from PSAPREORG

in the above example:
rm /usr/ora/C11/sapdata7/reorg_1/reorg.data1




Option No.2: Increase size of the available Tablespace
PSAPTEMP.

The Tablespace PSAPTEMP is set to be 40 MB when first installed.
Incase this size is insufficient for normal operation (ORA1547 appears
in PSAPTEMP), PSAPTEMP itself can also be enlarged:


1. Conversion of temp. tablespace for users SAPR3 and SYSTEM.

sqldba
SQLDBA> connect internal
SQLDBA> alter user sapr3 temporary tablespace PSAPUSER1D;
SQLDBA> alter user system temporary tablespace PSAPUSER1D;

2. Determine data file for PSAPTEMP (for point 4|)

SQLDBA> select * from dba_data_files where tablespace_name
= 'PSAPTEMP';

You receive a list of the data files belonging to PSAPTEMP.


3. Drop the Tablespace PSAPTEMP

sqlplus system/
SQL> alter tablespace PSAPTEMP offline;
SQL> drop tablespace PSAPTEMP;


4. Remove the data files(s) from PSAPTEMP
Delete the files under UNIX (see list from point 2).


5. Re-create Tablespace PSAPTEMP with a larger data file.

sqlplus system/
SQL> CREATE TABLESPACE PSAPTEMP
DATAFILE '/usr/ora/C11/sapdata0/temp_1/temp.data1'
SIZE 80 MB
DEFAULT STORAGE (INITIAL 64K NEXT 64K MAXEXTENTS 999
PCTINCREASE 50);

For comments on the individual storage parameters in this example,
see above.


6. Define the new Tablespace PSAPTEMP for the users SAPR3 and SYSTEM as
sort tablespace again.

sqldba
SQLDBA> connect internal
SQLDBA> alter user sapr3 temporary tablespace PSAPTEMP;
SQLDBA> alter user system temporary tablespace PSAPTEMP;