Home » RDBMS Server » Server Administration » ORA-01652: unable to extend temp segment by 128 in tablespace DATA_01 (Oracle 10.2.0.1.0, Windows)
ORA-01652: unable to extend temp segment by 128 in tablespace DATA_01 [message #546938] Sat, 10 March 2012 10:15 Go to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi,

When i try to create a duplicate table from an existing table i get error.

SQL> create table COMP_TEMP as select * from COMPETITIVE;
create table COMP_TEMP as select * from COMPETITIVE
                                          *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace DATA_01

The table size is 15 gb.

Currently the tablespace has 2GB free space.
If i need to increase the size of the tablespace DATA_01,how much space is required.

Regards,


Re: ORA-01652: unable to extend temp segment by 128 in tablespace DATA_01 [message #546939 is a reply to message #546938] Sat, 10 March 2012 10:21 Go to previous messageGo to next message
BlackSwan
Messages: 23056
Registered: January 2009
Senior Member
>If i need to increase the size of the tablespace DATA_01,how much space is required.
15GB
Re: ORA-01652: unable to extend temp segment by 128 in tablespace DATA_01 [message #546946 is a reply to message #546939] Sat, 10 March 2012 11:53 Go to previous messageGo to next message
jesuisantony
Messages: 116
Registered: July 2006
Location: Chennai
Senior Member
The error message says that your temporary tablespace is running out of space. Please check that.
Re: ORA-01652: unable to extend temp segment by 128 in tablespace DATA_01 [message #546948 is a reply to message #546946] Sat, 10 March 2012 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 23056
Registered: January 2009
Senior Member
jesuisantony wrote on Sat, 10 March 2012 09:53
The error message says that your temporary tablespace is running out of space. Please check that.


WRONG!

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

DATA_01 not TEMP
Re: ORA-01652: unable to extend temp segment by 128 in tablespace DATA_01 [message #546951 is a reply to message #546946] Sat, 10 March 2012 12:27 Go to previous messageGo to next message
John Watson
Messages: 4803
Registered: January 2010
Location: Global Village
Senior Member
CTAS actually creates the table in a temporary segment and inserts the rows into that, then converts it to a real table at the end. That is why you get an ORA-01652 if it runs out of space, when you might expect an ORA-01653. If you query dba_segments while the CTAS is running you'll see what is going on.
Re: ORA-01652: unable to extend temp segment by 128 in tablespace DATA_01 [message #547000 is a reply to message #546951] Sun, 11 March 2012 04:05 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Thank you Swan,

John,

When you say the below statement, then normally it should affect the space of temporary tablespace
then why the error says "unable to extend temp segment by 128 in tablespace DATA_01"

Can you please clarify it ?

Quote:
CTAS actually creates the table in a temporary segment and inserts the rows into that, then converts it to a real table at the end


Regards,


Re: ORA-01652: unable to extend temp segment by 128 in tablespace DATA_01 [message #547004 is a reply to message #547000] Sun, 11 March 2012 04:40 Go to previous messageGo to next message
John Watson
Messages: 4803
Registered: January 2010
Location: Global Village
Senior Member
Quote:
If you query dba_segments while the CTAS is running you'll see what is going on.
Re: ORA-01652: unable to extend temp segment by 128 in tablespace DATA_01 [message #547017 is a reply to message #547004] Sun, 11 March 2012 09:40 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Quote:
If you query dba_segments while the CTAS is running you'll see what is going on.


Can you provide the exact query for the above said statement?

Regards,
Re: ORA-01652: unable to extend temp segment by 128 in tablespace DATA_01 [message #547018 is a reply to message #547017] Sun, 11 March 2012 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 23056
Registered: January 2009
Senior Member
07:42:18 SQL> select segment_type, count(*) from dba_segments group by segment_type order by 1;

SEGMENT_TYPE	     COUNT(*)
------------------ ----------
CLUSTER 		   10
INDEX			 2439
INDEX PARTITION 	 1606
LOB PARTITION		    1
LOBINDEX		  748
LOBSEGMENT		  748
NESTED TABLE		   39
ROLLBACK		    1
TABLE			 1817
TABLE PARTITION 	 1413
TABLE SUBPARTITION	    3

SEGMENT_TYPE	     COUNT(*)
------------------ ----------
TYPE2 UNDO		   24

12 rows selected.

07:43:02 SQL> 

Re: ORA-01652: unable to extend temp segment by 128 in tablespace DATA_01 [message #547019 is a reply to message #547018] Sun, 11 March 2012 10:04 Go to previous message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member

Thank you Swan and John
Previous Topic: upgrade to 11.2.0.3 stuck at execute dbms_registry_sys.gather_stats(null); for 4 hours. (2 mERGED)
Next Topic: service_name issue
Goto Forum:
  


Current Time: Wed Nov 26 13:14:51 CST 2014

Total time taken to generate the page: 0.05996 seconds