Ora-30928
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Ora-30928

  1. #1
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    Exclamation Ora-30928

    Hi,
    I am getting the following error while running my procedure.

    My Query is
    -----------
    INSERT INTO /*+APPEND*/
    INPL_INA_PERF_LOAD(identity,
    parent_identity,
    category,
    name,
    tree,
    start_time,
    duration_ms,
    data_center,
    file_name)
    (SELECT identity,
    parent_identity,
    category,
    name,
    LPAD(' ',2*(LEVEL-1)) || TO_CHAR(identity) tree,
    start_time,
    duration_ms,
    p_i_datacenter,
    p_i_filename
    FROM
    WK_LOAD_SRC_PERF
    WHERE
    file_name = p_i_filename
    START WITH PARENT_IDENTITY=0
    CONNECT BY PRIOR IDENTITY = PARENT_IDENTITY);

    I am having 15 Lakhs records in my source table.

    The error is :

    ERROR: -- ORA-30928: Connect by filtering phase runs out of temp tablespace

    I tried to findout the details of this ORA-30928 error, But i couldn't able to trace this particular error code.

    Can anyone please help me to resolve this issue.

    Thanks in advance.

    Regards,
    PalaniKumar. R
    prajagopal@inautix.com
    RP Kumar
    You Can Win, if u believe Yourself

  2. #2
    Join Date
    Jul 2003
    Posts
    53
    Increase the temp tablespace and check out the code
    anu

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    ORA-30928 Connect by filtering phase runs out of temp tablespace

    Cause: It is probably caused by the fact that there is a loop in the data.

    Action: Retry the query with the NO_FILTERING hint. If the same error still
    occurs, then increase temp tablespace.
    Cheers!
    OraKid.

  4. #4
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    ORA-30928

    Hi..
    Thanks to Jegan , Anu...
    RP Kumar
    You Can Win, if u believe Yourself

  5. #5
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    Red face ORA-30928

    Hi,
    Increasing the temp tablespace is not that easy in my production environment.. So this is ruled out..
    I did the change as Jegan suggested by Using NO_FILLERING Hints.. Still i am getting the same error..
    So can anyone give some idea to rewrite this hierarchical query?..

    My Existing Query is

    INSERT INTO /*+ NO_FILTERING */
    INPL_INA_PERF_LOAD(identity,
    parent_identity,
    category,
    name,
    tree,
    start_time,
    duration_ms,
    data_center,
    file_name)
    (SELECT /*+ Parallel(C) */ identity,
    parent_identity,
    category,
    name,
    LPAD(' ',2*(LEVEL-1)) || TO_CHAR(identity) tree,
    start_time,
    duration_ms,
    p_i_datacenter,
    p_i_filename
    FROM
    WK_LOAD_SRC_PERF C
    WHERE
    file_name = p_i_filename
    START WITH PARENT_IDENTITY=0
    CONNECT BY PRIOR IDENTITY = PARENT_IDENTITY);


    Thanks in Advance...
    RP Kumar
    You Can Win, if u believe Yourself

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