unable to extend temp segment reached in creating as select statement

[abhaysk]

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.

[pando]

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

[desmondliu]

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.

[pando]

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

[jmodic]

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

[desmondliu]

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.

[jmodic]

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.