Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Posts
    3

    Unanswered: Query Error ORA-01652

    Hi.

    I hav e problem with "ORA 01652 unable to extend temp segment by 8 ..." in a query. To try to solve this I have tried 2 versions with an additional where-clause on the query and it gives either "no rows selected" or the "ORA 01652 Error"

    The differense is in the 2 last lines (17 and 18):

    "and akadcn = 100100700" is ok but "and akadcn in (Select 100100700 from dual)" fails...

    Can anyone help me out?

    SQL> SELECT akadcn, akajcd, akagcn, akzcce, Log.wcf,akicta,aka6nk
    2 FROM kaakrep@KASKAD LEFT OUTER JOIN
    3 (
    4 SELECT DISTINCT obadcn dcn, obajcd jcd, obagcn gcn, obawcf wcf, obaxcf xcf
    5 FROM kgobrep@KASKAD
    6 WHERE obardt = 0
    7 )
    8 Log ON akadcn = Log.dcn AND akajcd = Log.jcd AND akagcn = Log.gcn INNER JOIN
    9 (
    10 SELECT DISTINCT asadcn dcn, asajcd jcd, asagcn gcn
    11 FROM kaasrep@KASKAD INNER JOIN
    12 kabvrep@KASKAD ON asahst = bvahst AND asalcn = bvalcn INNER JOIN
    13 kab2rep@KASKAD ON bvahst = b2ahst AND bvwxst = b2wxst AND bvzbce = b2zbce AND bvalcn
    = b2alcn
    14 WHERE b2aqcd IN ('kwh','a.kwh','b.kwh')
    15 )
    16 Object ON akadcn = Object.dcn AND akajcd = Object.jcd AND akagcn = Object.gcn
    17 WHERE akajcd = 'EL' AND akagcn IN ('1','101','201') and akadcn in
    18 (select 100100700 from dual);
    WHERE akajcd = 'EL' AND akagcn IN ('1','101','201') and akadcn in
    *
    Fel pÕ rad 17:
    ORA-01652: kan inte ut÷ka temporõrsegment med 8 i tabellutrymmet TEMP


    SQL> SELECT akadcn, akajcd, akagcn, akzcce, Log.wcf,akicta,aka6nk
    2 FROM kaakrep@KASKAD LEFT OUTER JOIN
    3 (
    4 SELECT DISTINCT obadcn dcn, obajcd jcd, obagcn gcn, obawcf wcf, obaxcf xcf
    5 FROM kgobrep@KASKAD
    6 WHERE obardt = 0
    7 )
    8 Log ON akadcn = Log.dcn AND akajcd = Log.jcd AND akagcn = Log.gcn INNER JOIN
    9 (
    10 SELECT DISTINCT asadcn dcn, asajcd jcd, asagcn gcn
    11 FROM kaasrep@KASKAD INNER JOIN
    12 kabvrep@KASKAD ON asahst = bvahst AND asalcn = bvalcn INNER JOIN
    13 kab2rep@KASKAD ON bvahst = b2ahst AND bvwxst = b2wxst AND bvzbce = b2zbce AND bvalcn
    = b2alcn
    14 WHERE b2aqcd IN ('kwh','a.kwh','b.kwh')
    15 )
    16 Object ON akadcn = Object.dcn AND akajcd = Object.jcd AND akagcn = Object.gcn
    17 WHERE akajcd = 'EL' AND akagcn IN ('1','101','201') and akadcn =
    18 100100700;

    inga rader

    SQL>

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can anyone help me out?
    Help you out with what?

    Have you actually tired to make your TEMP tablespace larger?

    When are you going to learn & use "code tags" to make your posts readable?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2007
    Posts
    3

    Table-space

    I have tried drop and create a new one with size 30 GB, it gave the same error.

    I must hav missed the code tag thing...

    -Tore

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://www.dbforums.com/showthread.php?t=1031644

    >"and akadcn = 100100700" is ok but "and akadcn in (Select 100100700 from dual)" fails...
    So use the one that is OK.

    EXACTLY what do you expect "and akadcn in (Select 100100700 from dual)" to do for the query that is different from "akadcn = 100100700" ?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2007
    Posts
    3

    Ora-06152

    It shouldn't make a difference... but maybe it could explain why I have problem in the first place...

  6. #6
    Join Date
    May 2004
    Posts
    95
    Sometimes I get the same error, even on my USER tablespace.
    I've Oracle 10 on Red Hat Ent. 4, and sometimes files with more than 30 GB, freezes.
    My solution is to create several files on the tablespace, so that even one file doesn't grow anymore, the other(s) can continue growing the tablespace.

Posting Permissions

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