Home » RDBMS Server » Server Administration » Move partition to cache (Oracle 10.2.0.4.0,Windows)
Move partition to cache [message #545345] Tue, 28 February 2012 10:15 Go to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

Hi,

How to move a partition of a table to db_keep_cache ?

Thanks
Re: Move partition to cache [message #545348 is a reply to message #545345] Tue, 28 February 2012 10:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.lmgtfy.com/?q=oracle+db_keep_cache
Re: Move partition to cache [message #545349 is a reply to message #545345] Tue, 28 February 2012 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try ALTER TABLE?

Regards
Michel
Re: Move partition to cache [message #545350 is a reply to message #545349] Tue, 28 February 2012 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (val integer) partition by range (val)
  2  (partition p1 values less than (100),
  3  partition p2 values less than (maxvalue));

Table created.

SQL> select partition_name, buffer_pool from user_tab_partitions where table_name='T';
PARTITION_NAME                 BUFFER_
------------------------------ -------
P1                             DEFAULT
P2                             DEFAULT

2 rows selected.

SQL> alter table t modify partition p1 storage (buffer_pool keep);

Table altered.

SQL> select partition_name, buffer_pool from user_tab_partitions where table_name='T';
PARTITION_NAME                 BUFFER_
------------------------------ -------
P1                             KEEP
P2                             DEFAULT

2 rows selected.

Regards
Michel
Re: Move partition to cache [message #545400 is a reply to message #545350] Wed, 29 February 2012 02:32 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

Thank you Michel,

Can you please explain me the concept or provide me relevant link on what is the
benefit of moving the partition to database keep cache ?

Regards,
Re: Move partition to cache [message #545402 is a reply to message #545400] Wed, 29 February 2012 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is none (99.999% of the cases).
YOU want to move it to KEEP cache, why?

Regards
Michel
Re: Move partition to cache [message #545408 is a reply to message #545402] Wed, 29 February 2012 03:08 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

As the db_keep_cache pool is the allocation of buffers that are frequently accessed and need to be in memory
for fast response time.For this reason i wanted to move the partition to keep cache.

Quote:
here is none (99.999% of the cases


What about remaining 0.1 % ?


[Updated on: Wed, 29 February 2012 03:09]

Report message to a moderator

Re: Move partition to cache [message #545410 is a reply to message #545408] Wed, 29 February 2012 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You mesn 0.001%?
You ignore them for the moment as you will find the good reason by yourself when you will encounter this case in 20 years from now, if Oracle still exists and KEEP buffer pool too.

Regards
Michel
Re: Move partition to cache [message #545411 is a reply to message #545410] Wed, 29 February 2012 03:43 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As the db_keep_cache pool is the allocation of buffers that are frequently accessed and need to be in memory for fast response time.For this reason i wanted to move the partition to keep cache.

If the blocks are frequently accessed it is in cache, there is no need of a new pool.

Regards
Michel
Previous Topic: change from nologging to logging
Next Topic: Flashback Q regarding statistics (merged 2)
Goto Forum:
  


Current Time: Thu Mar 28 04:28:31 CDT 2024