This content has been marked as final. Show 16 replies
1. you need to add datafile to STAGING_TEST
2. message shows there is a space issue in STAGING_TEST tablespace (not in TEMP)
3. To confirm if autoextend is on, please paste the output of below queries
select file_name,sum(bytes/1024/1024),autoextensible,sum(maxbytes/1024/1024) from dba_data_files where tablespace_name='STAGING_TEST' group by file_name,autoextensible;
Edited by: Pavan DBA on 20 Mar, 2013 5:40 PM
aanto wrote:The message clearly states the problem is with STAGING_TEST. Nothing is said about the TEMP tablespace. Only a 'temp' segment in the STAGING_TEST tablespace.
OS :Linux 5.8
ORA-1652: unable to extend temp segment by 128 in tablespace STAGING_TEST
ORA-1652: unable to extend temp segment by 8192 in tablespace STAGING_TEST
ORA-12012: error on auto execute of job "STAGING_TEST"."PDM_MAIL_SCHEDULER"
ORA-06550: line 1, column 786:
we are getting the below error, we have a tablespace STAGING_TEST, it was only 75% full & TEMP is also almost free and "auto extent is on" on all datafiles.
why we are getting this error when it is having space(TEMP & STAGING_TEST)....
one more doubt.
1.where we need to add datafile whether in STAGING_TEST or TEMP ?
2.What is this msg exactly means....space problem is in TEMP or STAGING_TEST ?
3.Even though auto extent is on and space is available in disk, why we are getting this error....Perhaps it has already autoextended to it's max limit. Or perhaps there simply isn't enough space on the disk to fulfill the request to expand.
Thanks Pavan DBA..here is the output
SQL> select file_name,sum(bytes/1024/1024)in_MB,autoextensible,sum(maxbytes/1024/1024)in_MB from dba_data_files where tablespace_name='STAGING_TEST' group by file_name,autoextensible order by FILE_NAME;
FILE_NAME IN_MB AUT IN_MB
-------------------------------------------------- ---------- --- ----------
+DG_DATA1/etldb/datafile/staging_test01.dbf 10240 YES 10240
+DG_DATA1/etldb/datafile/staging_test02.dbf 10240 YES 10240
+DG_DATA1/etldb/datafile/staging_test03.dbf 10240 YES 10240
+DG_DATA1/etldb/datafile/staging_test04.dbf 9216 YES 10240
+DG_DATA1/etldb/datafile/staging_test05.dbf 5120 YES 10240
+DG_DATA1/etldb/datafile/staging_test06.dbf 5120 YES 10240
Thanks dbc001: we are not maintaining any temp tablespace group...pl see the output. The documents which u mentioned are very informative..
SQL> select * from dba_tablespace_groups;
no rows selected
from the output its clear that last 2 datafiles can autoextend on till 10GB. But i suspect your disk group might be full.
can you please paste the output of below query?
select name,total_mb,free_mb from v$asm_diskgroup;
select name,total_mb,free_mb from v$asm_disk;
if your disk group is full, then you need to a new disk to +DG1 disk group
Edited by: Pavan DBA on 20 Mar, 2013 6:27 PM
1.where we need to add datafile whether in STAGING_TEST or TEMP ?This problem has nothing to do with TEMP tablespace. You need to add the space in STAGING_TEST
2.What is this msg exactly means....space problem is in TEMP or STAGING_TEST ?The problem means Oracle in certain cases tries to do this processing in temp segments(not temp tablespace) and change those segments to permananet. one example is online index rebuild.
3.Even though auto extent is on and space is available in disk, why we are getting this error....Are you trying to create any big indexes or tables? bigger than 10 G?