-
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
-
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.
-
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
-
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."
-
thanx adewri for the information..
saravana kumar
-
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?
-
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."
-
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."
-
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
-
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."
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|