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

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

  1. #1
    Join Date
    May 2003
    Posts
    7

    Angry 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

  2. #2
    Join Date
    May 2003
    Location
    Kathmandu, Nepal
    Posts
    16
    Put the AUTOEXTEND ON of your targeted tablespace datafile.
    Raj Kishore
    Oracle DBA

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


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

  4. #4
    Join Date
    May 2003
    Posts
    7

    Question

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

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    May 2003
    Posts
    7
    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?

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    May 2003
    Posts
    7
    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?

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