Move partitioned table between table spaces of different block size. [message #502115] |
Mon, 04 April 2011 10:18 |
|
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 #502124 is a reply to message #502120] |
Mon, 04 April 2011 10:33 |
|
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 #502315 is a reply to message #502133] |
Tue, 05 April 2011 15:28 |
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 |
|
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?
|
|
|