Home » SQL & PL/SQL » SQL & PL/SQL » Moving data from one table to another (Oracle 11g)
Moving data from one table to another [message #664009] Mon, 03 July 2017 06:11 Go to next message
clancypc
Messages: 36
Registered: December 2006
Member
I have a call record repository that I want to make some changes to the structure of the underlying table. At the moment I store 100 days of data where each day is partitioned into 60 time periods to load the data into. I want to introduce a subpartition to this which is a hash of the anumber. I have attached the current and the new definitions in a file.
I believe that I cannot amend the template of an existing table to do this but have to create a new table and move the data from the old table to the new table. This is where I start to have problems, there are approx 146M records per day to be moved so doing a "insert into select * ..." is not very feasible I don't think. Instead I was looking into the exchange partition route. So the code for this would be something like
ALTER TABLE DAY_TABLES_2 EXCHANGE PARTITION JDAY_180_00_00 WITH TABLE DAY_TABLES;
Silly questions time, will this work, will it move the data from the current table (DAY_TABLES) to the new table (DAY_TABLES_2)?
Do I have to run the same query for every partition on DAY_TABLES?
Thanks,
Peter
Re: Moving data from one table to another [message #664013 is a reply to message #664009] Mon, 03 July 2017 07:01 Go to previous message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

It would be helpful if you posted DDL for the tables involved.
It would be helpful if you posted DML to provide sample data to populate tables.
It would be helpful if you posted example of desired results based upon sample data.
Previous Topic: What is the error ora-20914 pre insert I am getting this error in the forms?
Next Topic: Extracting number
Goto Forum:
  


Current Time: Thu Dec 14 04:56:32 CST 2017

Total time taken to generate the page: 0.01642 seconds