cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to extend temp segment by 128 in tablespace PSAPTEMP

Former Member
0 Kudos

Hi everybody!

While processing a UNICODE coversion with SAPINST on my SAP NW2004s system (AIX 5,2 - Oracle 10.2 - BW 7.0) into my.SAPDFACT_1.log file the following errors occured:

DbSl Trace: ORA-1403 when accessing table SAPUSER

(DB) INFO: connected to DB

(DB) INFO: DbSlControl(DBSL_CMD_NLS_CHARACTERSET_GET): UTF8

(GSI) INFO: dbname = "HBQ20080503070541 "

(GSI) INFO: vname = "ORACLE "

(GSI) INFO: hostname = "si088ss1 "

(GSI) INFO: sysname = "AIX"

(GSI) INFO: nodename = "si088ss1"

(GSI) INFO: release = "2"

(GSI) INFO: version = "5"

(GSI) INFO: machine = "0031832B4C00"

(DB) INFO: /BIC/FZHPY_LIM created #20080503200234

(IMP) INFO: import of /BIC/FZHPY_LIM completed (133182692 rows) #20080503212420

DbSl Trace: Error 1652 in exec_immediate() from oci_execute_stmt(), orpc=0

DbSl Trace: ORA-1652 occurred when executing SQL stmt (parse error offset=42)

(DB) ERROR: DDL statement failed

