http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#ADMIN01701

that's the best you can hope for - a "fast split", but it'll still move the rows.

Yes, 2x the storage.

no, you did not, please contact support for that - it would be something we did wrong.

under the covers, a lot of the materialized view stuff is used - yes.

you are correct, it'll recreate the segments leaving the old segments behind.

the new data could be MUCH MORE than the old data
the new data could be MUCH LESS than the old data

split off into 8 months ago and the rest
then 7 months ago and the rest
....
then 1 months ago and the rest.


create table temp
( just like the current table )
partition by .....;

create table dummy (just like current table)

insert /*+ append */ into temp select * from current_table partition(max_partition);

drop max_partition from current table
alter current table add partition for month8, month7, month6.... month1


loop
-- turn empty dummy into fully dummy;
alter table temp exchange partition with dummy;
-- make full dummy become a partition of current table
alter table current exchange partition with dummy;
end loop

1) they do not have to get an ora-54, they can wait

2) They are parsing (library cache) and heavy logical IO (cache buffers chains) related.

if it breaks in the middle, so what? All you lost was a bit of time.

Stop being skeptical.
Start being scientific.

Skeptics are good - if they base their skepticism on solid facts, things known to be true.

Skeptics that say - I don't think I like that because I don't understand it - ugh.


Are there manual methods? Sure, but they are infinitely more error prone and complex - and I'm not going into it.

use dbms_redefinition.

the start-redef procedure will copy the existing data into your new table without locking anything. it will also setup the functional equivalent of a materialized view log so that we know what rows where added/removed/modified during this time.

then use copy table dependents to get all of your indexes, grants, constraints, etc. or, if you want to do this yourself - go ahead, just create the indexes, constraints, grants, etc on the newly populated table. Call the synchronize routine everynow and then to keep the tables in sync

then finish_redef. this will sync the tables - lock the tables - sync them one last time (to get any changes that just happened during the prior sync) and then renames the old to new and new to old.

then you are done.

why do you say "you could not get much information"???????

You saw step by step by step exactly what it did.

Let me ask you something - do you know what is happening in the background when you issue "create table t as select * from t1, t2, t3 where ..."??

No more than you do about dbms_redefinition.

No more than you do about create materialized view.

No more than you do about create index.

No more than you do about <ANY DDL COMMAND GOES HERE>.


I frankly am at a loss as to what to tell you. If you trace it, you would see that the start redef basically does a big old INSERT /*+ APPEND */ into your table. A direct path load.

You can trace the copy table depenedents - to see it does DDL - createing stuff, granting stuff.



I told you what the steps are

o you create a new table

o we load it (which you can plainly see - when I demonstrate how to use dbms_redef, I use sql trace to show precisely what it does).

o we also set up the equivalent of a materialized view log so we can sync up the tables

o we copy table dependents by reading the dictionary and recreating your triggers, grants, constraints, indexes, etc.

o we sync up using the log I mentioned

o then we lock tables - do one more sync - rename tables and we are done.


I've already written that - those are the steps (and if you know Oracle very well - you could, based on what I've written above - figure out exactly how to use other features to do this manually - I've even used the right keywords to make that exercise trivial).




If it breaks in the middle, you get an error. When you get an error, you determine corrective action based on that.

If you get "out of space" during the start redef - guess what you would do?

If you get an error from copy table dependents - you would read the error and figure out "what caused that index to not create, why didn't that trigger compile, why did that grant fail, etc" - and then correct it (and rerun copy table dependents to finish the process)

and so on.



Why are you not afraid of a SELECT statement? You don't know the intimate details of what happens in the millions of cpu instructions being executed?

Why are you not afraid of CREATE TABLE?


Please, go into your testing environment, work with this, test it, simulate your production environment.

But stop saying "oh, so scary, I don't know what it does" - you don't know what anything truly does under the covers - a create table executes HUNDREDS of sql statements - it does a ton of work.

Do with this database capability what you do with EVERY OTHER feature/function in EVERY OTHER piece of software - learn it (learn the interface). Test it, test it hard. Simulate your situation, test it more.

Then, use it.

going from the really old to the simply very old... why not 11.2.0.3?

you cannot use split to achieve composite partitioning

You can use dbms_redefinition to online redefine into a new table
You can use create table as select to create a new table
You can create the new table and insert into it

and so on.