unable to extend temp segment reached in creating as select statement
We are facing up to a problem in creating table from remote database with db-link. We want to create a table by a "create as select statement" from a view in the remote database through a db-link, like,
create table xxx
(initial 50m next 50m pctincrease 0)
(select * from xxx@db-link);
however we frequently receive error message that "unable to extend the temp segment". everytime, dba has to extend tablespace to fix it. is there any other way we can resolve this problem. for example, could the above sql statement be tuned to avoid this problem. some one said it is better if we split the above up into 2 parts, with
(1) create an empty table
(2) then, insert data into the created empty table,
Is it true that by doing this can solve the problem? any reasoning behind this?
You solution/suggestion/comments will be highly appreciated.
thanks for the reply. can u give more detailed explanation. when the table is created under tablespace, let's say TS-A, under user AA, the temp tablespace of AA is set to TS-TEMP,
so when i received the 'unable to extend temp segment in tablespace TS-A' error msg,
(1) does it mean that no consecutive extents to allocate in TS-A or it is possible that because there is no extent to allocate in TS-TEMP?
when i create the table through db-link,
(2) should the sorting everything being done in remote database? why the local temp tablespace needed here?
thanks for the reply. i was told that it is possible that the process used to create the table uses a large amount of space in the TS-A tabelspace for temporary work by database behind the scene. It did not use the temporary space in temporary tablespace TS-B though. Is this true? what are the temporary works needed in a "create as select" statement? and why in such a statement, the process use the tablespace where the table will be created to do the temporary work instead of in the temporary tablespace?
If there was sorting involved -- and the only way i see that as possible is if xxx@db-link was actually a view, not a table, and the view query caused sort space to be used -- then the temporary tablespace would be used.
Since it is not, I'm sure that the problem is just that you do not have enough space in the tablespace you are trying to create in. The "temporary" bit id just the table segment before the CTAS is complete -- it is of "temporary" type until the build is complete and it is then changed to "table" type.
Perhaps you need PCTFREE=0 in your storage clause, if you don't want to extend your tablespace.
thank. however, should the sorting for the view been done in remote database? will it ask for temporary tablespace in local database? if split the create as select statement into two steps (1) create empty table (2) insert into xxx (select * from view@xxx). will it be better in terms of the usage of temporary ts?
thanks. slimdave. the scenario for us is local instance and remote instance trying to synchronize the data. while the table structure changed in remote database, the local database will follow. the current design will drop and re-create the table dynamically daily and get data from the remote database through db-link. this action bad impact on the allocation of the tablespace used. it is right that not because of temp ts that caused the temporary segment can not extend error. but my question is, is there any difference between the two options below,
1) use "create as select" to copy all data over from remote database through db-link.
2) create empty table first. then insert all data into the empty table as the second step.
some one said option 2 performs better than option 1, but i have some doubts on it. this is the first question,
and another one, let's assume the problem is with the temp ts, if there are soring logic involved somewhere, will the option 2 better than option 1?