(CREATE UNIQUE INDEX "/BIC/FZHPY_LIM~0" ON "/BIC/FZHPY_LIM" ( "KEY_ZHPY_LIMP", "KEY_ZHPY_LIMT", "KEY_ZHPY_LIMU", "KEY_ZHPY_LIM2", "KEY_ZHPY_LIM3", "KEY_ZHPY_LIM4", "KEY_ZH

PY_LIM5", "KEY_ZHPY_LIM6", "KEY_ZHPY_LIM7" ) TABLESPACE PSAPSR3 STORAGE (INITIAL 65536 NEXT 0000002560K MINEXTENTS 0000000001 MAXEXTENTS 2147483645 PCTINCREASE 0 ) NOLOGGING COMPUTE STATISTICS )

DbSlExecute: rc = 99

(SQL error 1652)

error message returned by DbSl:

ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP

(DB) INFO: disconnected from DB

Anybody can help me?

Thanks a lot!

Accepted Solutions (0)

Answers (6)

Answers (6)

Former Member
0 Kudos

I have also faced the asme problem during export::

#Trying to create primary key "ACCTCR~0"

DbSl Trace: ORA-1652 occured; SQL statement is ==>

(OLD) ERROR: CREATE statement failed for object "ACCTCR"

(CREATE UNIQUE INDEX "ACCTCR~0" ON "ACCTCR" ( "MANDT",

"AWTYP", "AWREF", "AWORG", "POSNR", "CURTP", "WAERS"

) TABLESPACE PSAPBTABI STORAGE (INITIAL 1782579200

NEXT 0000002560K MINEXTENTS 0000000001 MAXEXTENTS

0000000300 PCTINCREASE 0000 ))

DbSlExecute: rc = 99

(SQL error 1652)

error message returned by DbSl:

ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP

#STOP: 20071204230820

What we did is truncated the table usinf sql command truncate table ACCTCR and then restarted the export..

It went successfully after this.

Regds,

Mofizur

andreas_herzog
Active Contributor
0 Kudos

^^ and you will not miss either table ACCTCR or at least its content in the export??

GreetZ, AH

Former Member
0 Kudos

SELECT ALLOCATION_TYPE, INITIAL_EXTENT, NEXT_EXTENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'PSAPTEMP';

ALLOCATIO INITIAL_EXTENT NEXT_EXTENT

-


-


-


UNIFORM 1048576 1048576

si088ss1:orahbq 28> ulimit -a

time(seconds) unlimited

file(blocks) unlimited

data(kbytes) unlimited

stack(kbytes) unlimited

memory(kbytes) 32768

coredump(blocks) 100000

nofiles(descriptors) 2000

I have more than one instance on this server...

si088ss1:orahbq 30> df -k|grep -i hbq

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_17 131072 121243 8% 4 1% /home/hbpadm

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_23 2097152 12457 100% 3374 51% /oracle/HBQ

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_13 601882 548351 9% 4 1% /oracle/HBQ/920_64

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_03 524288 379391 28% 6 1% /oracle/HBQ/mirrlogA

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_04 524288 379391 28% 6 1% /oracle/HBQ/mirrlogB

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_05 524288 333955 37% 10 1% /oracle/HBQ/origlogA

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_07 16515072 3575779 79% 830 1% /oracle/HBQ/saparch

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_08 104241037 91651545 13% 17 1% /oracle/HBQ/sapdata1

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_09 320864256 101000207 69% 103 1% /oracle/HBQ/sapdata2

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_10 106319314 92176579 14% 12 1% /oracle/HBQ/sapdata3

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_11 76172754 71172527 7% 8 1% /oracle/HBQ/sapdata4

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_12 134217728 86506456 36% 16 1% /oracle/HBQ/sapdata5

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_14 1703936 1581016 8% 14 1% /oracle/HBQ/sapreorg

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_24 1572864 580229 64% 819 1% /sapmnt/HBQ

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_25 2621440 2440991 7% 3 1% /usr/sap/HBQ/DVEBMGS10

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_06 524288 369715 30% 8 1% /oracle/HBQ/origlogB

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_15 1048576 951433 10% 4 1% /usr/usrsap/HBQ

/dev/vx/dsk/dge_sapHBQ/vn_sap_HBQ_26 5242880 1197904 78% 17825 33% /oracle/HBQ/102_64

Former Member
0 Kudos

Hi Stefan,

My OS is AIX 5.2 and my DB is Oracle 10.2

This is the file system

Total used

104241037 91651545 13% 17 1% /oracle/HBQ/sapdata1

stefan_koehler
Active Contributor
0 Kudos

Hello,

ok then can you please check this:


SQL> SELECT ALLOCATION_TYPE, INITIAL_EXTENT, NEXT_EXTENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'PSAPTEMP';
shell> su - orahbq
shell> ulimit -a
shell> df -k

And which type of file system do you use (JFS / JFS2)?

Regards

Stefan

Former Member
0 Kudos

Hi Stefan!

desc SAPSR3."/BIC/FZHPY_LIM";

Name Null? Type

-


-


-


KEY_ZHPY_LIMP NOT NULL NUMBER(10)

KEY_ZHPY_LIMT NOT NULL NUMBER(10)

KEY_ZHPY_LIMU NOT NULL NUMBER(10)

KEY_ZHPY_LIM2 NOT NULL NUMBER(10)

KEY_ZHPY_LIM3 NOT NULL NUMBER(10)

KEY_ZHPY_LIM4 NOT NULL NUMBER(10)

KEY_ZHPY_LIM5 NOT NULL NUMBER(10)

KEY_ZHPY_LIM6 NOT NULL NUMBER(10)

KEY_ZHPY_LIM7 NOT NULL NUMBER(10)

/BIC/ZHCONTING NOT NULL NUMBER(17,2)

/BIC/ZHD_IMP NOT NULL NUMBER(17,2)

/BIC/ZHPERC2 NOT NULL NUMBER(10)

SELECT AVG_ROW_LEN FROM ALL_TABLES WHERE TABLE_NAME = '/BIC/FZHPY_LIM';

AVG_ROW_LEN

-


SELECT BYTES, BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = '/BIC/FZHPY_LIM';

BYTES BLOCKS

-


-


7180648448 876544

SELECT FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS, AUTOEXTENSIBLE, MAXBYTES, MAXBLOCKS FROM DBA_TEMP_FILES;

FILE_NAME

-


TABLESPACE_NAME BYTES BLOCKS AUT MAXBYTES MAXBLOCKS

-


-


-


--- -


-


/oracle/HBQ/sapdata1/temp_1/temp.data1

PSAPTEMP 1048576000 128000 YES 1.0486E+10 1280000

/oracle/HBQ/sapdata1/temp_1/temp2.data1

PSAPTEMP 298844160 36480 YES 3.4359E+10 4194176

Thanks.

stefan_koehler
Active Contributor
0 Kudos

Hello,

now we can try to calculate the size that is needed in PSAPTEMP. (this calculation is based on all columns of the table, because of the average row leng is missing -> no statistics gathered until yet).

TABLE /BIC/FZHPY_LIM: 8k * 876544 = 7012352kb => round about 6.6 GB

PSAPTEMP:

temp.data1 => 8k * 128000 = 1024000 kb => 1 GB (Max size of temp.data1: 8k * 1280000 = 10240000 kb => 10 GB)

temp2.data1 => 8k * 36480 = 291840 kb => 0.25 GB (Max Size of temp2.data: 8k * 4194176 = 33553408 kb => 32 GB)

So your temp tablespace shoud have enough space for the table /BIC/FZHPY_LIM on oracle side.

The question is now: Why aren't the temp files extended up to its max limit?

Have you already checked, if there is enough free disk space in the file system to handle the PSAPTEMP?

Unfortunately you didn't tell us the filesystem type and the OS which you are using.. but it doesn't seems like you hit some OS limits (because of the 1 GB file).

I think that the file system is full.

Regards

Stefan

Former Member
0 Kudos

Not yet resolved

stefan_koehler
Active Contributor
0 Kudos

Hello,

we need some more information.

How big is your table "/BIC/FZHPY_LIM" and how big is your TEMP tablespace - please run the following SELECT statements and post the output:


SQL> desc SAPSR3."/BIC/FZHPY_LIM";
SQL> SELECT AVG_ROW_LEN FROM ALL_TABLES WHERE TABLE_NAME = '/BIC/FZHPY_LIM';
SQL> SELECT BYTES, BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = '/BIC/FZHPY_LIM';
SQL> SELECT FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS, AUTOEXTENSIBLE, MAXBYTES, MAXBLOCKS FROM DBA_TEMP_FILES;

Regards

Stefan

andreas_herzog
Active Contributor
0 Kudos

extend tablespace PSAPTEMP by adding another data file using brtools/brspace or sqlplus

> sqlplus "/ as sysdba"

> alter tablespace PSAPTEMP add tempfile '/oracle/<SID>/sapdata<X>/temp_2/temp.data2' size 10000M

(the above is just an example...adapt your personal settings in order to alter the tablespace succesfully)

GreetZ, AH

Former Member
0 Kudos

Hi Andreas!

I've just extended my tablespace with more space adding a new datafile, but I have the same problem.

This is what i've write on SQLPLUS:

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/HBQ/sapdata1/temp_2/temp.data1'

SIZE 298844160 REUSE AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M;

Is correct or I try to extend it more?

Thanks