temporary tables in PSAPUSER1D during cube compression

[Former Member]

Hello community,

We are now compressing the fact tables for a large number of historical InfoCubes that are no longer being loaded, but whose fact tables were not compressed before.

The compression jobs are broken up into smaller pieces for each cube, and no more than 2 compression jobs are scheduled to run at one time.

One of these compression jobs has failed with the following error in the job log :

.........

System error: ORA-01653: unable to extend table SAPR3./BI0/0400016502I by 128 in tablespace PSAPUSER1D

........

I am curious to understand why the temporary tables for cube compression are created in the PSAPUSER1D tablespace ? Typically this is a smaller sized tablespace. And for BW jobs that have large data

sizes, the temporary tables would be better located in a tablespace that provides more wiggle room for temporary use.

Upon running this select below, I find that 15 such tables are currently found in PSAPUSER1D :

.............

select tablespace_name from dba_tables where table_name

like '%/BI0/0400%';

.............

What configuration changes can be made to change the default location of these temporary tables during fact table compression ?

If there is no such configuration setting, is it then necessary to increase the size of the PSAPUSER1D tablespace in order to continue with the fact table compressions ?

Thank you very much,

Keith

[Former Member]

Hi,

You need to increase the table space. Contact Basis team.

[Former Member]

You'll need to increase the space, perhaps checking if there are any other temp tables that have not been deleted due to compression failures.

Couldn't find any specifc notes, the following - 785732,

216440, 758912 (This is actually a DB2 note)

sure make me believe that these tablesbelong in your PSATEMP and not where they are currently.

Think I'd look further into that and if necessary, open a Customer Message.

[Former Member]

This was the response from a message to SAP :

...................

18.05.2006 - 14:15:14 CET SAP Reply

Hi keith,

The temporary tables are sometimes created without a specific tablespace assigned to them and then they get created in the default tablespace,in your case PSAPUSER1D, which is small.

The option would be to expand this tablespace(which you seem reluctant to do) or to change the default tablespace. e.g. PSAPBTABD or PSAPTEMP. You can change the default tablespace for user 'SAPR3' and the temporary tables will then go to the new default tablespace.

The temporary tables must be stored in tablespace PSAPTEMP. Please, read carefully the following attached notes regarding to the issue: 659946 - FAQ: Temporary tablespaces.

Please check the Default_tablespace for user SAPR3 with the command:

select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where USERNAME='SAPR3';

However, as per note "490365 - Tablespace naming conventions" you shouldhave a tablespace like PSAP{USR name} instead of PSAPUSER1D.

So, anyway, as per your additional information, it seems that temporary objects get the tablespace PSAPUSER1D instead of PSAPTEMP.Please, check the note 490365.

...................

So the problem I experienced during compression was a result of SAP defining temporary tables without an explicit tablespace definition while at the same time we have PSAPUSER1D defined as the DEFAULT_TABLESPACE.