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

Quote:

---

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.

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 :D

thanx adewri for the information..

Quote:

---

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.

Quote:

---

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...

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...

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

Quote:

---

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

Thank u Amar
What do u mean by 'ahem'?

Quote:

---

Originally posted by akhadar
Thank u Amar
What do u mean by 'ahem'?

---

I was clearing my throat before speaking ;)