You Asked
Dear Tom,
Before posting the question, i would like to thank you for providing the very
best solutions to lots and lots of questions here.
I want to move (cut and paste) data from threes tables (say
table1,table2,table3) to another three tables(say
table1_live,table2_live,table3_live) in the database, where the status column in
table1 is not null. table1,table2, table3 has millions of records in it.
Sample table script
CREATE TABLE TABLE1( ID NUMBER,t11 VARCHAR2(100), STATUS VARCHAR2(10), t12
CLOB, t13 DATE);
ALTER TABLE TABLE1 ADD (CONSTRAINT PK_TABLE1 PRIMARY KEY (ID));
CREATE TABLE TABLE1_LIVE(ID NUMBER, t11 VARCHAR2(100),STATUS VARCHAR2(10),
t12 CLOB, t13 DATE);
CREATE TABLE TABLE2(EID NUMBER,ID NUMBER, t21 VARCHAR2(100), t22
VARCHAR2(100), t23 DATE);
ALTER TABLE TABLE2 ADD (CONSTRAINT PK1 PRIMARY KEY (EID));
ALTER TABLE TABLE2 ADD (CONSTRAINT FK1 FOREIGN KEY (ID) REFERENCES TABLE1 (ID));
CREATE TABLE TABLE2_LIVE(EID NUMBER, ID NUMBER, t21 VARCHAR2(100), t22
VARCHAR2(100), t23 DATE);
CREATE TABLE TABLE3(LID NUMBER,ID NUMBER,t31 VARCHAR2(100 ),t32
VARCHAR2(100 BYTE),t33 DATE);
ALTER TABLE TABLE3 ADD (CONSTRAINT PK2 PRIMARY KEY (LID));
ALTER TABLE TABLE3 ADD (CONSTRAINT FK2 FOREIGN KEY (ID) REFERENCES TABLE1
(ID));
CREATE TABLE TABLE3_LIVE(LID NUMBER,ID NUMBER,t31 VARCHAR2(100 ),t32
VARCHAR2(100 BYTE),t33 DATE);
What will be the fastest option to do this using procedure. this procedure will
be executed every day midnight.
In a similar way, i want to move data from table1_live,table2_live,table3_live
tables to similar tables in another database through db link.
i have already tried, 'insert into .. select *' option, and then do delete, but
the delete operation is taking too much time.
please help me in this.
Thanks & Regards
and Tom said...
insert into table1_live select * from table1 where status is not null;
do the same for table2 and table3.
you have the ability to do parallel deletes.
If the number of rows you are deleting is HUGE relative to the table itself, you could:
ops$tkyte%ORA10GR2> create table t1 as select * from scott.emp;
Table created.
ops$tkyte%ORA10GR2> create table t1_live as select * from t1 where 1=0;
Table created.
ops$tkyte%ORA10GR2> create table t1_temp as select * from t1 where 1=0;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert
2 when (comm is not null) then
3 into t1_live( empno, ename, job, mgr, hiredate, sal, comm, deptno )
4 values ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
5 when (comm is null) then
6 into t1_temp( empno, ename, job, mgr, hiredate, sal, comm, deptno )
7 values ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
8 select * from t1
9 /
14 rows created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t1;
Table dropped.
ops$tkyte%ORA10GR2> alter table t1_temp rename to t1;
Table altered.
split the table T1 into two (t1_live and t1_temp) and then drop T1 and just rename T1_TEMP (the rows to keep) to T1
Rating
(3 ratings)
We're not taking comments currently, so please try again later if you want to add a comment.