If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

table to be kept in buffer at instance startup

Hi all,
there was one question in the interview.
they asked me that "when ever the intstance is started always that table is to be brought into the cache automatically."
i have told that db_keep_cache in 9i, but he was telling there is one more method to keep that in the buffer cache.
can anybody can help me.

In the interview I'd have said the way in which you could MAXIMISE the a table data being cached in the buffer, is to create the table as CACHE, and force a full table scan via a DB_STARTUP trigger of the table... pretty stupid thing to do I've have thought though, considering a cached table would be bought to the MRU end of the buffer cache on it's first FTS anyway. Mind you CACHING should only be used on small look up tables... and this still doesn't garantee the table is ALWAYS cache, it can still age out like all other blocks in the Buffer Cahce. I'd have love to know the intervers answer.

To me, that's a smart arse question some DBA is trying to make themselves feel good...

You could prevent it from being aged out by dedicating a cache to just that table. you could either direct it the the keep cache, or even create it as the onlyobject in a tablespace of a unique size -- say your db is generally operating on 4k blocks, and you create an 8k block tablespace with this table in it.