DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: cached tables

  1. #1
    Join Date
    Jun 2002
    Location
    VA,USA
    Posts
    83

    cached tables

    hi,
    i'm new to dba.i have a table on tablespace ts1.i took that tablespace ts1 offline, and when i give a select query on the table, it throws an error like 'cannot access file 2'.
    i cached that table giving 'alter table cache;' then tried to give the select query, still giving the same error.

    if, the table is cached in memory, oracle can get the table from there, why it is searching in the disk?

    kindly reply,
    saravann kumar
    saravana kumar

  2. #2
    Join Date
    Dec 2001
    Posts
    320

    Re: cached tables

    Originally posted by ksarav

    i took that tablespace ts1 offline
    hi Saravann, taking a tablespace offline makes it unavailable for access, according to oracle documentation, you may want to take a tablespace offline for one of the following reasons:

    1- To make a portion of the database unavailable while allowing normal access to the remainder of the database.
    2- To perform an offline tablespace backup (even though a tablespace can be backed up while online and in use).
    3- To make an application and its group of tables temporarily unavailable while updating or maintaining the application.

    for more information on taking tablespace offline check the following link:
    http://download-west.oracle.com/docs...spaces.htm#819

    Hope this helps.

    Regards.

  3. #3
    Join Date
    Jun 2002
    Location
    VA,USA
    Posts
    83
    hi,
    thanx hany, u gave the answer for when to take a tablespace offline and what happens when u take a tablespace offline.

    but my doubt is, i took a tablespace offline which contains a table, i cached that table(using 'alter table cache
    usually,when you give a select query, oracle searches for the table in buffer, and if it finds the table in buffer, it gives you the result..,but why it is accessing the disk when the table is cached.
    saravana kumar

  4. #4
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    It doesnot matter whether the table cached or not... what matters is whether the table is available or not.

    When ever you fire a Select or a DML statement it has to go through PARSING which is one of the first stages in processing of a SQL statement.

    Here a parsed call is made to Oracle which checks the data dictionary for the table againt which the SELECT or the DML statement was fired, but since the tablespace for that table is offline and this is marked in the data dictionary when you fired the alter tablespace offline command, Oracle does not allow further processing of the statement and returns the message that the tablespace is offline.

    If what you are saying had been possible then i could have put all tables in cache and dropped all the tablespaces and that would have saved a lot of space for me
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  5. #5
    Join Date
    Jun 2002
    Location
    VA,USA
    Posts
    83
    thanx adewri for the information..
    saravana kumar

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by ksarav
    but my doubt is, i took a tablespace offline which contains a table, i cached that table(using 'alter table _name_ cache
    usually,when you give a select query, oracle searches for the table in buffer, and if it finds the table in buffer, it gives you the result..,
    When you take tablespace offline Oracle flushes all the buffers belonging to that tablespace out of the buffer cache - so you realy have nothing from that tablespace cached any more once you take it offline.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by jmodic
    When you take tablespace offline Oracle flushes all the buffers belonging to that tablespace out of the buffer cache - so you realy have nothing from that tablespace cached any more once you take it offline.
    But i don't think that would be true for tables cached in the keep pool... they are still in the keep cache even after the tablespace is taken offline...
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  8. #8
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Yes it is flushed... and on further investigation (after looking at the trace) i see that the DML statement is parsed, executed and fetched... its during the fetch that the error comes when its not able to read the datafile so that shows that Jurij is right as usual

    So ksarav, its not there even if you put the table to cache, its flushed once the tablespace is taken offline...
    Last edited by adewri; 04-12-2003 at 06:12 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  9. #9
    Join Date
    May 2001
    Posts
    736
    Hello jurij
    in 'ALTER TABLESPACE TABLESPACENAME OFFLINE (NORMAL,TEMPORARY,IMMEDIATE)' command
    Under what circumstances the options 'temporary' and 'immediate' are used.Even though in temporary option the checkpoint is performed while taking the tablespace offline still we need to perform media recovery. why?

    regards
    akhadar

  10. #10
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by akhadar
    Hello jurij
    in 'ALTER TABLESPACE TABLESPACENAME OFFLINE (NORMAL,TEMPORARY,IMMEDIATE)' command
    Under what circumstances the options 'temporary' and 'immediate' are used.Even though in temporary option the checkpoint is performed while taking the tablespace offline still we need to perform media recovery. why?

    regards
    akhadar
    May I, ahem...

    If no files are offline, but you use the temporary option, media
    recovery is not required to bring the tablespace back online.
    However, if one or more files of the tablespace are offline
    because of write errors, and you take the tablespace offline
    temporarily, the tablespace requires recovery before you can
    bring it back online.

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

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