unable to extend temp segment reached in creating as select statement

Hi, guys,

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
tablespace shared
storage
(initial 50m next 50m pctincrease 0)
as
(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.

Rgds
Desmond

Put the AUTOEXTEND ON of your targeted tablespace datafile.

Quote:

---

Originally posted by Raj Kishore
Put the AUTOEXTEND ON of your targeted tablespace datafile.

---

DO u mean, have autoextend ON and no max size?

That wud be dumb idea..of not letting TEMP tablespace to grow without limit.

Quote:

---

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.

Rgds
Desmond

---

Better idea. AS creating empty table will not require TEMP TS.
and also inserting data ( bulk insert select ) will not as well.

Abhay.

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 in advance for tackle my puzzling...

If you're creating the table in TS_A, and the temp tablespace is TS_B, and the error you get is that you can't extend the temp segment in TS_A, then the problem is not with sorting.

The new table is created as a temp segment, and when the insert is complete it is ocnverted to a table segment. I think you just don't have enough space in TS_A for the table.

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?
thanks.

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?

Let me say again that you do not have an issue with sort space on your database.

If the remote table is actually a view that requires sorting then ...

i) You should know this -- I can't guess
ii) It doesn't matter, because even if there is sorting going on it is not causing your statement to fail

What is causing your statement to fail is an operation that is happening on the tablespace you are trying to create the new table in.

So ...

i) How much space do you have on the target tablespace
ii) How much many rows are going to be created?
iii) what is the average length of the rows?
iv) What is your pctfree set to for the new table?

Stop worrying about sort space. It is not what is causing the error.

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?

Quote:

---

Originally posted by desmondliu
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?

---

Over DB link, i wud prefer to have segment created and then use COPY cmd to copy data...rather than go for Select Insert or Create Select.

In that respect, i meant 2 betters.

But in ur scnerio..ur Tablespace ( target ) is unable to extend as its reached the size specifed during creation.
WRT this, u may have to add datafile, or use AUTOEXTEND with MAXSIZE limited.

If sorting logic is there, then there lies problem with ur TEMP tablespace defined for the USER, if its throwing error as to it cant extend space. It may have to be given some more sapce to do sorting.

Abhay.

create table xxx
tablespace shared
storage
(initial 50m next 50m pctincrease 0)
as
(select * from xxx@db-link);


it is your tablespace SHARED that is full, nothing to do with temp tablespace as slimdave already pointed out

unless xxx is a view contains some grouping functions

thanks a lot for all your help. it is a view that we going to bring over through the db-link. so do you mean only the group by functions will engender the usage of temp ts and not other functions.

you have to post execution plans in local database and the rewritten sql in the remote database (get it from v$sql in remote database) and post them here

Quote:

---

Originally posted by desmondliu
it is a view that we going to bring over through the db-link. so do you mean only the group by functions will engender the usage of temp ts and not other functions.

---

No, not only GROUP BY. Also:

- ORDER BY in a view
- MERGE JOINS
- analytical functions
- UNIQUE operator
- UNION/INTERSECT/MINUS operators
- ... probably many others that I can't remember right now...

thanks. but, i am a little bit confused. if we have a view defined in remote database, In the local database, we going to use the "create as select" statement to create a table which in fact copy all data in the remote view over. will the local temporary tablespace used or the remote temporary tablespace used for those sorting alike operations defined with the remote view itself when the "create as select" running.

If the sort-requiring oparetions listed above are used in your remote view, then the sorting will be done at remote database, hence remote temp tablespace will be used for that.