Similiar Question
SHGoh, July 23, 2004 - 6:11 am UTC
Dear Tom,
I encounter the similiar question today and need your help badly on this. The user submit the following SQL and receive ORA-01652: unable to extend temp segment by in tablespace and ORA-02063: preceding line from SCGOPDP. How do I resolve this problem? Thanks.
create table snap_rdp as select * from dpf@ext_tbl1;
Is the problem is caused by the TEMP tablespace(local) or the tablespace which user create the snap_rdp table? Thanks.
Rgds
Goh
July 23, 2004 - 9:00 am UTC
if the tablespace was "blank", it is most likely the remote database that is suffering the space problem. is dbf a complex view?
Thanks for quick respond
shgoh, July 23, 2004 - 10:39 am UTC
Dear Tom,
Thanks for the answer. dpf is a table with million of records stored in ext_tbl1 instance. I used the user admin to run this statement, the default tablespace is ADMIN with 600M free space, and the count(*) for dba_extents where tablespace_name = 'ADMIN' is 403 and max_extents is 500. There is a note posted in metalink 197569.996 saying that this is due to insuffient space or could not find continues free extent in oracle block by one of the forumer but Oracle said it is due to TEMP tablespace. I have also checked my local TEMP tablespace and apparently, it is very huge 6G. I don't know when the user hit the problem, what is the temporary usage. Your help is highly appreciated. Thanks in advance.
July 23, 2004 - 3:42 pm UTC
do you have the actual tablespace name (look in the alert log, any clues there? is that the real cut and paste of the error message).
is dpf a view or a real table?
if it is was simple select * from real-table@dblink - then, you ran out of space in the tablespace you wanted the table to be created in.
truncate instead of delte
John, July 28, 2004 - 12:42 am UTC
Tom,
What if we use truncate instead of delete, will the space below HWM be reused with APPEND hint?
if it is not used then, what will happen to the space(is available because of truncates) below HWM ?(if we are only doing batch inserts to the particular table).
thanks
July 28, 2004 - 8:09 am UTC
if you use truncate, there will be NO SPACE below the HWM :) truncate sort of moves the HWM all of the way down.
Why no hedgehogs?
Stevie Miao, August 24, 2004 - 7:34 pm UTC
The lack of hedgehogs I found to be personally disturbing and an insult. Really you should do better
August 24, 2004 - 7:55 pm UTC
did the lithium run out?
LMT confusion
RD, August 26, 2004 - 5:58 pm UTC
Hi Tom,
I created the following tablespace as exercise:-
>create tablespace test datafile 'c:\oracle\oradata\babysara\test1.dbf' size 100K extent management local autoallocate;
Tablespace created.
> create table big_table tablespace test as select * from all_objects;
ERROR at line 1:
ORA-01652: unable to extend temp segment by 8 in tablespace TEST.
When I specify autoallocate should'ent oracle allocate the extra space by itself? If I do not give it any size then it
works fine and creates the extents 64K as specified in your book "eff. oracle by design".
Please explain and if possible with examples.Also why the error message says unable to extend TEMP segment in tablespace test. Temp???
Regards,
RD.
August 26, 2004 - 7:15 pm UTC
no, autoallocate tells Oracle "figure out how big to make extents for me"
autoextend (a FILE attribute) would let the file grow
alter database datafile 'c:\.....test1.dbf' autoextend on next 1m;
the CTAS starts by filling up TEMP extents -- that when it fills them all up and the CTAS is basically done, it converts them into "real" extents.
why does it do that? in the event of a crash, SMON will come in and clean up any CTAS extents (that are temp extents until the end) automagically without anything special taking place.
CATS
A reader, September 14, 2004 - 5:25 pm UTC
Hi Tom,
When I write:
CREATE TABLE .... AS SELECT....
Do I need to specify any hint in order to write above HWM?
Thanks
September 14, 2004 - 7:12 pm UTC
you are creating a table, the concept of the HWM doesn't *even exist* yet. Sort of a quantum sql problem :)
CTAS sets the initial HWM of the newly created object, the newly created object doesn't even have a HWM until it is done being created....
Keita, December 10, 2009 - 5:34 pm UTC
When a table allocates extents, Does the HWM of the table automatically increase?
December 10, 2009 - 6:08 pm UTC
well sort of, but no not necessarily.
if you create a table that is empty with minextents "5", you'll have 5 extents allocated - but the HWM (high water mark) will be very low of course
If you alter table t allocate extent, you'll have a new extent allocated - but the HWM won't move.
If you insert into a table causing the table to become "full" and in need of more space, then we'll eventually allocate a new extent and advance the HWM into that extent. But it will advance a bit at a time, not all at once.