Professional Web Applications Themes

Tablespace, Diskspace or sql abuse.. - Oracle Server

Hi you'll, I've encounered a problem that recieved the following error: ORA-01114: IO error writing block to file 201 (block # 359561) ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file OSD-04026: Invalid parameter passed. (OS 359567) ORA-01114: IO error writing block to file 201 (block # 359561) ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file OSD-04026: Invalid parameter passed. (OS 359567) ORA-01114: IO error writing block to file 201 (block # 359561) ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file OSD-04026: Invalid parameter passed. (OS 359567) My dba ...

  1. #1

    Default Tablespace, Diskspace or sql abuse..


    Hi you'll,

    I've encounered a problem that recieved the following error:

    ORA-01114: IO error writing block to file 201 (block # 359561)
    ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
    OSD-04026: Invalid parameter passed. (OS 359567)
    ORA-01114: IO error writing block to file 201 (block # 359561)
    ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
    OSD-04026: Invalid parameter passed. (OS 359567)
    ORA-01114: IO error writing block to file 201 (block # 359561)
    ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
    OSD-04026: Invalid parameter passed. (OS 359567)

    My dba claimes that the problem is caused by a not logical query, I
    think it is a tablespace problem, maybe you can be the judge.

    My query joins a table (of 1358453 rows) to itself :
    The comparison fields are a date field and an int field when the date
    field is joined with the ">" operator and the int field is joined by
    equalization,
    The query is again joined to my previous table when the join method is
    that the last table date field is between the first table date field and
    the second table date field.
    Is this query unacceptable

    --
    Posted via [url]http://dbforums.com[/url]
    nikey11 Guest

  2. #2

    Default Re: Tablespace, Diskspace or sql abuse..

    "nikey11" <member31783dbforums.com> wrote in message
    news:3037250.1056459712dbforums.com...
    > Hi you'll,
    I'm not it, but...
    > ORA-01114: IO error writing block to file 201 (block # 359561)
    > ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
    > OSD-04026: Invalid parameter passed. (OS 359567)
    >
    > My dba claimes that the problem is caused by a not logical query, I
    > think it is a tablespace problem, maybe you can be the judge.
    You're both right. Can you confirm that file 201 is
    one of the datafiles in temporary tablespace?
    > Is this query unacceptable
    God only knows. I'd need to see what tables and indexes
    are being used, what data model, what are you trying to
    achieve, etc.

    Now, is it *efficient*? An EXPLAIN PLAN would help.
    Versions, OS, etc.
    > Posted via [url]http://dbforums.com[/url]
    How come all of a sudden we get so many posts
    here from dbforums? Something wrong with its
    "private" lists? Did this place just get
    "discovered"?

    Andrewst, what's going on? There are more
    posts here from your forum than inside it...

    --
    Cheers
    Nuno Souto
    [email]wizofoz2k.au.nosp[/email]am


    Noons Guest

  3. #3

    Default Re: Tablespace, Diskspace or sql abuse..


    "Norman Dunbar" <Norman.Dunbarlfs.co.uk> wrote in message
    news:E2F6A70FE45242488C865C3BC1245DA703D3D257lnew ton.leeds.lfs.co.uk...
    > A quick check with oerr shows this :
    >
    > oerr ora 27069
    > 27069, 00000, "skgfdisp: attempt to do I/O beyond the range of the file"
    > // *Cause: internal error, the range of blocks being read or written is
    > // outside the range of the file, additional information
    > indicates
    > // the starting block number, number of blocks in I/O, and the
    > // last valid block in the file
    > // *Action: check for trace file and contact Oracle Support
    >
    >
    > I suspect that internally, Oracle 'thinks' the file is 'x; extents long,
    > but the OS 'thinks' it is less than 'x'. End result, carnage !
    >
    > You say that you are running a query, but the error is obtained writing
    > to the file in question. I suspect that this could be a file in your
    > temporary tablespace. Whatever it is, you should check for any trace
    > files in BACKGROUND_DUMP_DEST and log an iTAR with support. Your DBA
    > should be able to manage this - he hasn't been very helpful so far :o)
    >
    If this is 8i or above, then the use of tempfile temporary tablespace can
    give rise to this error. When you create a tempfile datafile, Oracle only
    'touches' the file, and doesn't actually map out and format the entire
    thing. If another file on the same disk grows into the space that the
    tempfile one day needs to properly format, then you get this sort of mess.

    Likely, you will have to drop and re-create the temporary tablespace to fix
    the problem.

    Then you can tell your DBA that this sort of thing should never happen,
    because temporary tablespaces generate so much I/O, it's poor practice to
    house them along with other, 'genuine' tablespaces. And if they are housed
    on their own, as they should be, then the non-formatting of tempfiles at the
    time of their creation becomes a non-issue.

    If you like gloating, you can re-phrase that last paragraph slightly more
    clearly: he doesn't know his job.

    ;-o


    Arcangelo Guest

  4. #4

    Default Re: Tablespace, Diskspace or sql abuse..

    Another possibility is that your tempfile is allocated, and your block
    size is at least 8K, on a system that is not configured for large file
    support, i.e. files over 2GB in size. Make sure that your system can
    support large files if the other suggestions fail.

    HTH,
    Brian

    nikey11 wrote:
    >
    > Hi you'll,
    >
    > I've encounered a problem that recieved the following error:
    >
    > ORA-01114: IO error writing block to file 201 (block # 359561)
    > ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
    > OSD-04026: Invalid parameter passed. (OS 359567)
    > ORA-01114: IO error writing block to file 201 (block # 359561)
    > ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
    > OSD-04026: Invalid parameter passed. (OS 359567)
    > ORA-01114: IO error writing block to file 201 (block # 359561)
    > ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
    > OSD-04026: Invalid parameter passed. (OS 359567)
    >
    > My dba claimes that the problem is caused by a not logical query, I
    > think it is a tablespace problem, maybe you can be the judge.
    >
    > My query joins a table (of 1358453 rows) to itself :
    > The comparison fields are a date field and an int field when the date
    > field is joined with the ">" operator and the int field is joined by
    > equalization,
    > The query is again joined to my previous table when the join method is
    > that the last table date field is between the first table date field and
    > the second table date field.
    > Is this query unacceptable
    >
    > --
    > Posted via [url]http://dbforums.com[/url]
    --
    ================================================== =================

    Brian Peasland
    [email]oracle_dbaremove_spam.peasland.com[/email]

    Remove the "remove_spam." from the email address to email me.


    "I can give it to you cheap, quick, and good. Now pick two out of
    the three"
    Brian Peasland Guest

  5. #5

    Default Re: Tablespace, Diskspace or sql abuse..


    First of all, I'd like to say 10x to Arcangelo, Norm And Nuno. your
    swift replies have been most helpfull in confronting my evil dba...
    I've proven to him that he was wrong by his assumption that my query is
    not logical by running it on a smaller table.

    My question for you now is this:
    Have I been unproffesional by creating a query that combines a table of
    1358453 rows 3 times to itself?
    should I considered a different approach?

    All the best to you,
    noam.

    --
    Posted via [url]http://dbforums.com[/url]
    nikey11 Guest

  6. #6

    Default Tablespace, Diskspace or sql abuse..

    Hi Arcangelo,

    Good call !!!


    Cheers,
    Norman.

    -------------------------------------
    Norman Dunbar
    Database/Unix administrator
    Lynx Financial Systems Ltd.
    mailto:Norman.DunbarLFS.co.uk
    Tel: 0113 289 6265
    Fax: 0113 289 3146
    URL: [url]http://www.Lynx-FS.com[/url]
    -------------------------------------


    -----Original Message-----
    From: Arcangelo [mailto:arcangelo-c]
    Posted At: Tuesday, June 24, 2003 3:11 PM
    Posted To: server
    Conversation: Tablespace, Diskspace or sql abuse..
    Subject: Re: Tablespace, Diskspace or sql abuse..



    If this is 8i or above, then the use of tempfile temporary tablespace
    can
    give rise to this error. When you create a tempfile datafile, Oracle
    only
    'touches' the file, and doesn't actually map out and format the entire
    thing. If another file on the same disk grows into the space that the
    tempfile one day needs to properly format, then you get this sort of
    mess.

    <SNIP>


    Norman Dunbar Guest

  7. #7

    Default Tablespace, Diskspace or sql abuse..

    As with may things Oracle, it depends !!

    It depends on what the query is actually trying to do,
    also on whether the database was properly designed and normalised (or
    denormalised as appropriate !),
    and probably lots of other things that I can't quite remember at the
    moment !

    On the other hand, if said query is performing a cartesian join to
    itself (three times) then this is probably not a good thing.


    HTH

    Cheers,
    Norm.

    -------------------------------------
    Norman Dunbar
    Database/Unix administrator
    Lynx Financial Systems Ltd.
    mailto:Norman.DunbarLFS.co.uk
    Tel: 0113 289 6265
    Fax: 0113 289 3146
    URL: [url]http://www.Lynx-FS.com[/url]
    -------------------------------------



    -----Original Message-----
    From: nikey11 [mailto:nikey11]On Behalf Of nikey11
    Posted At: Tuesday, June 24, 2003 4:00 PM
    Posted To: server
    Conversation: Tablespace, Diskspace or sql abuse..
    Subject: Re: Tablespace, Diskspace or sql abuse..



    First of all, I'd like to say 10x to Arcangelo, Norm And Nuno. your
    swift replies have been most helpfull in confronting my evil dba...
    I've proven to him that he was wrong by his assumption that my query is
    not logical by running it on a smaller table.

    My question for you now is this:
    Have I been unproffesional by creating a query that combines a table of
    1358453 rows 3 times to itself?
    should I considered a different approach?

    All the best to you,
    noam.

    --
    Posted via [url]http://dbforums.com[/url]

    Norman Dunbar Guest

  8. #8

    Default Re: Tablespace, Diskspace or sql abuse..

    Hi!
    > If this is 8i or above, then the use of tempfile temporary tablespace can
    > give rise to this error. When you create a tempfile datafile, Oracle only
    > 'touches' the file, and doesn't actually map out and format the entire
    > thing. If another file on the same disk grows into the space that the
    > tempfile one day needs to properly format, then you get this sort of mess.
    >
    > Likely, you will have to drop and re-create the temporary tablespace to
    fix
    > the problem.
    If this is a sp file issue as you describe, then the file system should
    be (almost) full.
    Otherwise OS should be able to handle expanding the file correctly.

    ls -ls command in most unixes and linux should show you the *real* disk
    usage of a file,
    it's helpful when dealing with sp files such as temp files. Of course I
    would
    either create a "permanent" file for temp tablespace, then reuse the file
    when creating
    correct temp ts, to make the file to allocate space "correctly".
    Or as another solution, you just take the newly created sp tempfile
    offline,
    copy it to another name and rename it back to original name. OS copy copies
    sp
    blocks to real blocks.

    2 cents,
    Tanel.


    Tanel Poder Guest

  9. #9

    Default Re: Tablespace, Diskspace or sql abuse..

    "Ryan Gaffuri" <rgaffuricox.net> wrote in message
    news:1efdad5b.0306241036.5ec79048posting.google.c om...
    > by 'tempfile' you mean a locally managed temporary tablespace correct?
    Not exactly, he means tablespaces created with the command

    create temporary tablespace blah
    TEMPfile 'temp01.dbf' size 1049m;

    Its the specification of the datafile rather than the extent management
    clause that is important.
    > this doesnt happen with dictionary managed temporary tablespaces. This
    > is pretty interesting. Never saw that before.
    IIRC it does or could happen with DMT's. Unfortunately I don't have a DMT
    system to play with at home to prove it. This is probably a bit daft. Try
    the following (on an 8i instance)

    create temporary tablespace blah
    tempfile 'blah.dbf' size 10m;

    followed by

    select extent_management from dba_tablespaces where tablespace_name='BLAH';

    should prove you can have a dictionary managed sp file.

    --
    Niall Litchfield
    Oracle DBA
    Audit Commission UK
    *****************************************
    Please include version and platform
    and SQL where applicable
    It makes life easier and increases the
    likelihood of a good answer
    ******************************************



    Niall Litchfield Guest

  10. #10

    Default Re: Tablespace, Diskspace or sql abuse..

    "nikey11" <member31783dbforums.com> wrote in message
    news:3037651.1056466812dbforums.com...
    >
    > First of all, I'd like to say 10x to Arcangelo, Norm And Nuno. your
    > swift replies have been most helpfull in confronting my evil dba...
    > I've proven to him that he was wrong by his assumption that my query is
    > not logical by running it on a smaller table.
    >
    > My question for you now is this:
    > Have I been unproffesional by creating a query that combines a table of
    > 1358453 rows 3 times to itself?
    Not necessarily. If you have just written

    select a.*,b.*,c.*
    from large_tab a,large_tab b,large_tab c;

    Then this wouldn't be the best piece of code ever seen. On the other hand we
    might routinely include the same large table 3 times in the same query.
    Access would nearly always be via indexed columns and return small
    resultsets (in theory - in practice Accountants write the sql sometimes).
    Self Joins aren't in and of themselves a bad thing, but they need to be part
    of a sensible design and anticipated access paths.
    > should I considered a different approach?
    You should consider EXPLAINing all SQL that takes longer than a cutoff
    period (say 10 seconds) to return results and see if the SQL is sufficiently
    tuned. There are 3 things if you take this approach seriously that you need
    to know.

    1. It can be hard :(
    2. There are tools and your DBA to help you.
    3. If you consider the efficiency of your SQL and not just wether it returns
    the right results your DBA will love you. Or at least not hit you often.


    --
    Niall Litchfield
    Oracle DBA
    Audit Commission UK
    *****************************************
    Please include version and platform
    and SQL where applicable
    It makes life easier and increases the
    likelihood of a good answer
    ******************************************


    Niall Litchfield Guest

  11. #11

    Default Re: Tablespace, Diskspace or sql abuse..

    nikey11 <member31783dbforums.com> wrote in message news:<3037651.1056466812dbforums.com>...
    >
    > My question for you now is this:
    > Have I been unproffesional by creating a query that combines a table of
    > 1358453 rows 3 times to itself?
    > should I considered a different approach?
    >
    I don't think the issue is professionalism.
    It is highly dependent on what the requirements are
    in your particular case. If the design of the db
    and the requirements for data mandate that such a join
    be done, then that's it. Abstracting any wishes for
    quick re-development or re-design, which are nothing
    but wishful thinking.

    Now, of course it may need tuning or refining or whatever
    terminology your folks use. In order to make it meet
    a certain level of service and performance. A true
    professional will accept that as a given, nothing wrong
    with it and par for the course.

    That's where you should be building the bridges with your
    DBA: they can help in that area. Avoid confrontational
    situations on other fields: they favour no one in the work place.

    Cheers
    Nuno Souto
    [email]wizofoz2k.au.nosp[/email]am
    Nuno Souto Guest

Similar Threads

  1. M,I-5 Persecut ion abuse in set-up situation s a nd in pub lic
    By fevme@gmail.com in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: January 1st, 03:03 PM
  2. Diskspace
    By Barry Margolin in forum PostgreSQL / PGSQL
    Replies: 13
    Last Post: December 20th, 07:53 PM
  3. getURL abuse
    By chaos_control in forum Macromedia Flash Actionscript
    Replies: 4
    Last Post: March 1st, 01:41 PM
  4. Replies: 11
    Last Post: January 9th, 07:46 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139