Home » RDBMS Server » Server Administration » Move partitioned table between table spaces of different block size. (Oracle DB 10gR2, Linux x64)
Move partitioned table between table spaces of different block size. [message #502115] Mon, 04 April 2011 10:18 Go to next message
vini_au
Messages: 14
Registered: April 2011
Location: Australia
Junior Member
Hi All,

I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.

So far the only option I have is to export and then import back the data.

Does anyone know if there is any way to move a partitioned table between table spaces of different block size?

Thanks
Vini
Re: Move partitioned table between table spaces of different block size. [message #502117 is a reply to message #502115] Mon, 04 April 2011 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
but it seems it is not possible to move partitioned tables between table spaces of different block sizes.

Show us!

Before, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Move partitioned table between table spaces of different block size. [message #502118 is a reply to message #502117] Mon, 04 April 2011 10:24 Go to previous messageGo to next message
vini_au
Messages: 14
Registered: April 2011
Location: Australia
Junior Member
Well I don't have access to the db right here where I am but when I tired I got an error saying that the table spaces had different block sizes.

I also tried moving partition by partition but again oracle complained about the different block sizes.

I will post the exact error!
Re: Move partitioned table between table spaces of different block size. [message #502119 is a reply to message #502117] Mon, 04 April 2011 10:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>but it seems it is not possible to move partitioned tables between table spaces of different block sizes.
WHY do you have tablespaces with different block sizes?

DBMS_REDEFINITION?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Move partitioned table between table spaces of different block size. [message #502120 is a reply to message #502119] Mon, 04 April 2011 10:28 Go to previous messageGo to next message
vini_au
Messages: 14
Registered: April 2011
Location: Australia
Junior Member
I thought I was following the guidelines, if you could point out what I have done wrong I will make sure not to repeat it.
Re: Move partitioned table between table spaces of different block size. [message #502122 is a reply to message #502120] Mon, 04 April 2011 10:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
in the Practice section it says

"Show us what you did (if you tried it yourself) and how Oracle responded (COPY & PASTE your SQL*Plus session), including errors and/or why the result is not what you want.
Do not describe, explain or report - show us!"
Re: Move partitioned table between table spaces of different block size. [message #502124 is a reply to message #502120] Mon, 04 April 2011 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I thought I was following the guidelines, if you could point out what I have done wrong I will make sure not to repeat it.

Did you read my post?

A way to post would be:
SQL> create table t (id integer, val varchar2(100))
  2  partition by range (id)
  3  (partition p1 values less than (50),
  4  partition p2 values less than (maxvalue))
  5  /

Table created.

SQL> insert into t select level, 'A' from dual connect by level <= 100;

100 rows created.

SQL> commit;

Commit complete.

SQL> alter table t move partition p1 tablespace ts_16k;
alter table t move partition p1 tablespace ts_16k
                                           *
ERROR at line 1:
ORA-14520: Tablespace TS_16K block size [16384] does not match existing object block size [8192]

An answer could be:
ORA-14520: Tablespace %s block size [%s] does not match existing object block size [%s]
 *Cause: A DDL statement was issued that would require a tablespace of a
         block size different from the block size of the specified
         partitioned object to be assigned either:
           (1) As the object's default tablespace (or one of the
               object's partition-level default tablespaces,
               if composite partitioning is being used)   OR
           (2) To one of the object's partitions/subpartitions.
 *Action: Specify a tablespace of the same block size as the partitioned
          object.

It makes sense that all partitions of a table have the same block size.

Regards
Michel

[Updated on: Mon, 04 April 2011 10:36]

Report message to a moderator

Re: Move partitioned table between table spaces of different block size. [message #502125 is a reply to message #502120] Mon, 04 April 2011 10:34 Go to previous messageGo to next message
vini_au
Messages: 14
Registered: April 2011
Location: Australia
Junior Member
Sorry I didnt answer the question you asked.

The db was initially designed with 16k block and give the volume of data and how the data is queried we would greatly benefit from a larger block. Since we cannot stop the db to make changes we decided to create another table space (32k) and move the large tables and indexes there. We have also set the 32k buffer to a size that is large enough for what we need. We are also adding another storage and we want to put the tables with high I/O on the new-fast storage.
Re: Move partitioned table between table spaces of different block size. [message #502126 is a reply to message #502125] Mon, 04 April 2011 10:36 Go to previous messageGo to next message
vini_au
Messages: 14
Registered: April 2011
Location: Australia
Junior Member
I dont have access to this info right now, and considering that I might be a common challenge I thought I could simply ask.
Re: Move partitioned table between table spaces of different block size. [message #502128 is a reply to message #502126] Mon, 04 April 2011 10:39 Go to previous messageGo to next message
vini_au
Messages: 14
Registered: April 2011
Location: Australia
Junior Member
The errors that I got were exactly what Michel posted.
Re: Move partitioned table between table spaces of different block size. [message #502130 is a reply to message #502128] Mon, 04 April 2011 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>we would greatly benefit from a larger block.
I do NOT believe this can be supported by reproducible benchmark tests.
Re: Move partitioned table between table spaces of different block size. [message #502132 is a reply to message #502130] Mon, 04 April 2011 10:50 Go to previous messageGo to next message
vini_au
Messages: 14
Registered: April 2011
Location: Australia
Junior Member
Ok so if it cannot, what are the real benefits of using larger and smaller blocks?

My data never changes, when I read it I read large chunks at a time. There is never a time when a query goes and brings back one single row.
Re: Move partitioned table between table spaces of different block size. [message #502133 is a reply to message #502132] Mon, 04 April 2011 10:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Which pizza can you eat faster; one that has 4 slices or one that has 8 slices?

Oracle does NOT do any I/O; which is handles by underlying OS.
By changing Oracle blocksize you simply affect an abstraction layer; which has minimal to no impact on the physical layer.
Re: Move partitioned table between table spaces of different block size. [message #502315 is a reply to message #502133] Tue, 05 April 2011 15:28 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
This is from the Oracle Uni 11g Performance Tuning course:
Quote:
In recent years all the TPC performance tests have used an 8KB block size. Larger and smaller block sizes have not given significant performance benefits.


There is no reason to assume that the author (James Spiller) has more direct knowledge of this than we do, but we can assume that he is promoting the official Oracle policy. That's good enough for me.
Re: Move partitioned table between table spaces of different block size. [message #502481 is a reply to message #502133] Wed, 06 April 2011 10:42 Go to previous message
vini_au
Messages: 14
Registered: April 2011
Location: Australia
Junior Member
If both pizzas are the same size then you have the same amount of food to eat and therefore you should take the same amount of time.

I clearly agree with that!

There has been a lot of argument out there about whether it is really beneficial to have a larger block. There is also a lot of stuff said by Don Burleson about this issue, there are also a lot of people questioning him and dismissing his arguments.

What seems to be consistent across everything I have read is the fact the a larger block size reduces the logical reads, but then again the data still has to be read by the physical layer.

What I cannot understand now, is if there is no proven performance difference then why and when would you use a 32k over 16k?

Is there any real benefit at all?
Previous Topic: How to calculate the number of hash keys
Next Topic: New instance not starting
Goto Forum:
  


Current Time: Fri Nov 29 06:40:09 CST 2024