ORA-01652: unable to extend temp segment by %s in tablespace %s
When you try to create a new table or index in a tablespace, it is first created as a temporary segment inside the tablespace before the actual segment is created. There was not enough space left inside the tablespace to successfully complete the creation
eg:
SQL> create tablespace very_small datafile 'C:\ORACLE\ORADATA\VS01.DBF' size 128k;In order to solve the problem either extend the datafile attached to the tablespace, add a new datafile to the tablespace or turn on the autoextend feature of the datafile(s).
Tablespace created.
SQL> create table very_large tablespace very_small as select * from dba_objects;
create table very_large tablespace very_small as select * from dba_objects
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 8 in tablespace VERY_SMALL
To check what datafile are attached to the tablespace, use following command:
select file_name, bytes, maxbytes, autoextensibleTo add more space to a file issue following command:
from dba_data_files
where tablespace_name='VERY_SMALL'
alter database datafile 'C:\ORACLE\ORADATA\VS01.DBF' resize 1m;To turn on the autoextend feature on a datafile use following command:
alter database datafile 'C:\ORACLE\ORADATA\VS01.DBF' autoextend on next 100m maxsize 2000m;
Welcome to our forum for Oracle error: ORA-01652 Add your own message
if query cant be modified and also DBA team is not willing to increase the tempspace then what option is there to avoid this problem?
Any help will be appreciated.
Regards,
Puja
http://mindgrillq.blogspot.com
This query, is this a "real" query (select, with ...) or are you trying to create a new index or something which requires temp space in your tablespace?
So, the tablespace mentioned in the error message, is this a TEMPORARY tablespace, or a "normal" tablespace?
This is important to know. If you are just creating new indexes, unique constraints, ... the statement need to have some temporary space before it can created. Without it, the index, constraint, ... will not be created as well.
If it is a normal query, you need to investigate why it is taking so much temporary space. Ask you dba to check for the amount of temporary space it uses. If is is rather large, there are a number of possibilities as to why this occurs.
One might think about bugs, incorrect execution plan, incorrect optimizer settings, ...
Just a question: why is the dba reluctant to increase the space?
In which table to check all table_space names
select * from dba_tablespaces;
Add your message
If you need more information about this particular error message, you can leave a forum message.
We are replying to this message whenever we have some spare time, so please do not consider this as a private 'solve my critical issue asap' service.
Should you need professional Oracle Assistance to make your project a success, please have a look at our consultancy services.
In order to prevent automatic generation of messages, we are asking for a validation code. This code is unique and is generated every time a new message is asked.
If you do not enter the validation correctly, your message will not be recorded.