OraFAQ Forum: Server Administration » different block size

Home » RDBMS Server » Server Administration » different block size (11.2.0.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

different block size [message #606920]

Thu, 30 January 2014 14:46

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

Report message to a moderator

Re: different block size [message #606923 is a reply to message #606920]

Thu, 30 January 2014 14:49

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes.

Report message to a moderator

Re: different block size [message #606924 is a reply to message #606923]

Thu, 30 January 2014 14:51

varunvir
Messages: 389
Registered: November 2007

Senior Member

Hi Michel,
Would not it be a conflict?
Thanks,
Varun

Report message to a moderator

Re: different block size [message #606925 is a reply to message #606924]

Thu, 30 January 2014 14:56

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Conflict between what and what?

Report message to a moderator

Re: different block size [message #606928 is a reply to message #606925]

Thu, 30 January 2014 15:45

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

Report message to a moderator

Re: different block size [message #606931 is a reply to message #606928]

Thu, 30 January 2014 16:09

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.

Report message to a moderator

Re: different block size [message #606932 is a reply to message #606920]

Thu, 30 January 2014 16:14

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.

Report message to a moderator

Re: different block size [message #607247 is a reply to message #606932]

Tue, 04 February 2014 04:15

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

Report message to a moderator

Re: different block size [message #607248 is a reply to message #607247]

Tue, 04 February 2014 04:20

mrkamranumer
Messages: 11
Registered: January 2014

Junior Member

or you can visit this
http://www.dba-oracle.com/oracle_tips_multiple_blocksizes.htm

Report message to a moderator

Re: different block size [message #607259 is a reply to message #607247]

Tue, 04 February 2014 05:06

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: different block size [message #607260 is a reply to message #607248]

Tue, 04 February 2014 05:06

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: different block size [message #607285 is a reply to message #607247]

Tue, 04 February 2014 09:13

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.

Report message to a moderator

Re: different block size [message #607286 is a reply to message #607285]

Tue, 04 February 2014 09:24

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 03:14:23 CDT 2025