Home » RDBMS Server » Server Administration » different block size (11.2.0.2)
different block size [message #606920] Thu, 30 January 2014 14:46 Go to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Hi Experts,
Is it possible to create table space with different block size than db_block_size in the database?
Thanks,
Varun
Re: different block size [message #606923 is a reply to message #606920] Thu, 30 January 2014 14:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes.

Re: different block size [message #606924 is a reply to message #606923] Thu, 30 January 2014 14:51 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Hi Michel,
Would not it be a conflict?
Thanks,
Varun
Re: different block size [message #606925 is a reply to message #606924] Thu, 30 January 2014 14:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Conflict between what and what?

Re: different block size [message #606928 is a reply to message #606925] Thu, 30 January 2014 15:45 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
since we already set db_block_size to a certain number.so conflict between database block size and new tablespace with different block size
Re: different block size [message #606931 is a reply to message #606928] Thu, 30 January 2014 16:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
varunvir wrote on Thu, 30 January 2014 13:45
since we already set db_block_size to a certain number.so conflict between database block size and new tablespace with different block size


database block size is default value when no (other) block size is explicitly specified.

Re: different block size [message #606932 is a reply to message #606920] Thu, 30 January 2014 16:14 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
varunvir wrote on Thu, 30 January 2014 14:46
Hi Experts,
Is it possible to create table space with different block size than db_block_size in the database?
Thanks,
Varun


If you would simply check the docs on CREATE TABLESPACE (http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7003.htm#SQLRF54668) you'd see that blocksize can be specified at the tablespace level.

Which begs the question ... why would you want to? What problem does it solve to have different block sizes. Yes , there can be some valid reasons but they are highly specialized.
Re: different block size [message #607247 is a reply to message #606932] Tue, 04 February 2014 04:15 Go to previous messageGo to next message
mrkamranumer
Messages: 11
Registered: January 2014
Junior Member
Dear Sir
varunvir and Michel Cadot,

Same tablespace with different Block size is not supported at all in all versions
Thanks
Re: different block size [message #607248 is a reply to message #607247] Tue, 04 February 2014 04:20 Go to previous messageGo to next message
mrkamranumer
Messages: 11
Registered: January 2014
Junior Member
or you can visit this
http://www.dba-oracle.com/oracle_tips_multiple_blocksizes.htm
Re: different block size [message #607259 is a reply to message #607247] Tue, 04 February 2014 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is all CURRENT versions; it has been introduced in version 9.0!

Re: different block size [message #607260 is a reply to message #607248] Tue, 04 February 2014 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Forget this site it is b......t.

Re: different block size [message #607285 is a reply to message #607247] Tue, 04 February 2014 09:13 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
mrkamranumer wrote on Tue, 04 February 2014 04:15


Same tablespace with different Block size is not supported at all in all versions
Thanks


Different block sizes within a single TS? Never supported and counter to the basic principles of Oracle architecture. Now I'm more curious than ever about WHY you'd want to.
Re: different block size [message #607286 is a reply to message #607285] Tue, 04 February 2014 09:24 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

mrkamranumer is not OP, varunvir, who I think this because of an interview question or the like.
The only case I see when it is useful to have different block size than default one is when you import a tablespace from another database.
When this feature was introduced the point was it'd be useful to have different block sizes for performances reasons, small block for tables that are often modified or accessed via an index, and big blocks for objects that are accessed through scans. Minds has changed since then as experienced and Oracle kernel evolution didn't show any real performances improvement but the opposite due to have to configure several buffer caches.

Previous Topic: upgrade from 10g to 11g, startup upgrade error: LRM-00101: unknown parameter name 'diagnostic_dest';
Next Topic: REASON="Job slave process was terminated"
Goto Forum:
  


Current Time: Thu Mar 28 17:02:58 CDT 2024