unable to extend temp segment reached in creating as select statement - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: unable to extend temp segment reached in creating as select statement

  1. #11
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  2. #12
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  3. #13
    Join Date
    May 2003
    Posts
    7
    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.

  4. #14
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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
    Last edited by pando; 05-30-2003 at 01:30 AM.

  5. #15
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #16
    Join Date
    May 2003
    Posts
    7
    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.

  7. #17
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width