This content has been marked as final. Show 15 replies
But i am sure this is not the space related problem in TEMP tablespace , as i have checked that TEMP tablespace has sufficient free space ...
ORA-1652: unable to extend temp segment by 64 in tablespace TEMP Wed Jan 12 06:17:01 2011 ORA-1652: unable to extend temp segment by 64 in tablespace TEMP Wed Jan 12 06:17:01 2011 ORA-1652: unable to extend temp segment by 64 in tablespace TEMP Wed Jan 12 08:43:16 2011 ORA-1652: unable to extend temp segment by 64 in tablespace TEMP Wed Jan 12 08:43:16 2011 ORA-1652: unable to extend temp segment by 64 in tablespace TEMP Wed Jan 12 11:08:55 2011 ORA-1652: unable to extend temp segment by 64 in tablespace TEMP Wed Jan 12 11:08:55 2011 ORA-1652: unable to extend temp segment by 64 in tablespace TEMP Wed Jan 12 13:35:10 2011 ORA-1652: unable to extend temp segment by 64 in tablespace TEMP Wed Jan 12 16:01:43 2011 ORA-1652: unable to extend temp segment by 64 in tablespace TEMP Wed Jan 12 16:01:43 2011 ORA-1652: unable to extend temp segment by 64 in tablespace TEMP
Thanks for your reply ..
This was about TEMP tablespace ... if you have some solution please let me know ... also i have this same issue in other tablespace also ... you can see the detail exaplin scenario above ...
select AUTOEXTENSIBLE,file_name from dba_temp_files where TABLESPACE_NAME='TEMP03' YES /u01/app/oracle/oradata/rdbbk/TEMP03.DBF
The simplest solution for this problem is to create another temporary tablespace
CREATE TEMPORARY TABLESPACE temp ...
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp; --> change to new temp.. tab.space.
and drop earlier temporary tablespace after some time i.e. after switching to the new temporary tablespace.
Let us know if further help required.
The simplest thing would be to drop the temp tablespace and recreate it and let it grow on the fly. If you are having the same issue with other tablespaces as well, it simply means that you are either running out of spaces in them or they have space but that's not usable and you need to defragment them.
How did you come to the conclusion that the temp has space? What's the output from v$tempseg_usage/V$sort_usage?
PS: Please don't make your question in bold, it's not required and doesn't add any emphasis to the question either.
Edited by: Aman.... on Jan 13, 2011 9:36 PM
According to the documentation of 11g you simply can not change the TEMP tablespace's extent allocation from UNIFORM to AUTOMATIC. Of course by default the behaviour for the extent allocation (if you do not define it explicitly) is automatic. Can you please explain why do you want to deal with the maxextents parameter of temporary and system tablespace?
I believe the explanation is quite helpful;
+ORA-25143: default storage clause is not compatible with allocation policy+
+Cause: default storage clause was specified for a tablespace with AUTOALLOCATE or UNIFORM policy+
+Action: Omit the storage clause+
autoallocate is the alternative name for the extent allocation automatic. So with that message i can say that both automatic and uniform tablespaces' can not have the default storage clause. On the other hand a dictionary managed tablespace can have. Please read the following documentation;
+DEFAULT storage_clause lets you specify the new default storage parameters for objects subsequently created in the tablespace. For a dictionary-managed temporary table, Oracle Database considers only the NEXT parameter of the storage_clause.+
+Refer to the storage_clause for more information.+
+Restriction on Default Tablespace Storage+
+You cannot specify this clause for a locally managed tablespace.+
Since you are using 11g version by default those tablespaces are created as locally managed tablespaces.
Hope That Helps.
Edited by: Ogan Ozdogan on 13.Oca.2011 23:12
Also the Oracle can change the extent allocation size according to the inserts into the particular table. It may start with small extent allocations in the segment and than grows automatically if needed. Allocating an extent from the system takes time and the Oracle tries to decrease that performance impact by allocating bigger extents.
Thanks to all the people for their replies.
I know the TEMP tablespace recreation process. I was just trying to figure out that if its possible to manage any solution with the current tablespace as we have only 1 or 2 GB free space in our Disk to create a new tablespace. Well regarding the TEMP tablespace i will manage it but what about the Other tablespaces which contains user data and which can't be dropped ???
Next i will fire the below command and expect to get some free space.
One more question : By default when we create tablespace in ORACLE 10g and higher version it always gets created as Locally Managed tablespaces. ( Am i right or wrong ? please let me know ) What if i need to create a tablespace with dictionary managed or TEMP tablespace with Uniform extent ? how should i write the query in Oracle 11g ??? I am doing this so as in future if i face this situation again , i would be able to fire this query ..
ALTER TABLESPACE TABLESPACE_NAME COALESCE ;
The major part is how can i create a tablespace on which i can fire the above queries ....
SQL> alter tablespace TEMP default storage (maxextents unlimited); SQL> alter tablespace TBSP1 default storage (maxextents unlimited);
Again thanks for all your replies ... I will come back with the latest update.